[lib] CSV - AutoHotkey library for working with CSV Files

Post your working scripts, libraries and tools for AHK v1.1 and older
ahk7
Posts: 572
Joined: 06 Nov 2013, 16:35

[lib] CSV - AutoHotkey library for working with CSV Files

Post by ahk7 » 22 Jul 2017, 03:09

Source: https://github.com/hi5/CSV

This CSV library was developed by trueski and Kdoske - the original source(s) can be found here http://www.autohotkey.com/forum/viewtop ... 126#329126 and https://autohotkey.com/board/topic/5168 ... brary-lib/
(archived forum links, the code posted there is no longer valid due to errors caused by upgrading the forum software.)

The code at GH includes various fixes as documented in the readme and code.

Functions
  • CSV_Load() ; Load CSV file into memory
  • CSV_TotalRows() ; Return total number of rows
  • CSV_TotalCols() ; Return total number of columns
  • CSV_Delimiter() ; Return the delimiter used
  • CSV_FileName() ; Return the filename
  • CSV_Path() ; Return the path
  • CSV_FileNamePath() ; Return the filename with the full path
  • CSV_Save() ; Save CSV file
  • CSV_DeleteRow() ; Delete a row
  • CSV_AddRow() ; Add a row
  • CSV_DeleteColumn() ; Delete a column
  • CSV_AddColumn() ; Add a column
  • CSV_ModifyCell() ; Modify an existing cell
  • CSV_ModifyRow() ; Modify an existing row
  • CSV_ModifyColumn() ; Modify an existing column
  • CSV_Search() ; Search for text within
  • CSV_SearchRow() ; Search for text within a cell within a specific row
  • CSV_SearchColumn() ; Search for text within a cell within a specific column
  • CSV_MatchCell() ; Search for a cell containing exactly the data specified
  • CSV_MatchCellColumn() ; Search for a cell containing exactly the data specified in a specific column
  • CSV_MatchCellRow() ; Search for a cell containing exactly the data specified in a specific row
  • CSV_MatchRow() ; Search for a row containing exactly the data specified
  • CSV_MatchCol() ; Search for a column containing exactly the data specified
  • CSV_ReadCell() ; Read data from the specified cell
  • CSV_ReadRow() ; Read data from the specified row
  • CSV_ReadCol() ; Read data from the specified column
  • CSV_LVLoad() ; Load data into a ListView in the specified gui window, ListView name variable will equal "CSV_Identifier"
  • CSV_LVSave() ; Save the specified ListView as a CSV file, CSV_Identifier is the ListView's associated variable name.

RiseUp
Posts: 28
Joined: 01 Oct 2013, 21:27

Re: [lib] CSV - AutoHotkey library for working with CSV Files

Post by RiseUp » 02 Dec 2017, 21:06

Thank you very much for putting this together and throwing it up on GitHub. I'm starting a project now that should hopefully be able to make use of this library.

User avatar
divanebaba
Posts: 804
Joined: 20 Dec 2016, 03:53
Location: Diaspora

Re: [lib] CSV - AutoHotkey library for working with CSV Files

Post by divanebaba » 25 Jun 2018, 01:19

Hallo.

I decided to use this library for my issues and have found a little fault.
The CSV_Save(...) command uses StringRight with too many parameters.
Instead of stringright, test, EntireFile, EntireFile, 1 it has to be stringright, test, EntireFile, 1 or, as help-file says test := SubStr(EntireFile, 0)
I do not know how bad the mistake is and what it can do.

EDIT: After description of Gast (see below) I added link to faulty line https://github.com/hi5/CSV/blob/master/csv.ahk#L118
It is not a big thing, but maybe it could generate strange troubles.
Last edited by divanebaba on 25 Jun 2018, 10:47, edited 3 times in total.

Guest

Re: [lib] CSV - AutoHotkey library for working with CSV Files

Post by Guest » 25 Jun 2018, 09:06

@divanebaba Good catch :-)

The entire lib/code is a bit messy but I haven't run in to any specific issues with CSV_Save()

That code seems to have been added here https://autohotkey.com/board/topic/5168 ... /?p=323475 * to ensure that after the last row/cell there aren't any `r `n characters - this would ensure proper loading of the CSV next time - later a fix for the loading issue was posted here https://autohotkey.com/board/topic/5168 ... ntry596685 ( that fix is implemented in the current CSV lib in GH (see line #58 https://github.com/hi5/CSV/blob/master/csv.ahk#L58 )

* So it seems the posted / added code doesn't actually do anything - I've changed in my local copy and see how it works. I don't expect there to be any problems as it only removes `r `n in clumsy way. Using Trim() would make more sense nowadays.

Let me know if you notice something going wrong with this change in CSV_Save().

Elendiar
Posts: 17
Joined: 03 Jul 2018, 05:44

Re: [lib] CSV - AutoHotkey library for working with CSV Files

Post by Elendiar » 13 Aug 2018, 05:25

Hello, thanks for this useful script! But i have an issue, CSV_LVLoad add only one, first column :( Using like this:

Code: Select all

CSV_Load(CSVfile, CSV, ";")
GUI, CSV_gui:New, +AlwaysOnTop +Resize +LastFound -DPIScale, Stats
CSV_LVLoad(CSV, "CSV_gui", , , 1000, 1000, "Date")
GUI, CSV_gui:Show
From csv:
Spoiler
I got only 1 col:
csv.png
csv.png (8.51 KiB) Viewed 20260 times

UPD: Oh, writing this post I guessed using separator | in

Code: Select all

CSV_LVLoad(CSV, "CSV_gui", , , 1000, 1000, "Date|Time|Status")
and it works fine!

Thank you very much for such a convenient lib!

Jojo-French

How use it for dummy

Post by Jojo-French » 04 Oct 2018, 19:18

Hello,

I have a CVS file: test.csv

In my file I have
Data1, Data2, Data3

I just wanna have msg Data3

How can I use this amazing library?

Guest

Re: [lib] CSV - AutoHotkey library for working with CSV Files

Post by Guest » 05 Oct 2018, 01:59

If it really is just a one line file with 3 cells I would simply use FileRead+Loop or StrSplit() but assuming you have multiple rows CSV might be easier to use, you can use it like so (if you look at csv.ahk you'll see a list of all available functions with parameters at the start of the file).

Code: Select all

; Load file
CSV_Load("b.csv", "Data") ; filename, CSV_Identifier

;CSV_ReadCell(CSV_Identifier, Row, Column) ; Read data from the specified cell
MsgBox % CSV_ReadCell("Data", 1, 3) ; CSV_Identifier, row, column

; find specific cell
MsgBox % CSV_Search("Data", "Data3") ; success, Search for text within, partial match using InStr()
MsgBox % CSV_MatchCell("Data", " Data3") ; success, exact match so note the space BEFORE Data3
MsgBox % CSV_MatchCell("Data", "Data3") ; fail, using exact match so note the MISSING space before Data3 which is why it can't find it " Data3" <> "Data3"

ExitApp ; when you're done need to exit the script as using CSV makes it persistent

JOHN-FRENCH-FRANCE

Re: [lib] CSV - AutoHotkey library for working with CSV Files

Post by JOHN-FRENCH-FRANCE » 05 Oct 2018, 08:06

Thank you so to take your time for me

actualy it would be a loop to past this info line after lines

here are my colomns
name,owner,Adress,postalcode,city

then i would like to write what client said about yes or no on my product on new colomn

THANK YOU AND SORRY FOR DISTRUBING

Guest

Re: [lib] CSV - AutoHotkey library for working with CSV Files

Post by Guest » 05 Oct 2018, 13:07

Just so you know AutoHotkey can directly write to Excel (or LibreOffice Calc) - see the Tutorial section of this forum.
For CSV you can try this, first add a new column so there are empty cells you can update with yes/no/whatever:

Code: Select all

; general remark, create frequent backups (you can use FileCopy to automatically create timestamped backups)
; create a test file
FileDelete, testdata.csv
FileAppend,
(
name,owner,Adress,postalcode,city
john,jane,street1,1234,Paris
mary,rupert,street1,5678,Lyon
), testdata.csv

CSV_Load("testdata.csv","data")

; we need to add a column first

CSV_AddColumn("data", "Interested") ; this will add a column (6th) with empty cells with a header (first line of "Interested").
CSV_Save("testdata_modified_newcolumn.csv", "data") ; just you can see how it looks

; Now we have empty cells to write to using CSV_ModifyCell(CSV_Identifier, NewValue, Row, Col)
CSV_ModifyCell("data", "yes",2, 6) ; john is interested
CSV_ModifyCell("data", "no",3, 6) ; mary is not 

; remember that data is only in memory not in the file until you save it
CSV_Save("testdata_with_new_data.csv", "data") ; just you can see how it looks

ExitApp

burque505
Posts: 1731
Joined: 22 Jan 2017, 19:37

Re: [lib] CSV - AutoHotkey library for working with CSV Files

Post by burque505 » 06 Oct 2018, 08:21

@guest, very nice example, thank you.
Regards,
burque505

Invitro
Posts: 12
Joined: 09 Oct 2018, 08:51

Re: [lib] CSV - AutoHotkey library for working with CSV Files

Post by Invitro » 06 Nov 2018, 11:43

I am using the CSV Library to use a CSV Database for Autohotkey. Multiple Users read and change the CSV. However sometimes the CSV get empty or suddenly there are multiple lines more in the csv, although the script is only changing one Cell. I believe it happens when ever two user are changing the CSV at the same time.
The script is doing the same like in the example above by the way.
Does anyone know a build in way or a good way to tell ahk that the csv is right now in use by an other script?

Thank you for any help!

ahk7
Posts: 572
Joined: 06 Nov 2013, 16:35

Re: [lib] CSV - AutoHotkey library for working with CSV Files

Post by ahk7 » 06 Nov 2018, 12:29

The CSV data is entirely in memory while running the script, so when multiple users are writing to the same file it will indeed cause problems.
You could use an INI file and use IniRead and IniWrite to check if a user has opened the file or when it has closed it again.
That way you can show a dialog to your users saying 'data in use by X'

In general this situation with multiple users would be an ideal candidate for a proper database, there are two libraries that can help with that see https://github.com/ahkscript/awesome-Au ... /#database

Invitro
Posts: 12
Joined: 09 Oct 2018, 08:51

Re: [lib] CSV - AutoHotkey library for working with CSV Files

Post by Invitro » 06 Nov 2018, 14:30

Thank you for your reply!!!

Rafaews
Posts: 25
Joined: 16 Mar 2018, 21:19

Re: [lib] CSV - AutoHotkey library for working with CSV Files

Post by Rafaews » 23 Aug 2019, 16:29

I think there's something wrong with the CSV_DeleteRow function...

My code:

Code: Select all

if (LabExecutorCol)
{
	Loop %TotalRows%
	{
		if (CSV_ReadCell("GAL", A_Index, LabExecutorCol) != "WHATEVER")
		{
			CSV_DeleteRow("GAL", A_Index)
			Count++
		}
	}
}
"Count" returns two different values if I comment/uncomment CSV_DeleteRow("GAL", A_Index), which doesn't make sense... (62) with no function, (76) if I want to delete the rows. The code is deleting more than it should.

ahk7
Posts: 572
Joined: 06 Nov 2013, 16:35

Re: [lib] CSV - AutoHotkey library for working with CSV Files

Post by ahk7 » 23 Aug 2019, 17:04

I think it works correctly, but because you are modifying the rows it is actually skipping rows, say it finds row 3, it deletes row 3, so row 4 is now actually row 3, but the next loop it will look at row 4 (which was row 5) so it doesn't check row 4. Anyway, you're probably better off by building a list of found rows firs, then parse that list to delete the rows. Below an example which I think works correctly.

EDIT: I fell into my own trap :) the rows need to be deleted in reverse order

Code: Select all

FileDelete, TestCSVFile.csv
FileAppend,
(
row1,1,2,3,4,5,6,7,8,9,10
row2,a,b,c,d,e,f,g,h,i,k
row3,l,m,n,o,p,q,r,s,t,u
row4,v,w,z,y,z,_,),(,*,&
), TestCSVFile.csv

CSV_Load("TestCSVFile.csv","data")
CSV_DeleteRow("data", 2) ; works as it should
CSV_Save("TestCSVFile_Changed.csv", "data")

; MsgBox % CSV_TotalRows("data") ; shows 3 so that is correct

LabExecutorCol:=2
RowsToDelete:=""

Loop, % CSV_TotalRows("data") ; not %TotalRows%
	{
	if (CSV_ReadCell("data", A_Index, LabExecutorCol) != "l") ; so remove all rows where the column 2 isn't "l" so row1 and row4, so the CSV will just have row3
		{
		; CSV_DeleteRow("data", A_Index)
		RowsToDelete := A_Index "," RowsToDelete ; we build a REVERSE list to delete ; was RowsToDelete .= A_Index "," which was incorrect
		Count++
		}
	}

RowsToDelete:=Trim(RowsToDelete,",") ; just remove any "empty" values

MsgBox % Count ":" RowsToDelete ; note the reversed order 

Loop, parse, RowsToDelete, CSV
	CSV_DeleteRow("data", A_LoopField)

CSV_Save("TestCSVFile_Changed2.csv", "data") ; works

ExitApp
EDIT: I fell into my own trap :) the rows need to be deleted in reverse order

ahk7
Posts: 572
Joined: 06 Nov 2013, 16:35

Re: [lib] CSV - AutoHotkey library for working with CSV Files

Post by ahk7 » 23 Aug 2019, 17:26

Just bump to highlight Edit above...

Rafaews
Posts: 25
Joined: 16 Mar 2018, 21:19

Re: [lib] CSV - AutoHotkey library for working with CSV Files

Post by Rafaews » 26 Aug 2019, 20:04

but because you are modifying the rows it is actually skipping rows
OMG, that makes sense! hahhaa
EDIT: I fell into my own trap :) the rows need to be deleted in reverse order
No problem. Thank you very much!

hasantr
Posts: 933
Joined: 05 Apr 2016, 14:18
Location: İstanbul

Re: [lib] CSV - AutoHotkey library for working with CSV Files

Post by hasantr » 20 Sep 2019, 14:05

ahk7 wrote:
22 Jul 2017, 03:09
Source: https://github.com/hi5/CSV

This CSV library was developed by trueski and Kdoske - the original source(s) can be found here http://www.autohotkey.com/forum/viewtopic.php?p=329126#329126 and https://autohotkey.com/board/topic/51681-csv-library-lib/
(archived forum links, the code posted there is no longer valid due to errors caused by upgrading the forum software.)

The code at GH includes various fixes as documented in the readme and code.

Functions
  • CSV_Load() ; Load CSV file into memory
  • CSV_TotalRows() ; Return total number of rows
  • CSV_TotalCols() ; Return total number of columns
  • CSV_Delimiter() ; Return the delimiter used
  • CSV_FileName() ; Return the filename
  • CSV_Path() ; Return the path
  • CSV_FileNamePath() ; Return the filename with the full path
  • CSV_Save() ; Save CSV file
  • CSV_DeleteRow() ; Delete a row
  • CSV_AddRow() ; Add a row
  • CSV_DeleteColumn() ; Delete a column
  • CSV_AddColumn() ; Add a column
  • CSV_ModifyCell() ; Modify an existing cell
  • CSV_ModifyRow() ; Modify an existing row
  • CSV_ModifyColumn() ; Modify an existing column
  • CSV_Search() ; Search for text within
  • CSV_SearchRow() ; Search for text within a cell within a specific row
  • CSV_SearchColumn() ; Search for text within a cell within a specific column
  • CSV_MatchCell() ; Search for a cell containing exactly the data specified
  • CSV_MatchCellColumn() ; Search for a cell containing exactly the data specified in a specific column
  • CSV_MatchCellRow() ; Search for a cell containing exactly the data specified in a specific row
  • CSV_MatchRow() ; Search for a row containing exactly the data specified
  • CSV_MatchCol() ; Search for a column containing exactly the data specified
  • CSV_ReadCell() ; Read data from the specified cell
  • CSV_ReadRow() ; Read data from the specified row
  • CSV_ReadCol() ; Read data from the specified column
  • CSV_LVLoad() ; Load data into a ListView in the specified gui window, ListView name variable will equal "CSV_Identifier"
  • CSV_LVSave() ; Save the specified ListView as a CSV file, CSV_Identifier is the ListView's associated variable name.
Bu harika bir fonksiyon. İşleri gerçekten çok kolaylaştırıyor.
Lv_load() işlevine Limit parametresi eklemenizi öneririm. Her zaman binlerce satır göstermek zorunda değildir.

hasantr
Posts: 933
Joined: 05 Apr 2016, 14:18
Location: İstanbul

Re: [lib] CSV - AutoHotkey library for working with CSV Files

Post by hasantr » 22 Sep 2019, 19:55

If the text I'm looking for is available in more than one line, can I get it all? CSV_MatchRow () Returns only one line.

ahk7
Posts: 572
Joined: 06 Nov 2013, 16:35

Re: [lib] CSV - AutoHotkey library for working with CSV Files

Post by ahk7 » 26 Sep 2019, 14:00

hasantr wrote:
22 Sep 2019, 19:55
If the text I'm looking for is available in more than one line, can I get it all? CSV_MatchRow () Returns only one line.
Perhaps like so, not very elegant but should work OK for small(er) csv files. If you have very large files it will probably be too slow to be very useful.

Code: Select all

FileDelete, TestCSVFile.csv
FileAppend,
(
row1,1,2,3,4,5,6,7,8,9,10
row2,a,b,c,d,e,f,g,h,i,k
row3,l,m,n,o,p,q,r,s,t,u
row4,v,w,z,y,z,_,),(,*,&
row2,a,b,c,d,e,f,g,h,i,k
), TestCSVFile.csv

CSV_Load("TestCSVFile.csv","data")

SearchRow:="row2,a,b,c,d,e,f,g,h,i,k"

; not very efficient but we simply try all instances just in case all rows are duplicates
; if you have a large CSV it will probably be slow and other solutions my proof more useful
; and (a lot) faster

while (A_Index < CSV_TotalRows("data")) ; keep looping until we've checked all rows
	{
	 result:=CSV_MatchRow("data", SearchRow, A_Index)
	 If result ; so we skip "0" which could be quite a few, if there is only one matchrow all other instances after the first will be "0" and your results list would look something like "2,0,0,0,0" for example
		foundrows .= result ","
	}

MsgBox % RTrim(foundrows,",") ; we trim off the additional "," that is always added using RTRIM()

Post Reply

Return to “Scripts and Functions (v1)”