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

Post your working scripts, libraries and tools for AHK v1.1 and older
User avatar
112
Posts: 6
Joined: 24 May 2019, 20:02

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

Post by 112 » 02 Oct 2019, 05:41

Thank you for this library, I'm not super advanced with ahk, so my questions might be kinda obvious, but here goes:

1.

Code: Select all

CSV_Load(file,"CSV")
MsgBox, % CSV_ReadCell("CSV", 2, 3)         ; row, column
MsgBox, % CSV_ReadRow("CSV",1)
My .csv is 3 columns and 30 rows.
ReadCell gives me the right output, ReadRow only shows the value of the first 2 cells in any given row, any idea why it'd do that? (ReadCell for a cell in column 3 works)

2. Can I use the value ReadCell give me in a variable?

Thanks for any help :D

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

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

Post by ahk7 » 02 Oct 2019, 12:40

Now sure why it doesn't show the entire row for you, my guess there is something in that row that is "incorrect" - if you have a sample file / row you can share feel free to post perhaps it can be replicated.

The test script below works for CSV_ReadRow() so it should work. It also shows how you can assign results to variables.

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")

MyCell:=CSV_ReadCell("data",2,3) ; should be b
MsgBox % MyCell                  ; shows b

MyRow:=CSV_ReadRow("data",1) ; should be row1,1,2,3,4,5,6,7,8,9,10
MsgBox % MyRow               ; shows row1,1,2,3,4,5,6,7,8,9,10

ExitApp

kairushinjuu
Posts: 46
Joined: 07 May 2020, 07:02

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

Post by kairushinjuu » 13 Nov 2020, 20:31

what exactly is the "CSV_Identifier" supposed to be? sorry for bumping an old post

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

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

Post by ahk7 » 14 Nov 2020, 02:55

kairushinjuu wrote:
13 Nov 2020, 20:31
what exactly is the "CSV_Identifier" supposed to be? sorry for bumping an old post
Example:

Code: Select all

CSV_Load("TestCSVFile.csv","data") ; data is the CSV_Identifier here, note that is always a string, 
                                   ; so data is not a variable or object, just a name so 
                                   ; CSV_ReadCell(data,2,3) would be wrong (note the lack of "" around data)
MyCell:=CSV_ReadCell("data",2,3)   ; read that cell from "data"
You can load multiple CSV files in the same script and refer to each using the "CSV_Identifier" - if I where to rewrite this library now, I would omit the CSV_Identifier and load the CSV into an object (array) - but this library would still work with very old versions of AutoHotkey.

kairushinjuu
Posts: 46
Joined: 07 May 2020, 07:02

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

Post by kairushinjuu » 14 Nov 2020, 03:27

@ahk7
Thanks for the reply. Unfortunately I am still not fully understanding... Do you mean its the header for a column that I would have in the CSV file? Sorry if this is coming off as really stupid

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

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

Post by ahk7 » 14 Nov 2020, 09:07

It is just a name you give the "CSV_Identifier" which you use to tell the CSV functions which data to use.
It can be anything and doesn't have to mean anything as long as you pass it on to all CSV functions.

So if you only work with one csv file, you can use "data" as in the example above and you don't have to worry about it. Only if you are working with multiple/different CSV files in one script you should keep track of "what is what" by using CSV_Identifiers that are meaningful to you.

Perhaps this example makes it clear, here we don't use "data" as identifier, but "TestCSVFile", if you use part of the filename of the CSV like so, you always know which csv file you are using when you are calling CSV functions. Just try it out. If it works it works :)

Code: Select all

CSV_Load("TestCSVFile.csv","TestCSVFile") ; "TestCSVFile" is the CSV_Identifier here
MyCell:=CSV_ReadCell("TestCSVFile",2,3) ; store the contents of that cell in the "MyCell" variable



It is just like a variable which you can give any "name" you'd like, these are all the same:

Name:="kairushinjuu"
User:="kairushinjuu"
Member:="kairushinjuu"

If you want to use them in command or function you pass on the variable, so think of the CSV_Identifier in the same way, you need to pass on the "name" to a function by using the correct "variable name". Same here with CSV_Identifier.

Not sure if I can make it any clearer, but if you just try it out, you will see it works :)

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

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

Post by ahk7 » 14 Nov 2020, 10:07

I've added a simple example script illustrating how to use CSV to get people started here https://github.com/hi5/CSV/blob/master/example.ahk

User avatar
iilabs
Posts: 296
Joined: 07 Jun 2020, 16:57

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

Post by iilabs » 07 Dec 2020, 12:46

I'm getting a Error: Call to nonexistent function. Tried two different examples with same error code.

Using latest AHK 64bit

User avatar
iilabs
Posts: 296
Joined: 07 Jun 2020, 16:57

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

Post by iilabs » 07 Dec 2020, 12:50

I think i figured it out. Must include?

#include, csv.ahk

User avatar
lmstearn
Posts: 681
Joined: 11 Aug 2016, 02:32
Contact:

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

Post by lmstearn » 18 Apr 2021, 01:48

Thanks for the library. :)
At one stage there was the consideration of venturing down the rabbit hole of coaxing old Windows Address Book/WLM contacts to port over to the People App without losing any info- such as Notes. It turns out to be a rather large warren, to be avoided like the plague. :sick:
The WLM CSV fields are included here if for nothing more than for reasons historical:
Spoiler
:arrow: itros "ylbbub eht tuO kaerB" a ni kcuts m'I pleH

pramach
Posts: 54
Joined: 24 Jan 2018, 08:20

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

Post by pramach » 14 Sep 2022, 08:02

Hi and thanks for the library

I have some questions where any help is appreciated

When I use a different delimiter in CSV_Load than a comma, the function Format4CSV is still checking for a comma. Should it not be the CSV_Delimiter used in CSV_Load ?

Assuming I have a CSV with 5 columns (A, B, C, D, E), and I want to move column 5 (E) to position 3 in order to get sequence A, B, E, C, D. How would be the best approach ?

Assuming I have a CSV with with 5 columns (A, B, C, D, E), and I want to add a column 6 (CSV_AddColumn) with a default value (all rows same value), what would be the best approach here ? I know that I can add values as a parameter of CSV_AddColumns, but in case I have thousands of rows it does not make an sense that way, or ?

In case I want to process multiple CSV files: CSV_Load -> modify ... -> CSV_Save ; Can I use always same identifier in my processing loop ? Or in other words, will the identifier be completely overwritten/Cleared when doing a new CSV_Load with same identifier ?


Thanks for any suggestions and helps.

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

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

Post by ahk7 » 25 Sep 2022, 05:05

Thanks for your comments and questions.

Note that it has been a long time since I looked at this code and I didn't actually write the library as such and I don't use all functions myself.

And as a reminder: you can use Excel or LibreOffice Calc via COM to read/update CSV (search the forum)
pramach wrote:
14 Sep 2022, 08:02
In case I want to process multiple CSV files: CSV_Load -> modify ... -> CSV_Save ; Can I use always same identifier in my processing loop ? Or in other words, will the identifier be completely overwritten/Cleared when doing a new CSV_Load with same identifier ?
Answer: A few quick tests shows that is the case, but you should try it yourself with a few test runs and always backup your data before doing so of course.
pramach wrote:
14 Sep 2022, 08:02
When I use a different delimiter in CSV_Load than a comma, the function Format4CSV is still checking for a comma. Should it not be the CSV_Delimiter used in CSV_Load ?
Answer: you are correct, given the age of the library I guess it shows "comma" is just the standard and other delimiters are not used (often?) by users of this library. I think I have a fix and post a new version once ready.
pramach wrote:
14 Sep 2022, 08:02
Assuming I have a CSV with with 5 columns (A, B, C, D, E), and I want to add a column 6 (CSV_AddColumn) with a default value (all rows same value), what would be the best approach here ? I know that I can add values as a parameter of CSV_AddColumns, but in case I have thousands of rows it does not make an sense that way, or ?
Answer: I probably wouldn't use the library to do it that case as the library has to loop over each cell.
I would use standard AutoHotkey commands as this will be a lot faster even for large(r) files (only limit could be your memory - if the files are huge use another method to avoid running into issues)
  1. FileRead the entire file
  2. Use StrReplace or RegExReplace to "append" the column (text) at the end of each line. (Do take the last line of the file into account which may not be processed by StrReplace or RegExReplace as it may lack the "new line" character you use to "find new line" and "replace with new column + newline")
  3. FileAppend to save to new file (or FileDelete original + FileAppend to same file name - testing with a new file is always a good idea, and backup your data)
Otherwise something like this, create a CSV list (comma delimited) of the same length as the rows present, and use that to add a new column (same goes for different values for each row, create a list and append as new column in one go - if you want you can add a new empty column first and then update each cell - see
viewtopic.php?p=242250#p242250 just use one "cell" first then update via CSV_ModifyCell() which will be fairly slow I suspect for large files)

Code: Select all

CSV_Load("file.csv","data")
MyDelimiter:=","
Loop, % CSV_TotalRows("data")
    newcolumndata .= "default value" MyDelimiter
newcolumndata:=RTrim(newcolumndata,MyDelimiter) ; remove the last one
CSV_AddColumn("data",newcolumndata)
CSV_Save("newfile.csv", "data")
ExitApp
pramach wrote:
14 Sep 2022, 08:02
Assuming I have a CSV with 5 columns (A, B, C, D, E), and I want to move column 5 (E) to position 3 in order to get sequence A, B, E, C, D. How would be the best approach ?
Answer: I don't have a good answer (yet) my first reaction would be to create a new file, would that work? You would need to CSV_Load() the new file to continue to work with the "updated" CSV data.
Edit: see CSVMoveColumns() viewtopic.php?p=484035#p484035
Last edited by ahk7 on 25 Sep 2022, 09:56, edited 1 time in total.

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

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

Post by ahk7 » 25 Sep 2022, 06:59

I updated Format4CSV() to check for the Delimiter not just "comma" - https://github.com/hi5/CSV.
Updating shouldn't cause any issues with a existing scripts, but if you only use comma separated files there is no need to update as such.

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

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

Post by ahk7 » 25 Sep 2022, 09:49

Additional function (will not be included in CSV as such) but perhaps useful

CSVSwapColumns() - swap columns

Code and example how to use, you need to add CSVSwapColumns() to your own script or #include it.

Code: Select all

#NoEnv
#SingleInstance, force
#Include CSV.ahk ; place this script and CSV.ahk in the same folder for it to work
                 ; or create a LIB folder where you have your scripts - https://www.autohotkey.com/docs/Functions.htm#lib

FileDelete, file.csv
FileAppend,
(
Year,Make,Model,Description,Price
1997,Ford,E350,"ac, abs, moon",3000.00
1999,Chevy,"Venture ""Extended Edition""","",4900.00
1999,Chevy,"Venture ""Extended Edition, Very Large""",,5000.00
1996,Jeep,Grand Cherokee,"MUST SELL! air, moon roof, loaded",4799.00
), file.csv

; how to use
CSV_Load("file.csv","data")
Loop % CSV_TotalRows("data")
     CSVSwapColumns("data",A_Index,1,5) ; swap price and year column

CSV_Save("swapcols-test.csv", "data")

; include this function in your own script
CSVSwapColumns(Identifier,SwapRow,SwapCol1,SwapCol2)
    {
     TempCol1:=CSV_ReadCell(identifier,SwapRow,SwapCol1)
     TempCol2:=CSV_ReadCell(identifier,SwapRow,SwapCol2)
     CSV_ModifyCell(identifier,TempCol2,SwapRow,Swapcol1)
     CSV_ModifyCell(identifier,TempCol1,SwapRow,Swapcol2)
    }

ExitApp
Last edited by ahk7 on 25 Sep 2022, 09:54, edited 1 time in total.

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

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

Post by ahk7 » 25 Sep 2022, 09:53

Additional function (will not be included in CSV as such) but perhaps useful

CSVMoveColumns() - Move column data to another column

Code and example how to use, you need to add CSVMoveColumns() to your own script or #include it.

Code: Select all

#NoEnv
#SingleInstance, force
#Include CSV.ahk ; place this script and CSV.ahk in the same folder for it to work
                 ; or create a LIB folder where you have your scripts - https://www.autohotkey.com/docs/Functions.htm#lib

FileDelete, file.csv
FileAppend,
(
1,2,3,4,5
1,2,3,4,5
1,2,3,4,5
1,2,3,4,5
), file.csv


; example how to use
CSV_Load("file.csv","data")

CSVMoveColumns("data",2,4) ; move 2nd column, move to the 4th column (4th column becomes 3rd column in this example)

CSV_Save("movecols-test.csv", "data")

; include this function in your own script
CSVMoveColumns(Identifier,FromCol,ToCol)
   {
    If (FromCol = ToCol)
      return 

    Delimiter:="`,"
    NewColOrder:=Delimiter

    TotalCols:=CSV_TotalCols(Identifier)
    TotalRows:=CSV_TotalRows(Identifier)

    Loop, % TotalCols
      NewColOrder .= A_Index Delimiter

    If (FromCol > ToCol)
      {
       highest:=FromCol
       lowest:=ToCol
       NewColOrder:=StrReplace(NewColOrder, Delimiter highest Delimiter, Delimiter)
       NewColOrder:=StrReplace(NewColOrder, Delimiter lowest Delimiter, Delimiter highest Delimiter lowest Delimiter)
      }
    else If (FromCol < ToCol)
      {
       highest:=ToCol
       lowest:=FromCol
       NewColOrder:=StrReplace(NewColOrder, Delimiter highest Delimiter, Delimiter highest Delimiter lowest Delimiter)
       NewColOrder:=StrReplace(NewColOrder, Delimiter lowest Delimiter, Delimiter,,1)
      }

    NewColOrder:=StrSplit(Trim(NewColOrder,Delimiter),Delimiter)

    Loop, % TotalRows
      {
       Row:=A_Index
       NewRow:=[]
       for k, v in NewColOrder
         NewRow.Push(CSV_ReadCell(identifier,Row,v))

       for k, v in NewRow
         CSV_ModifyCell(identifier,v,Row,k)
      }

   }

ExitApp

pramach
Posts: 54
Joined: 24 Jan 2018, 08:20

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

Post by pramach » 26 Sep 2022, 06:14

Cool Thanks a lot!

anwikipedia
Posts: 22
Joined: 19 Sep 2023, 22:28

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

Post by anwikipedia » 02 Oct 2023, 08:46

Code: Select all

#Include csv.ahk
CSV_Load("D:\OneDrive\p\ok\data.csv","data")

Sub := 123abc
Sub1 := Format("{1:s}",Sub)				;i want to convert this to string ; i have a element HMS[2] is not string 
Result := CSV_Search("data", Sub1)
I found how to use Varible's "find" function, but I am unsure of how to use an element array.
I tried it, but it didn't work. I might not be able to format it to a string. I'm hoping someone can help me.

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

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

Post by ahk7 » 02 Oct 2023, 11:05

Sub := 123abc doesn't do much I think, it should probably be Sub := "123abc"
Apart from that I'm not sure what you mean but these should all work

Code: Select all

Result := CSV_Search("data", "123abc")
Result := CSV_Search("data", Sub)
Result := CSV_Search("data", Sub1) ; with quoted Sub := "123abc"
Result := CSV_Search("data", HMS[2])
What is in HMS[2] that it doesn't work you think?

anwikipedia
Posts: 22
Joined: 19 Sep 2023, 22:28

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

Post by anwikipedia » 04 Oct 2023, 01:26

ahk7 wrote:
02 Oct 2023, 11:05
Sub := 123abc doesn't do much I think, it should probably be Sub := "123abc"
Apart from that I'm not sure what you mean but these should all work

Code: Select all

Result := CSV_Search("data", "123abc")
Result := CSV_Search("data", Sub)
Result := CSV_Search("data", Sub1) ; with quoted Sub := "123abc"
Result := CSV_Search("data", HMS[2])
What is in HMS[2] that it doesn't work you think?
I modified something i don't rememer but it work now .
Do you know anysoftware alternative excel but i can save the file to utf-8.
My Excel version 2016 don't have option save csv utf-8.

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

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

Post by ahk7 » 04 Oct 2023, 15:32

You can look have a look at FileEncoding + FileRead and then create a new file with FileAppend the encoding parameter.
https://www.autohotkey.com/docs/v1/lib/FileEncoding.htm
https://www.autohotkey.com/docs/v1/lib/FileAppend.htm
or use your text editor to open/save the document.

If you are actually working with Excel files you may be better off working directly with the excel files using COM - search the forum for many examples on how to use it to read, find, change data.
Hint: ComObjActive("Excel.Application")

Post Reply

Return to “Scripts and Functions (v1)”