12 replies to this topic

[JnLLnd]

This tutorial will help you get the basics of playing with CSV files and AHK objects. It requires the installation of the library ObjCSV. The most up-to-date version of this library can be found on GitHub:

https://raw.github.c.../Lib/ObjCSV.ahk
 
Follow the setup instructions in the header of the library or take look at the discussion on this forum:

http://www.autohotke...s-and-listview/
 


This script first builds a Gui with five buttons and a ListView control:

• Load file: load the file named "TheBeatles.txt" (a CSV file with all song titles from The Beatles) to an object collection and to a ListView control
• Read object: show how to read a given cell of a given record or how to parse the whole content of the object collection
• Change list: how to update the content of the file in the ListView programmatically
• Save file: how to save the content of the ListView the a CSV file with fields in the order of your choice
• Quit: leave the script

In this basic demo, many advanced options are used with the default values. An advanced demo will show how to use these options later.
 
This tutorial is divided in four sections:

• Section #1: Load file button
• Section #2: Read object button
• Section #3: Change list button
• Section #4: Save file button

In summary, download the library, this script and the CSV demo file and run the script. Play with the buttons. Edit the code as you wish. And read the comments of this topic for more details on the code behind each button.
 
If you have any comments or question, the "Reply to this topic" field below is there just for that!
 

;===============================================
/* ObjCSV Demo Basic v0.2
Written using AutoHotkey_L v1.1.09.03+ (http://l.autohotkey.net/)
By JnLlnd on AHK forum
2013-08-22+
*/

/*
Make sure that the libray ObjCSV.ahk in copied in one of these folders:
  %A_ScriptDir%\Lib\
  %A_MyDocuments%\AutoHotkey\Lib\
  path-to-the-currently-running-AutoHotkey.exe\Lib\

The ObjCSV.ahk library can be downloaded here:
https://raw.github.com/JnLlnd/ObjCSV/master/Lib/ObjCSV.ahk

Also, download a copy of the CSV file "TheBeatles.txt" and save it in the script directory:
https://raw.github.com/JnLlnd/ObjCSV/master/TheBeatles.txt
*/

#NoEnv
#SingleInstance


; Gui creation
/*
Gui with five buttons:
- Load file: load the file named "TheBeatles.txt" (a CSV file with all song titles from The Beatles) to an object collection and to a ListView
- Read object: show how to read a given cell of a given record or how to review the whole content of the object collection
- Change list: how to updaste the content of the file in the ListView programmatically
- Save file: how to content of the ListView the a CSV file with firlds in the order of your choice
- Quit: leave the script
*/
Gui, New, +Resize
Gui, Add, Button, x10 Default gButtonLoadFile, Load file
Gui, Add, Button, x+10 yp gButtonRead, Read object
Gui, Add, Button, x+10 yp gButtonChange, Change list
Gui, Add, Button, x+10 yp gButtonSaveFile, Save file
Gui, Add, Button, x+10 yp gGuiClose, Quit
Gui, Add, ListView, x10 r20 w800 -ReadOnly vMyLV
Gui, Show, Autosize
return



ButtonLoadFile:
strListViewName := "MyLV"
strFile := A_ScriptDir .  "\TheBeatles.txt"
strFields := ""
obj := ObjCSV_CSV2Collection(strFile, strFields) ; load the CSV file to a collection of objects
LV_Delete() ; delete all rows of ListView
loop, % LV_GetCount("Column")
	LV_DeleteCol(1) ; delete all columns of ListView
strFields := "str_Name,str_Album,lng_Track_Number,str_Genre,lng_Total_Time,lng_Size" ; field order in the ListView
ObjCSV_Collection2ListView(obj, , strListViewName, strFields) ; load the collection of objects to a ListView control
LV_ModifyCol(3, "Integer") ; allow numeric sorting
LV_ModifyCol(5, "Integer") ; allow numeric sorting
LV_ModifyCol(6, "Integer") ; allow numeric sorting
return



ButtonRead:
if !obj.MaxIndex() ; object is empty
	return
Gui +OwnDialogs
Random, intRecordNumber, 1, obj.MaxIndex()
MsgBox, , Random Song!, % "Song #" . intRecordNumber . " is """ . obj[intRecordNumber].str_Name . """"
Loop, % obj.MaxIndex() ; loop in each record in the obj object
{
	intRecordNumber := A_Index
	str := "--------------------------------------------------------------------------------`n"
	for strFieldName, strFieldValue in obj[intRecordNumber] ; loop each field in the record
		str := str . "[ " . strFieldName . " ]`t`t " . strFieldValue . "`n"
	str := str . "--------------------------------------------------------------------------------"
	MsgBox, 4, Song #%intRecordNumber%, %str% `n`nContinue?
	IfMsgBox, No
		Break
}
return



ButtonChange:
if !LV_GetCount("") ; ListView is empty
	return
Gui +OwnDialogs
blnAlternate := !blnAlternate
Loop, % LV_GetCount("") ; loop in each row in the ListView
{
	intRowNumber := A_Index
	Loop, % LV_GetCount("Column") ; loop in each column in the row
	{
		LV_GetText(strColData, intRowNumber, A_Index) ;  read this cell
		if blnAlternate
			StringUpper, strColData, strColData
		else
			StringUpper, strColData, strColData, T
		LV_Modify(intRowNumber, "Col" . A_Index, strColData) ;  update this cell
	}
}
MsgBox, , Case, Click "Change list" again to change case again
return



ButtonSaveFile:
if !LV_GetCount("") ; ListView is empty
	return
Gui +OwnDialogs  ; Forces user to dismiss the following dialog before using main window.
FileSelectFile, strFile, S18, %A_ScriptDir%\TheBeatles-Copy.txt, Save CSV file as?
if not strFile ; The user canceled the dialog.
	return
obj := ObjCSV_ListView2Collection() ; load the ListView data to a collection of objects 
strFields := "str_Name,str_Album,lng_Track_Number,str_Genre,lng_Total_Time,lng_Size" ;  field order in the saved file
ObjCSV_Collection2CSV(obj, strFile, 1, strFields, , 1) ; save the collection of objects to a CSV file and overwrite this file
MsgBox, 4, Display file?, File saved:`n`n%strFile%`n`nDisplay file?
IfMsgBox, Yes
	Run %strFile%
return



GuiSize: ; Expand or shrink the ListView in response to the user's resizing of the window.
if A_EventInfo = 1  ; The window has been minimized.  No action needed.
    return
; Otherwise, the window has been resized or maximized. Resize the ListView to match.
GuiControl, -Redraw, MyLV
intWidth := A_GuiWidth + 30
GuiControl, Move, MyLV, % "W" . (A_GuiWidth - 20) . " H" . (A_GuiHeight - 45)
LV_ModifyCol(1, (intWidth * 0.2))
LV_ModifyCol(2, (intWidth * 0.2))
LV_ModifyCol(3, (intWidth * 0.1))
LV_ModifyCol(4, (intWidth * 0.2))
LV_ModifyCol(5, (intWidth * 0.1))
LV_ModifyCol(6, (intWidth * 0.1))
LV_ModifyCol(7, (intWidth * 0.1))
GuiControl, +Redraw, MyLV
return



GuiClose:
ExitApp

 
This script can also be downloaded here:
https://raw.github.c...-Demo-Basic.ahk

[JnLLnd]

Before going further with this tutorial, I would like to say that I’m not a veteran AHK programmer. There may be other/better ways to achieve what my code is doing. If you have ideas or views on this, do not hesitate to comment. This way, this tutorial could be as informative for me :-)

 

Now, let's take a look at the actions behind our first button: "Load file".

ButtonLoadFile:
strListViewName := "MyLV"
strFile := A_ScriptDir .  "\TheBeatles.txt"
strFields := ""
obj := ObjCSV_CSV2Collection(strFile, strFields) ; load the CSV file to a collection of objects
LV_Delete() ; delete all rows of ListView
loop, % LV_GetCount("Column")
      LV_DeleteCol(1) ; delete all columns of ListView
strFields := "str_Name,str_Album,lng_Track_Number,str_Genre,lng_Total_Time,lng_Size" ; field order in the ListView
ObjCSV_Collection2ListView(obj, , strListViewName, strFields) ; load the collection of objects to a ListView control
LV_ModifyCol(3, "Integer") ; allow numeric sorting
LV_ModifyCol(5, "Integer") ; allow numeric sorting
LV_ModifyCol(6, "Integer") ; allow numeric sorting
return

This procedure opens the TheBeatles.txt CSV file, parses and stores its content in an Object variable and displays this data in the "MyLV" ListView control.

 

Before we examine the script code, take a look at the CSV file TheBeatles.txt. If a wide variety of CSV files formats can be found in the Universe, this file sticks to the most widely adopted conventions:

• its first line contain the header (the list of field names);
• subsequent lines hold the records (the list of 257 Beatles songs);
• field name in the header and field values in the records are separated by a comma (this is not called a "comma-separated-value" for nothing);
• when a field contains a comma (as in "Ob-La-Di, Ob-La-Da"), the value is encapsulated between double-quote to prevent the CSV parser to interpret this comma as field separator.

 

As we will see, various options not described in this basic tutorial allow to support files delimited with tabs or semi-colon (instead of comma) or encapsulated with single quotes (instead of double-quotes), with or without headers, even with values including end-of-lines. More to come in the advanced tutorial, later.

 

Now, back to the code. We will load our Beatles songs list in AHK.

obj := ObjCSV_CSV2Collection(strFile, strFields) ; load the CSV file to a collection of objects

The "ObjCSV_CSV2Collection" function will load the file "TheBeatles.txt" in the object variable "obj" created to store its content. You can imagine this object as a two-dimension array or a table: rows are the first dimension (the songs) and columns are the second dimension holding the various attributes of the songs (title, album, etc.).

 

To be more precise, technically, we should say that the "obj" variable is an array of pointers (I sometimes use the word "collection"), each of them pointing to one of the 257 records. Each record is itself an associative array (more on this in the next section) containing pairs of "fieldname = value", for example: "str_Name = Dig It", "str_Album = Let It Be", etc. Next section of this tutorial will demonstrate how you can interact with these objects.

 

The second parameter of this function is "strFields". First, this variable dictate what fields in the CSV file will be loaded. Since it is empty, by default all fields are read. Second, since this is a ByRef variable, its content is changed at the end of the function to return the CSV file header. This value can be used to change the order of fields or select what fields should be loaded in the ListView in the next step.

 

OK. The CSV file is now transferred to the collection and its content is ready to be displayed in our ListView control. After a few lines to clear the content of the ListView, if required, we transfer the collection to the ListView:

ObjCSV_Collection2ListView(obj, , strListViewName, strFields) ; load the collection of objects to a ListView control

The first parameter "obj" is our collection. Enough has been said about it. "strListViewName" is the name of our ListView control. It is a good practice to name Gui ListView controls because this name allows to avoid redraws while we write in a ListView. Performance is greatly improved by this simple practice. Lastly, the "strFields" string is used here to change the order of the fields in the list. It could also be used to exclude some fields from the view, if we wish, as we said.

 

Finally, after a few lines of code to set the columns properties, we are ready to interact with The Beatles collection in the list view! This will be the subject of the next section of this tutorial.

 

Do not hesitate to interrupt! I prefer interaction to monologue ;-)

[JnLLnd]

The button "Read object" fires the procedure "ButtonRead:" to do two things:

• display randomly one of the 257 songs of our collection;
• display each record of the collection, one at a time.

ButtonRead:
if !obj.MaxIndex() ; object is empty
	return
Gui +OwnDialogs
Random, intRecordNumber, 1, obj.MaxIndex()
MsgBox, , Random Song!, % "Song #" . intRecordNumber . " is """ . obj[intRecordNumber].str_Name . """"
Loop, % obj.MaxIndex() ; loop in each record in the obj object
{
	intRecordNumber := A_Index
	str := "--------------------------------------------------------------------------------`n"
	for strFieldName, strFieldValue in obj[intRecordNumber] ; loop each field in the record
		str := str . "[ " . strFieldName . " ]`t`t " . strFieldValue . "`n"
	str := str . "--------------------------------------------------------------------------------"
	MsgBox, 4, Song #%intRecordNumber%, %str% `n`nContinue?
	IfMsgBox, No
		Break
}
return

But before we try to read the object, it’s a good idea to check if we have something to read.

if !obj.MaxIndex() ; object is empty
	return

The command "obj.MaxIndex()" returns the number of items in our object. If the "obj" is empty, it returns 0 which is interpreted as false by the If command. In this case, we interrupt the procedure.

Random, intRecordNumber, 1, obj.MaxIndex()
MsgBox, , Random Song!, % "Song #" . intRecordNumber . " is """ . obj[intRecordNumber].str_Name . """"

If we have records to read, the "Random" command returns a number between 1 and 257. We use this number as an index to reach one of the songs: "obj[intRecordNumber]". Then we have to indicate that we want to show the title field of the record: ".str_Name".

 

Lets take the time to clarify this with a few word about object index and keys. There are two types of objects (see http://l.autohotkey....ocs/Objects.htm for more info):

• simple arrays - series of values indexed by numbers;
• associative arrays - series of values indexed by keys (or names).

 

"Obj" is a simple array. To read one item of this type of array, all we need is its index number (in our case, a number between 1 and 257), put between brackets like this: "obj[intRecordNumber]".

 

We could not display the content of this variable as-is with MsgBox because the content of "obj[intRecordNumber]" is not a simple value but an associative array. To read the value of one item in this record, we need its associated key (or field name).  For example, to read the song name, we put dot and the key "str_Name" like this: "obj[intRecordNumber].str_Name". To display its genre, we would use "obj[intRecordNumber].str_Genre", etc.

 

Another side note before we continue. The syntax above works only if key names are single word. If a key stands on two words (or more) we need to encapsulate the key name between brackets AND double-quotes, without the dot, like this: obj[intRecordNumber]["First Name"]. I lost quite a bit of time on that one...

 

OK. We know now how to read one value somewhere in our object. But how do we scan the whole thing? That’s what will do the next lines.

Loop, % obj.MaxIndex() ; loop in each record in the obj object
{
...
}

We’ve already seen "obj.MaxIndex()". We use it here to loop in the 257 records of our object. On each iteration of the loop, we use the "A_Index" variable to access this record. In fact, we safely put the "A_Index" AHK variable in our own variable "intRecordNumber" just to avoid confusion when we will enter a nested loop in a moment with the "For" command.

for strFieldName, strFieldValue in obj[intRecordNumber] ; loop each field in the record
	str := str . "[ " . strFieldName . " ]`t`t " . strFieldValue . "`n"

The "For key, value in array" command provides a convenient way to scan the content of an associative array. For each item in the array, the command sets the key name (or field name) in the first variable and the value associated to this key in the second variable. In our case, every values of "strFieldName" and "strFieldValue" are concatenated in the "str". At the end of the for loop, this string is passed to the MsgBox command to display the record content.

 

This is it for this second button! Next button will introduce the use of the ListView control to get a global view on the content of our collection.

[ELengefeld]

I am missing the lib in the following line.

 

 

#Include %A_ScriptDir%\JLDev.ahk ; some debugging tools or utilities 

 

 

 

Did I miss a step?

 

Thanks for the tutorial!

[JnLLnd]

Oops! My bad... Please delete this line that should not be part of the published script (I removed it from the original post).

 

The main library ObjCSV.ahk must be downloaded and installed in one of these directories:

• %A_ScriptDir%\Lib\
• %A_MyDocuments%\AutoHotkey\Lib\
• path-to-the-currently-running-AutoHotkey.exe\Lib\

 

That way, it does not have to be referenced with an #include statement.

 

Hope this help and thank you for the remark.

[Rafael Pi]

AHK is complaining about a missing comma in line 47 of the main library (ObjCSV.ahk):

ObjCSV_CSV2Collection(strFilePath, ByRef strFieldNames, blnHeader := true, blnMultiline := 1, blnProgress := 0, strFieldDelimiter := ",", strEncapsulator := """", strRecordDelimiter := "`n", strOmitChars := "`r")

¿Could strFieldDelimiter := "," be the culprit? I tried escaping it (as in "`,") but that didn't solved it.

Thank you very much for your effort! I'm following this topic closely now (not a professional programmer myself).

[JnLLnd]

Thank you for your interest, Rafael. 

 

The lib is OK on my side. I see 2 possible reasons for this error:

1. You dont have the proper AutoHotkey.exe version for this script. You should have AHK_L (aka v2) that can be downloaded here: http://l.autohotkey.net/
2. There is a line break somewhere in this line of your source file. To make sure you have a well formated file, you could download it here.

Or maybe there is another cause that more experienced AHK programers would know?

[JnLLnd]

This third part of the tutorial is much easier to get than the previous one. We will see here how to scan a ListView control, and how to read and update its content. This is only one example of what you can do after you loaded data from a CSV file to objects using the ObjCSV library.

ButtonChange:
if !LV_GetCount("") ; ListView is empty
      return
Gui +OwnDialogs
blnAlternate := !blnAlternate
Loop, % LV_GetCount("") ; loop in each row in the ListView
{
      intRowNumber := A_Index
      Loop, % LV_GetCount("Column") ; loop in each column in the row
      {
            LV_GetText(strColData, intRowNumber, A_Index) ;  read this cell
            if blnAlternate
                  StringUpper, strColData, strColData
            else
                  StringUpper, strColData, strColData, T
            LV_Modify(intRowNumber, "Col" . A_Index, strColData) ;  update this cell
      }
}
MsgBox, , Case, Click "Change list" again to change case again
return

The button "Change list" fires the procedure "ButtonChange:" to change the content of the ListView, alternating between uppercase and lowercase. The script loops through the whole list using two variations of the "LV_GetCount()" function that we already used in the previous sections of this tutorial.  Without parameters, LV_GetCount("") returns the number of rows in the ListView control. We use this value to loop each line.  Inside the loop, LV_GetCount("Column") returns the number of columns used in the nested loop.
 
Before the first loop, we turn the boolean variable "blnAlternate" from true to false and from false to true, each time the user click the "Change list" button.  Inside the loop for each row (intRowNumber), we get the text of each cell at the column A_Index with the command "LV_GetText(strColData, intRowNumber, A_Index)".

      LV_GetText(strColData, intRowNumber, A_Index) ;  read this cell
      if blnAlternate
            StringUpper, strColData, strColData
      else
            StringUpper, strColData, strColData, T
      LV_Modify(intRowNumber, "Col" . A_Index, strColData) ;  update this cell

The variable "strColData" is then converted to uppercase or lowercase depending on the value of blnAlternate, and stored back in the cell using the command "LV_Modify(intRowNumber, "Col" . A_Index, strColData)" that requires three parameters: the row number, the column number (in the format "Col1", "Col2", etc.) and the value to store in the cell.
 
In the last section of this tutorial, we will see how to save the modified data back to a CSV file using the functions "ObjCSV_ListView2Collection" and "ObjCSV_Collection2CSV" from the ObjCSV library.

[JnLLnd]

This fourth and last section of the ObjCSV basic tutorial will show you how use the ObjCSV library to transfer content from the ListView to the Object collection and how to save the data in the collection to a CSV file. Again, we will use the default values for many options. Advanced features of ObjCSV functions will soon be presented in another tutorial.

ButtonSaveFile:
if !LV_GetCount("") ; ListView is empty
	return
Gui +OwnDialogs  ; Forces user to dismiss the following dialog before using main window.
FileSelectFile, strFile, S18, %A_ScriptDir%\TheBeatles-Copy.txt, Save CSV file as?
if not strFile ; The user canceled the dialog.
	return
obj := ObjCSV_ListView2Collection() ; load the ListView data to a collection of objects 
strFields := "str_Name,str_Album,lng_Track_Number,str_Genre,lng_Total_Time,lng_Size" ;  field order in the saved file
ObjCSV_Collection2CSV(obj, strFile, 1, strFields, , 1) ; save the collection of objects to a CSV file and overwrite this file
MsgBox, 4, Display file?, File saved:`n`n%strFile%`n`nDisplay file?
IfMsgBox, Yes
	Run %strFile%
return

The button "Save File" fires the procedure "ButtonSaveFile:". After we make sure that we have content in the ListView, we ask the user for a file name. If the user provide a file name, we call the "ObjCSV_ListView2Collection()" function to transfer the data from the ListView to the "obj" object variable:

obj := ObjCSV_ListView2Collection() ; load the ListView data to a collection of objects

In this basic use of the function, we don’t provide any parameter. Instead, we use all the default values of the function:

• we take the data from the current ListView of the current Gui;
• we take the field (columns) in the order they appear in the ListView.

If no row is selected, the function will transfer all content of the ListView to the collection. But, if the user selected one row (using Click), a series of adjacent rows (using Shift-Click) or non contiguous rows (using Ctrl-Click or Shift-Ctrl-Click), only the selected rows will be transferred.
 
As you probably know, the user can click on a column name in the ListView to sort its rows. The "ObjCSV_ListView2Collection()" will transfer rows in the order they appear in the list, following the current sorting order.
 
Now, we are ready to save this data to a CSV file.

strFields := "str_Name,str_Album,lng_Track_Number,str_Genre,lng_Total_Time,lng_Size" ;  field order in the saved file
ObjCSV_Collection2CSV(obj, strFile, 1, strFields) ; save the collection of objects to a CSV file

The variable "strFields" contains a list of field names in the collection objects. This string will be used to set the order of the fields in the CSV file. In our case, we include all fields but it could also be used to select which fields to include in the file.
 
The "ObjCSV_Collection2CSV" function receives four parameters:

• "obj" is the object pointing to our collection of objects (our records), created a few lines above;
• "strFile" is the name of the CSV file to create;
• "1" (standing for True) is passed to the Boolean parameter "blnHeader" in the function to indicate that we want to include a header as the first line of our CSV file;
• and "strFields" determine the order of the fields in the CSV file.

The function will format the data to the coma-delimited format: one record per line and, for each line, columns separated by the default field delimiter (comma) and fields containing special character (like line breaks or comma) embedded in the default encapsulator character (double-quotes).
 
This was the last section of this tutorial. Stay tune for more options about the ObjCSV library in the advanced demo to be published soon on this forum.

[Rafael Pi]

After a few changes in my code editor and other stuff, the script and libraries loaded OK. I still don't know what happened, as I couldn't reproduce the error I reported. I got the sources from Github. Maybe a line ending character issue? Nevermind, I'll check out the tutorial... or maybe learn as I use the library! Thank you JnLLnd!

[Joe Glines]

This tutorial was incredibly helpful!   It not only explained how to use the ObjectCSV library but also drastically improved my knowledge of how objects work in AutoHotKey.  I wish I'd read this a couple of years ago when I was struggling to learn OOP when trying to scrape/manipulate the DOM on we pages!  

 

Very, very awesome!

[JnLLnd]

Thank you joetazz. Much appreciated :-)

[mmoole]

Thanks a lot for the library!

 

I have a beginner question: I want to load, sort and save a csv file. Currently, the sort part doesnt work in my script.

SortCSV(csvfile := "")
{

	
	csvfileobj := ObjCSV_CSV2Collection(csvfile, strFieldNames, 1, 0, 0)
	
	
	csvfileobj := ObjCSV_SortCollection(csvfileobj, "Field Name", "N", 0, "")

	
	ObjCSV_Collection2CSV(csvfileobj, csvfile, 1, "", 0, 1, ",", """", "", "")

	
	csvfileobj := ""
}

The resulting csv

* is not sorted by Field Name (it has a space in its name, in the original csv it's enclosed by ""

* enclosing "" are lost and only used where fields contents have a space or similar. But the original CSV uses "" to enclose every field - maybe that could be added as an option?

 

greetings

m

 

## edit

well, I found out that it is not a good idea to use the very same object for both - so sorting works now when using a different object for that :-)

Still it would be good for that use case, to retain the forced usage of "" on all fields :-)

 

greetings