Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

Fairly elaborate CSV functions


  • Please log in to reply
17 replies to this topic
trueski
  • Members
  • 121 posts
  • Last active: Jun 25 2014 09:12 PM
  • Joined: 08 Apr 2008
Update:
I forgot to enclose a variable with percent signs in the CSV_Save function which caused the function to add an extra line to the CSV file when saving it. Thanks for finding this bug HugoV.

I tried using the CSV functions posted here, http://www.autohotke...topic32833.html, but ran into quite a few bugs, there wasn't much functionality and the code was somewhat confusing for me to read. Here's my own CSV editing functions, I have only tested it with comma delimited files, because I don't use anything else.
I didn't add the functionality to sort or to accept cells with commas inside them, ("Cell, 1", "Cell2"). If anyone wants to add these features post the code and I'll add it to the post.

Sorry if all the brackets don't line up right, I use a different font that's a little easier on my eyes.

The only functions that should need additional clarification:
CSV_LVLoad(Gui, x, y, w, h, header, Sort?, AutoAdjustCol?)
Gui: Specify which window to create the list view
Header: Create headers in this format "Header1|Header2..."
Sort: Specify 0 to not sort or any column number to sort by that column
AutoAdjustCol: specify 0 to not adjust column widths, specify 1 to adjust widths to fit data
You can run this function multiple times to refresh the listview

CSV_LVSave(FileName, Delimiter, OverWrite?, Gui)
Delimiter: Delimiter used in file
Overwrite: Specify 1 to overwrite existing file(default)
Gui: specify which window has the ListView that will be saved
; AutoHotkey Version: 1.0.48
; Author:         trueski <trueski@gmail.com>
;
;##################################################    CSV    FUNCTIONS     ###############################################
;CSV_Load(FileName, Delimiter)                                        ;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(FileName, OverWrite?)                                       ;Save CSV file
;CSV_DeleteRow(RowNumber)                                             ;Delete a row
;CSV_AddRow("Cell1, Cell2...")                                        ;Add a row
;CSV_DeleteColumn(ColNumber)                                          ;Delete a column
;CSV_AddColumn("Cell1, Cell2...")                                     ;Add a column
;CSV_ModifyCell(NewValue, Row, Col)                                   ;Modify an existing cell
;CSV_ModifyRow("NewValue1, NewValue2...", RowNumber)                  ;Modify an existing row
;CSV_ModifyColumn("NewValue1, NewValue2...", ColNumber)               ;Modify an existing column
;CSV_Search(SearchText, Instance)                                     ;Search for text within
;CSV_SearchRow(SearchText, RowNumber, Instance)                       ;Search for text within a cell within a specific row
;CSV_SearchColumn(SearchText, ColNumber, Instance)                    ;Search for text within a cell within a specific column
;CSV_MatchCell(SearchText, Instance)                                  ;Search for a cell containing exactly the data specified
;CSV_MatchRow("SearchText1, SearchText2", Instance)                   ;Search for a row containing exactly the data specified
;CSV_MatchCol("SearchText1, SearchText2", Instance)                   ;Search for a column containing exactly the data specified
;CSV_ReadCell(Row, Column)                                            ;Read data from the specified cell
;CSV_ReadRow(RowNumber)                                               ;Read data from the specified row
;CSV_ReadCol(ColNumber)                                               ;Read data from the specified column
;CSV_LVLoad(Gui, x, y, w, h, header, Sort?, RowIdentification?, AutoAdjustCol?)         ;Load data into a listview in the specified gui window
;CSV_LVSave(FileName, Delimiter, OverWrite?, Gui)                                       ;Save the specified listview as a CSV file
;####################################################################################################################
CSV_Load(FileName, Delimiter="`,")
  {
	Local Row
	Local Col
	
	Loop, Read, %FileName%
	  {
		Row := A_Index
		Loop, Parse, A_LoopReadLine, %Delimiter%
		  {
			Col := A_Index
			CSV_Row%Row%_Col%Col% := A_LoopField
		  }
	  }
	    CSV_TotalRows := Row
		CSV_TotalCols := Col
		CSV_Delimiter := Delimiter
		SplitPath, FileName, CSV_FileName, CSV_Path
		
		IfNotInString, FileName, `\
		  {
			CSV_FileName := FileName
			CSV_Path := A_ScriptDir
	  }
	  
		CSV_FileNamePath = %CSV_Path%\%CSV_FileName%
    }
;####################################################################################################################
CSV_TotalRows()
  { 
    global	
	Return %CSV_TotalRows%
  }
;####################################################################################################################
CSV_TotalCols()
  { 
    global	
	Return %CSV_TotalCols%
  }
;####################################################################################################################
CSV_Delimiter()
  { 
    global	
	Return %CSV_Delimiter%
  }
;####################################################################################################################
CSV_FileName()
  { 
    global	
	Return %CSV_FileName%
  }
;####################################################################################################################
CSV_Path()
  { 
    global	
	Return %CSV_Path%
  }
;####################################################################################################################
CSV_FileNamePath()
  { 
    global	
	Return %CSV_FileNamePath%
  }
;####################################################################################################################
CSV_Save(FileName, OverWrite="1")
  {
	Local Row
	Local Col
	
	If OverWrite = 0
	  IfExist, %FileName%
	    Return
		
	FileDelete, %FileName%

    EntireFile = 
	Loop, %CSV_TotalRows%
	  {
		  Row := A_Index
	      Loop, %CSV_TotalCols%
		    {
				Col := A_Index
				EntireFile .= CSV_Row%Row%_Col%Col%
				If Col <> %CSV_TotalCols%
				  EntireFile .= CSV_Delimiter
		    }
		      If Row <> %CSV_TotalRows%
			  EntireFile .= "`n"
	   }
		  FileAppend, %EntireFile%, %FileName%
  }
;####################################################################################################################
CSV_DeleteRow(RowNumber)
  {
	Local Row
	Local Col
	Local NewRow
	
	Loop, %CSV_TotalRows%
	  {
		Row := A_Index
	    NewRow := Row + 1
		If Row < %RowNumber%
		  Continue
		  
		Else
	      Loop, %CSV_TotalCols%
		    {
				Col := A_Index
				CSV_Row%Row%_Col%Col% := CSV_Row%NewRow%_Col%Col%
		    }
	  }
	CSV_TotalRows --
  }
;####################################################################################################################
CSV_AddRow(RowData)
  {
    global
	CSV_TotalRows ++
	Loop, Parse, RowData, `,
		CSV_Row%CSV_TotalRows%_Col%A_Index% := A_LoopField
	}
;####################################################################################################################
CSV_DeleteColumn(ColNumber)
  {
	Local Row
	Local Col
	Local NewCol

	Loop, %CSV_TotalRows%
	  {
		  Row := A_Index
	      Loop, %CSV_TotalCols%
		    {
				Col := A_Index
				NewCol := Col + 1

				If Col < %ColNumber%
				  Continue
				  
				Else
				  CSV_Row%Row%_Col%Col% := CSV_Row%Row%_Col%NewCol%
		    }
      }
    CSV_TotalCols --
  }
;####################################################################################################################
CSV_AddColumn(ColData)
  {
    global
	CSV_TotalCols ++
	Loop, Parse, ColData, `,
		CSV_Row%A_Index%_Col%CSV_TotalCols% := A_LoopField
  }
;####################################################################################################################
CSV_ModifyCell(Value, Row, Col)
  {
	global
	CSV_Row%Row%_Col%Col% := Value
  }
;####################################################################################################################
CSV_ModifyRow(Value, RowNumber)
  {
	Loop, Parse, Value, `,
	  CSV_Row%RowNumber%_Col%A_Index% := A_LoopField
   }
;####################################################################################################################	
CSV_ModifyColumn(Value, ColNumber)
  {
	Loop, Parse, Value, `,
	  CSV_Row%A_Index%_Col%ColNumber% := A_LoopField
  }
;####################################################################################################################
CSV_Search(SearchText, Instance=1)
  {
	Local Row
	Local Col
	Local FoundInstance
	
	Loop, %CSV_TotalRows%
	  {
		  Row := A_Index
	      Loop, %CSV_TotalCols%
		    {
				  Col := A_Index
                  CurrentString := CSV_Row%Row%_Col%Col%
				  IfInString, CurrentString, %SearchText%
				    {
				      FoundInstance ++
					  CurrentCell = %Row%`,%Col%
					  
					  If FoundInstance = %Instance%
					    Return %CurrentCell%
					}
			}
	  }
    Return 0
  }
;####################################################################################################################
CSV_SearchRow(SearchText, RowNumber, Instance=1)
  {
	Local Col
	Local FoundInstance
	
	Loop, %CSV_TotalCols%
		{
			Col := A_Index
			CurrentString := CSV_Row%RowNumber%_Col%Col%
			IfInString, CurrentString, %SearchText%
				{
					FoundInstance ++
					  
					If FoundInstance = %Instance%
					  Return %Col%
				}
	    }
	  Return 0
  }
;####################################################################################################################
CSV_SearchColumn(SearchText, ColNumber, Instance=1)
  {
	Local Row
	Local FoundInstance
	
	Loop, %CSV_TotalRows%
		{
			Row := A_Index
			CurrentString := CSV_Row%Row%_Col%ColNumber%
			IfInString, CurrentString, %SearchText%
				{
					FoundInstance ++
					  
					If FoundInstance = %Instance%
					  Return %Row%
				}
	    }
	  Return 0
  }
;####################################################################################################################
CSV_MatchCell(SearchText, Instance=1)
  {
	Local Row
	Local Col
	Local FoundInstance
	
	Loop, %CSV_TotalRows%
	  {
		  Row := A_Index
	      Loop, %CSV_TotalCols%
		    {
				  Col := A_Index
                  CurrentString := CSV_Row%Row%_Col%Col%
				  IfEqual, CurrentString, %SearchText%
				    {
				      FoundInstance ++
					  CurrentCell = %Row%`,%Col%
					  
					  If FoundInstance = %Instance%
					    Return %CurrentCell%
					}
			}
	  }
    Return 0
  }
;####################################################################################################################
CSV_MatchRow(SearchText, Instance=1)
 {
	Local Col
	Local Row
	Local CurrentRow
	Local FoundInstance
	
	Loop, %CSV_TotalRows%
	  {
		  Row := A_Index
		  CurrentRow =
	      Loop, %CSV_TotalCols%
		    {
			    Col := A_Index
			    CurrentRow .= CSV_Row%Row%_Col%Col%
				If Col <> %CSV_TotalCols%
				  CurrentRow .= "`,"
				  
			    IfEqual, CurrentRow, %SearchText%
				  {
					FoundInstance ++
					  
					If FoundInstance = %Instance%
					  Return %Row%
				  }
	        }
        }
    Return 0
  }
;####################################################################################################################
CSV_MatchCol(SearchText, Instance=1)
  {
	Local Col
	Local Row
	Local CurrentCol
	Local FoundInstance
	
    Loop, %CSV_TotalCols%
	  {	
		  Col := A_Index
		  CurrentCol =
	      Loop, %CSV_TotalRows%
		    {
			    Row := A_Index
			    CurrentCol .= CSV_Row%Row%_Col%Col%
				If Row <> %CSV_TotalRows%
				  CurrentCol .= "`,"
				  
			    IfEqual, CurrentCol, %SearchText%
				  {
					FoundInstance ++
					  
					If FoundInstance = %Instance%
					  Return %Col%
				  }
	        }
      }
  Return 0
}
;####################################################################################################################
CSV_ReadCell(Row, Col)
  {
	Local CellData
	CellData := CSV_Row%Row%_Col%Col%
	Return %CellData%
  }
;####################################################################################################################
CSV_ReadRow(RowNumber)
  {
	Local CellData
	
	Loop, %CSV_TotalCols%
	  {
	    RowData .= CSV_Row%RowNumber%_Col%A_Index%
		If A_Index <> %CSV_TotalCols%
		   RowData .= "`,"
	  }
	Return %RowData%
  }
;####################################################################################################################
CSV_ReadCol(ColNumber)
  {
	Local CellData
	
	Loop, %CSV_TotalRows%
	  {
	    ColData .= CSV_Row%A_Index%_Col%ColNumber%
		If A_Index <> %CSV_TotalRows%
		   ColData .= "`,"
	  }
	Return %ColData%
  }
;####################################################################################################################
CSV_LVLoad(Gui=1, x=10, y=10, w="", h="", header="", Sort=0, AutoAdjustCol=1)
  {
	Local Row

	 If CSV_LVAlreadyCreated =
	   {
	    Gui, %Gui%:Add, ListView, vListView%Gui% x%x% y%y% w%w% h%h%, %header%
		CSV_LVAlreadyCreated = 1
	    }
	
	;Set GUI window, clear any existing data
	Gui, %Gui%:Default
    GuiControl, -Redraw, ListView%Gui%
    Sleep, 200
	LV_Delete()
	
	;Add Data
	Loop, %CSV_TotalRows%
	  LV_Add("", "")
	
	Loop, %CSV_TotalRows%
	  {
		Row := A_Index
		Loop, %CSV_TotalCols%
	        LV_Modify(Row, "Col" . A_Index, CSV_Row%Row%_Col%A_Index%)
	  }

	
	;Display Data
	If Sort <> 0
	  LV_ModifyCol(Sort, "Sort")
      
      
     If AutoAdjustCol = 1
     LV_ModifyCol()
     GuiControl, +Redraw, ListView%Gui%
  }
;####################################################################################################################
CSV_LVSave(FileName, Delimiter=",",OverWrite=1, Gui=1)
  {
	Gui, %Gui%:Default
	Rows := LV_GetCount()
	Cols := LV_GetCount("Col")
	
	IfExist, %FileName%
	  If OverWrite = 0
	    Return 0
	
	FileDelete, %FileName%
	
	Loop, %Rows%
	  {
		  FullRow =
		  Row := A_Index
		  
		  Loop, %Cols%
		    {
	          LV_GetText(CellData, Row, A_Index)
			  FullRow .= CellData
			  
			  If A_Index <> %Cols%
			    FullRow .= Delimiter
		    }
		
			If Row <> %Rows%
			  FullRow .= "`n"
			  
			EntireFile .= FullRow
		}
    FileAppend, %EntireFile%, %FileName%
  }

-trueski-

SoLong&Thx4AllTheFish
  • Members
  • 4999 posts
  • Last active:
  • Joined: 27 May 2007
Try this example:
in=
(
"1,1",2,3
4,5,6
7,8,9
)
FileDelete, in.csv
FileAppend, %in%, in.csv
CSV_Load("in.csv")
CSV_ModifyCell(111, 1, 1)
CSV_Save("out.csv", 1)
ExitApp
#include lib.ahk
It fails. Cell 1,1 should be 111 after ModifyCell but it is 111,1".
Check out <!-- m -->http://www.autohotke...topic32938.html<!-- m --> for Format4CSV() and DerRaphael DSV script for pointers

Second example:
; Test data taken from http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm#ExampleData
in=
(join`r`n
John,Doe,120 jefferson st.,Riverside, NJ, 08075
Jack,McGinnis,220 hobo Av.,Phila, PA,09119
"John ""Da Man""",Repici,120 Jefferson St.,Riverside, NJ,08075
Stephen,Tyler,"7452 Terrace ""At the Plaza"" road",SomeTown,SD, 91234
,Blankman,,SomeTown, SD, 00298
"Joan ""the bone"", Anne",Jet,"9th, at Terrace plc",Desert City,CO,00123
)
FileDelete, in2.csv
FileAppend, %in%, in2.csv
CSV_Load("in2.csv")
CSV_Save("out2.csv")
Compare the two, they should be the same but out2 has extra , added on each row.

trueski
  • Members
  • 121 posts
  • Last active: Jun 25 2014 09:12 PM
  • Joined: 08 Apr 2008

I didn't add the functionality to sort or to accept cells with commas inside them, ("Cell, 1", "Cell2"). If anyone wants to add these features post the code and I'll add it to the post.


-trueski-

segalion
  • Guests
  • Last active:
  • Joined: --
Could be better put CSV_TotalRows and CSV_TotalCols to max and lot last
rows and cols loop.


CSV_Load(FileName, Delimiter="`,")
	{
	Local Row
	Local Col
    CSV_TotalRows := 0
    CSV_TotalCols := 0
   
	Loop, Read, %FileName%
		{
		Row := A_Index
		if (CSV_TotalRows < Row )
			CSV_TotalRows := Row
		Loop, Parse, A_LoopReadLine, %Delimiter%
			{
			Col := A_Index
			CSV_Row%Row%_Col%Col% := A_LoopField
			if (CSV_TotalCols < Col)
				CSV_TotalCols := Col
			}
		}
      CSV_Delimiter := Delimiter
      SplitPath, FileName, CSV_FileName, CSV_Path
      
      IfNotInString, FileName, `\
        {
         CSV_FileName := FileName
         CSV_Path := A_ScriptDir
     }
    CSV_FileNamePath = %CSV_Path%\%CSV_FileName%
    }


Yurii
  • Members
  • 2 posts
  • Last active: Apr 04 2011 09:05 AM
  • Joined: 14 Jun 2009
I cant figure out how to use the search function??

;CSV_SearchRow(SearchText, RowNumber, Instance) ;Search for text within a cell within a specific row

Can you please give an example please?? of this function being used...

tinku99
  • Members
  • 560 posts
  • Last active: Feb 08 2015 12:54 AM
  • Joined: 03 Aug 2007
May as well include defaults for the header, otherwise no data shows up with default options:
CSV_LVLoad(Gui=1, x=10, y=10, w="", h="", header="1|2|3|4", Sort=0, AutoAdjustCol=1)

Also, since the builtin sort function doesn't work on tables, while the LV_ModifyCol has some functionality:
consider adding functiond to load and save csv from a variable:
csv_loadvar(Var, Delimiter="`,")
  {
   Local Row
   Local Col
   
   Loop, Parse, Var,`n
     {
      Row := A_Index
      Loop, Parse, A_LoopField, %Delimiter%
        {
         Col := A_Index
         CSV_Row%Row%_Col%Col% := A_LoopField
        }
     }
       CSV_TotalRows := Row
      CSV_TotalCols := Col
      CSV_Delimiter := Delimiter
}

LVtoCSV(Var, Delimiter=",",OverWrite=1, Gui=1)
  {
   Gui, %Gui%:Default
   Rows := LV_GetCount()
   Cols := LV_GetCount("Col")
   
   If Var
     If OverWrite = 0
       Return 0
   
%Var% := ""
   
   Loop, %Rows%
     {
        FullRow =
        Row := A_Index
       
        Loop, %Cols%
          {
             LV_GetText(CellData, Row, A_Index)
           FullRow .= CellData
          
           If A_Index <> %Cols%
             FullRow .= Delimiter
          }
		  StringTrimRight, FullRow, FullRow, 1      
         If Row <> %Rows%
           FullRow .= "`n"

         %Var% .= FullRow
      }
return %Var%
  }


segalion
  • Guests
  • Last active:
  • Joined: --
CSV_ReadRow(RowNumber)
  {
   Local RowData,
   
   Loop, %CSV_TotalCols%
     {
       RowData .= CSV_Row%RowNumber%_Col%A_Index%
      If A_Index <> %CSV_TotalCols%
         RowData .= "`,"
     }
   Return %RowData%
  }

Local RowData instead Local CellData


Thanks for this functions...

SoLong&Thx4AllTheFish
  • Members
  • 4999 posts
  • Last active:
  • Joined: 27 May 2007
To further expand this lib you could add

Format4CSV by Rhys
<!-- m -->http://www.autohotke...topic27233.html<!-- m -->
to CSV_Load

and

Delimiter Seperated Values by DerRaphael
<!-- m -->http://www.autohotke...280.html#203280<!-- m -->
to CSV_Save

Quick tests show it works but check your data before/after to see it doesn't mess it up somewhere.

CSV_Load(FileName, Delimiter="`,")
  {
   Local Row
   Local Col
   
   FileRead, CSVFile, %FileName%
   Loop, Parse, CSVFile, `n, `r
     {
      Col := ReturnDSVArray(A_LoopField, "CSV_Row" . A_Index . "_Col", Delimiter)
     Row := A_Index
     ;Loop, Parse, A_LoopReadLine, %Delimiter%
      ;  {
      ;  Col := A_Index
      ;  CSV_Row%Row%_Col%Col% := A_LoopField
      ;  }
     }
      CSV_TotalRows := Row
      CSV_TotalCols := Col
      CSV_Delimiter := Delimiter
      SplitPath, FileName, CSV_FileName, CSV_Path
     
      IfNotInString, FileName, `\
        {
         CSV_FileName := FileName
         CSV_Path := A_ScriptDir
        }
   
      CSV_FileNamePath = %CSV_Path%\%CSV_FileName%
    }

CSV_Save(FileName, OverWrite="1")
  {
   Local Row
   Local Col
   
   If OverWrite = 0
     IfExist, %FileName%
       Return
     
   FileDelete, %FileName%

    EntireFile =
   Loop, %CSV_TotalRows%
     {
        Row := A_Index
         Loop, %CSV_TotalCols%
          {
            Col := A_Index
            EntireFile .= Format4CSV(CSV_Row%Row%_Col%Col%)
            If Col <> %CSV_TotalCols%
              EntireFile .= CSV_Delimiter
          }
            If Row <> CSV_TotalRows
           EntireFile .= "`n"
      }
       StringTrimRight, EntireFile, EntireFile, 1 ; trim trailing `n (would add empty line otherwise)
        FileAppend, %EntireFile%, %FileName%
  }

 
; Format4CSV by Rhys
; http://www.autohotkey.com/forum/topic27233.html 
Format4CSV(F4C_String)
{
   Reformat:=False ;Assume String is OK
   IfInString, F4C_String,`n ;Check for linefeeds
      Reformat:=True ;String must be bracketed by double quotes
   IfInString, F4C_String,`r ;Check for linefeeds
      Reformat:=True
   IfInString, F4C_String,`, ;Check for commas
      Reformat:=True
   IfInString, F4C_String, `" ;Check for double quotes
   {   Reformat:=True
      StringReplace, F4C_String, F4C_String, `",`"`", All ;The original double quotes need to be double double quotes
   }
   If (Reformat)
      F4C_String=`"%F4C_String%`" ;If needed, bracket the string in double quotes
   Return, F4C_String
}

; Delimiter Seperated Values by DerRaphael
; http://www.autohotkey.com/forum/post-203280.html#203280
;
; Proof of Concept to extract DSV (Delimiter Seperator Values)
;      - adapted for AHK by derRaphael / 21st July 2008 -
;                           derRaphael@oleco.net
; Following rules apply:
;   You have to set a delimiter char and an encapsulation char.
;   1) If you're using the delimeter char within your value, the value has
;      to be surrounded by your encapsulation char. One at beginning and one
;      at its end.
;   2) If you're using your encapsulation char within your value you have to
;      double it each time it occurs and surround your value as in rule 1.
; Remarks:
;   The whole concept will break, when using same EOL (End Of Line) as LineBreaks
;   in a value as in the entire file. Either you will have to escape these chars
;   somehow or use a single linefeed (`n) in values and carriage return linefeed
;   (`r`n) as EOL in your DSV file.
;   Encapsulation and delimiter chars have to be single Chars. Strings containing
;   more than one char are not supported by concept.
;CurrentDSVLine=a,b,c,"d,e","f"","",g",,i
;
;Loop, % ReturnDSVArray(CurrentDSVLine)
;   MsgBox % A_Index ": " DSVfield%A_Index%

ReturnDSVArray(CurrentDSVLine, ReturnArray="DSVfield", Delimiter=",", Encapsulator="""")
{
   global
   if ((StrLen(Delimiter)!=1)||(StrLen(Encapsulator)!=1)) {
      return -1                            ; return -1 indicating an error ...
   }
   SetFormat,integer,H                      ; needed for escaping the RegExNeedle properly
   local d := SubStr(ASC(delimiter)+0,2)    ; used as hex notation in the RegExNeedle
   local e := SubStr(ASC(encapsulator)+0,2) ; used as hex notation in the RegExNeedle
   SetFormat,integer,D                      ; no need for Hex values anymore

   local p0 := 1                            ; Start of search at char p0 in DSV Line
   local fieldCount := 0                    ; start off with empty fields.
   CurrentDSVLine .= delimiter              ; Add delimiter, otherwise last field
   ;                                          won't get recognized
   Loop
   {
      Local RegExNeedle := "\" d "(?=(?:[^\" e "]*\" e "[^\" e "]*\" e ")*(?![^\" e "]*\" e "))"
      Local p1 := RegExMatch(CurrentDSVLine,RegExNeedle,tmp,p0)
      ; p1 contains now the position of our current delimitor in a 1-based index
      fieldCount++                         ; add count
      local field := SubStr(CurrentDSVLine,p0,p1-p0)
      ; This is the Line you'll have to change if you want different treatment
      ; otherwise your resulting fields from the DSV data Line will be stored in AHK array
      if (SubStr(field,1,1)=encapsulator) {
         ; This is the exception handling for removing any doubled encapsulators and
         ; leading/trailing encapsulator chars
         field := RegExReplace(field,"^\" e "|\" e "$")
         StringReplace,field,field,% encapsulator encapsulator,%encapsulator%, All
      }
      Local _field := ReturnArray A_Index  ; construct a reference for our ReturnArray name
      %_field% := field                    ; dereference _field and assign our value to it
      if (p1=0) {                          ; p1 is 0 when no more delimitor chars have been found
         fieldCount--                     ; so correct fieldCount due to last appended delimitor
         Break                            ; and exit loop
      } Else
         p0 := p1 + 1                     ; set the start of our RegEx Search to last result
   }                                        ; added by one
   return fieldCount
}


kdoske
  • Members
  • 138 posts
  • Last active: Nov 06 2012 01:58 AM
  • Joined: 17 Dec 2008

To further expand this lib you could add

Format4CSV by Rhys
<!-- m -->http://www.autohotke...topic27233.html<!-- m -->
to CSV_Load

and

Delimiter Seperated Values by DerRaphael
<!-- m -->http://www.autohotke...280.html#203280<!-- m -->
to CSV_Save

Quick tests show it works but check your data before/after to see it doesn't mess it up somewhere.


This is fantastic, thank you. OP, please replace original functions with the ones above!

kdoske
  • Members
  • 138 posts
  • Last active: Nov 06 2012 01:58 AM
  • Joined: 17 Dec 2008

I cant figure out how to use the search function??

;CSV_SearchRow(SearchText, RowNumber, Instance) ;Search for text within a cell within a specific row

Can you please give an example please?? of this function being used...


I realize this is almost a year to late but I figured I would throw it up incase anyone else is having any problems.

search:
loop,
{
  searchmatch := CSV_SearchColumn("2010-02-02", 1, a_index)
  if searchmatch = 0
    break
  msgbox, %searchmatch%
}
return

Modify:
CSV_ModifyCell("modify test 1", 2, 5) 
lv_modify(2, "col5", "modify test 1")
CSV_Save("1.csv", 1)
return

delete:
CSV_DeleteRow(2) 
lv_delete(2)
CSV_Save("1.csv", 1)
return


kdoske
  • Members
  • 138 posts
  • Last active: Nov 06 2012 01:58 AM
  • Joined: 17 Dec 2008
Ok now i have a question. How do I use the 'CSV_AddRow' function if the row I have has a cell with a comma in it. If I use the one in the original functions in combination with Hugov's functions a problem is created if I add the quotes manually. They are added fine but when I save they get enclosed in double quotes in the save file.

I looked to the new modified 'CSV_load' function created by hugov but sad to say its a little confusing to me. It looks like its handled in 'ReturnDSVArray' but i just can't figure it out! Any help would be appreciated.


This is the current addrow:
CSV_AddRow(RowData)
  {
    global
   CSV_TotalRows ++
   Loop, Parse, RowData, `,
      CSV_Row%CSV_TotalRows%_Col%A_Index% := A_LoopField
   }


SoLong&Thx4AllTheFish
  • Members
  • 4999 posts
  • Last active:
  • Joined: 27 May 2007

function if the row I have has a cell with a comma in it. If I use the one in the original functions in combination with Hugov's functions a problem is created if I add the quotes manually. They are added fine but when I save they get enclosed in double quotes in the save file.


That is what is supposed to happen to have correctly formatted CSV:

1,2,3 -> OK 3 cols
1,2,3,4 -> Bad 3 cols -> has become 4 cols
1,2,"3,4" -> OK 3 cols because of the ""

kdoske
  • Members
  • 138 posts
  • Last active: Nov 06 2012 01:58 AM
  • Joined: 17 Dec 2008

function if the row I have has a cell with a comma in it. If I use the one in the original functions in combination with Hugov's functions a problem is created if I add the quotes manually. They are added fine but when I save they get enclosed in double quotes in the save file.


That is what is supposed to happen to have correctly formatted CSV:

1,2,3 -> OK 3 cols
1,2,3,4 -> Bad 3 cols -> has become 4 cols
1,2,"3,4" -> OK 3 cols because of the ""


Thanks for the quick response. Let me explain in a code breakdown, because I think I explained it wrong above.

If I use the add feature like this

tempdata = 1,2,"3,a",4
CSV_AddRow(tempdata)

It works great while the script is running. Once I use the save command however It will add another set of quotes around the already exsisting quotes:

1,2,""3,a"",4

or it may actually look like this(im at work, the actual save is on my computer at home)

1,2," ""3,a"" ",4

SoLong&Thx4AllTheFish
  • Members
  • 4999 posts
  • Last active:
  • Joined: 27 May 2007
Tested should work
CSV_AddRow(RowData)
  {
    global
   CSV_TotalRows ++
;   Loop, Parse, RowData, `,
;      CSV_Row%CSV_TotalRows%_Col%A_Index% := A_LoopField
  ReturnDSVArray(RowData, "CSV_Row" . CSV_TotalRows . "_Col")
   }

Edit: This library uses
Loop, Parse, Value, `,
a lot which may simply break certain CSV files, with ReturnDSVArray and Format4CSV this can be resolved. CSV_AddRow is just one example, CSV_ModifyRow would be another etc etc.

kdoske
  • Members
  • 138 posts
  • Last active: Nov 06 2012 01:58 AM
  • Joined: 17 Dec 2008
Thanks hugov!

I will test it out when I get home. Looks like I'll have to rewrite most of the library. It will take me a while but when I do finally figure it out i'll post up the modified version for everyone.