Jump to content

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

CSV Library [lib]


  • Please log in to reply
52 replies to this topic
kdoske
  • Members
  • 138 posts
  • Last active: Nov 06 2012 01:58 AM
  • Joined: 17 Dec 2008
I know there are already a couple of these out there but I needed something more flexible that would allow me to manipulate more then one CSV file at a time. This set of CSV functions is a complete turn key solution that is a collaboration of works from Me, trueski, DerRaphael, and Rhys (There could be other's, im not sure).

Like I said above its main benefit is that it allows you to load multiple CSV files at once in one script by using a "CSV_Identifier" in each function. Basically every time you load a CSV file using the CSV_Load Function you include a custom name to Label that CSV file with. In doing so allows one program to contain many CSV files that can all been manipulated at the same time using the functions below.

Another benefit of this function set is the auto format of the CSV file that was created by DerRaphael and Rhys. I used them pretty much ever time there could be a problem with formatting. If you see a function that I have not covered let me know

Anyway, it will probably make more sense if you just check out the code...


Updated with bug fixes on 09/22/2012. Tested with AHK_L.
; includes bugfixes from:
; - http://www.autohotkey.com/forum/viewtopic.php?p=400669#400669
; - http://www.autohotkey.com/forum/viewtopic.php?p=453352#453352
; AutoHotkey_L Version: 1.1.08.01
; Author: Kdoske, trueski, SoLong&Thx4AllTheFish, segalion
; http://www.autohotkey.com/forum/viewtopic.php?p=329126#329126
;##################################################    CSV    FUNCTIONS     

###############################################
;if A Functions Field requires commas do not use spaces after each comma exp: 'text1,text2,text3,text,4'
;Encapsulation must be quotations, example: 'text1, "text, 2", text3, text4'
;When you CSV_Load a blank file you must specify the column count before adding new rows or columns with a command 

similar to: %CSV_Identifier%CSV_TotalCols := 'column count'.
;CSV_Load(FileName, CSV_Identifier, Delimiter)                                      ;Load CSV file into memory, must 

complete first.
;CSV_TotalRows(CSV_Identifier)                                                      ;Return total number of rows
;CSV_TotalCols(CSV_Identifier)                                                      ;Return total number of columns
;CSV_Delimiter(CSV_Identifier)                                                      ;Return the delimiter used
;CSV_FileName(CSV_Identifier)                                                       ;Return the filename
;CSV_Path(CSV_Identifier)                                                           ;Return the path
;CSV_FileNamePath(CSV_Identifier)                                                   ;Return the filename with the full 

path
;CSV_Save(FileName, CSV_Identifier, OverWrite?)                                     ;Save CSV file
;CSV_DeleteRow(CSV_Identifier, RowNumber)                                           ;Delete a row
;CSV_AddRow(CSV_Identifier, "Cell1,Cell2...")                                       ;Add a row
;CSV_DeleteColumn(CSV_Identifier, ColNumber)                                        ;Delete a column
;CSV_AddColumn(CSV_Identifier, "Cell1,Cell2...")                                    ;Add a column
;CSV_ModifyCell(CSV_Identifier, NewValue,Row, Col)                                  ;Modify an existing cell
;CSV_ModifyRow(CSV_Identifier, "NewValue1,NewValue2...", RowNumber)                 ;Modify an existing row
;CSV_ModifyColumn(CSV_Identifier, "NewValue1,NewValue2...", ColNumber))              ;Modify an existing column
;CSV_Search(CSV_Identifier, SearchText, Instance)                                   ;Search for text within
;CSV_SearchRow(CSV_Identifier, SearchText, RowNumber, Instance)                     ;Search for text within a cell within 

a specific row
;CSV_SearchColumn(CSV_Identifier, SearchText, ColNumber, Instance)                  ;Search for text within a cell within 

a specific column
;CSV_MatchCell(CSV_Identifier, SearchText, Instance)                                ;Search for a cell containing exactly 

the data specified
;CSV_MatchCellColumn(CSV_Identifier, SearchText, ColNumber, Instance=1)            ;Search for a cell containing exactly 

the data specified in a specific column
;CSV_MatchCellRow(CSV_Identifier, SearchText, RowNumber, Instance=1)            ;Search for a cell containing exactly the 

data specified in a specific row
;CSV_MatchRow(CSV_Identifier, "SearchText1,SearchText2", Instance)                  ;Search for a row containing exactly 

the data specified
;CSV_MatchCol(CSV_Identifier, "SearchText1, SearchText2", Instance)                 ;Search for a column containing 

exactly the data specified
;CSV_ReadCell(CSV_Identifier, Row, Column)                                          ;Read data from the specified cell
;CSV_ReadRow(CSV_Identifier, RowNumber)                                             ;Read data from the specified row
;CSV_ReadCol(CSV_Identifier, ColNumber)                                             ;Read data from the specified column
;CSV_LVLoad(CSV_Identifier, Gui, x, y, w, h, header, Sort?, RowIdentification?, AutoAdjustCol?) ;Load data into a 

listview in the specified gui window, listviewname variable  will equal "CSV_Identifier"
;CSV_LVSave(FileName, CSV_Identifier, Delimiter, OverWrite?, Gui)                               ;Save the specified 

listview as a CSV file, CSV_Identifier is the ListView's associated variable name.
;####################################################################################################################
;CSV Functions
;####################################################################################################################
CSV_Load(FileName, CSV_Identifier="", Delimiter="`,")
{
  Local Row
  Local Col
  temp :=  %CSV_Identifier%CSVFile
  FileRead, temp, %FileName%
  String = a,b,c`r`nd,e,f,,"g`r`n",h`r`nB,`n"C`nC",D
  e:= """" ; the encapsulation character (tipical ")
  RegExNeedle:= "\n(?=[^" e "]*" e "([^" e "]*" e "[^" e "]*" e ")*([^" e "]*)\z)"
  String := RegExReplace(String, RegExNeedle , "" )
  StringReplace,String, String,`r,@,All ;only for see msgbox 
  Loop, Parse, temp, `n, `r
  {
    Col := ReturnDSVArray(A_LoopField, CSV_Identifier . "CSV_Row" . A_Index . "_Col", Delimiter)
    Row := A_Index
  }
  %CSV_Identifier%CSV_TotalRows := Row
  %CSV_Identifier%CSV_TotalCols := Col
  %CSV_Identifier%CSV_Delimiter := Delimiter
  SplitPath, FileName, %CSV_Identifier%CSV_FileName, %CSV_Identifier%CSV_Path
  IfNotInString, FileName, `\
  {
    %CSV_Identifier%CSV_FileName := FileName
    %CSV_Identifier%CSV_Path := A_ScriptDir
  }
  %CSV_Identifier%CSV_FileNamePath := %CSV_Identifier%CSV_Path . "\" . %CSV_Identifier%CSV_FileName
}
;####################################################################################################################
CSV_Save(FileName, CSV_Identifier, OverWrite="1")
{
Local Row
Local Col

If OverWrite = 0
 IfExist, %FileName%
   Return
 
FileDelete, %FileName%

EntireFile =
CurrentCSV_TotalRows := %CSV_Identifier%CSV_TotalRows
CurrentCSV_TotalCols := %CSV_Identifier%CSV_TotalCols
Loop, %currentcsv_totalrows%
{
    Row := A_Index
   Loop, %currentCSV_TotalCols%
   {
      Col := A_Index
      EntireFile .= Format4CSV(%CSV_Identifier%CSV_Row%Row%_Col%Col%)
      If (Col <> %CSV_Identifier%CSV_TotalCols)
         EntireFile .= %CSV_Identifier%CSV_Delimiter
   }
   If (Row < %CSV_Identifier%CSV_TotalRows)
      EntireFile .= "`n"
} 
   StringReplace, temp, temp, `r`n`r`n, `r`n, all   ;Remove all blank lines from the CSV file
   loop,
   {
      stringright, test, EntireFile, EntireFile, 1
      if (test = "`n") or (test = "`r")
         stringtrimright, EntireFile, EntireFile, 1
      Else
         break
   }
    FileAppend, %EntireFile%, %FileName%
}
;####################################################################################################################
CSV_TotalRows(CSV_Identifier)
{
  global   
  CurrentCSV_TotalRows := %CSV_Identifier%CSV_TotalRows
  Return %CurrentCSV_TotalRows%
}
;####################################################################################################################
CSV_TotalCols(CSV_Identifier)
{
  global   
  CurrentCSV_TotalCols := %CSV_Identifier%CSV_TotalCols
  Return %CurrentCSV_TotalCols%
}
;####################################################################################################################
CSV_Delimiter(CSV_Identifier)
{
  global
  CurrentCSV_Delimiter := %CSV_Identifier%CSV_Delimiter
  Return %CurrentCSV_Delimiter%
}
;####################################################################################################################
CSV_FileName(CSV_Identifier)
{
  global   
  CurrentCSV_FileName := %CSV_Identifier%CSV_FileName
  Return %CurrentCSV_FileName%
}
;####################################################################################################################
CSV_Path(CSV_Identifier)
{
  global
  CurrentCSV_Path := %CSV_Identifier%CSV_Path
  Return %CurrentCSV_Path%
}
;####################################################################################################################
CSV_FileNamePath(CSV_Identifier)
{
  global
  CurrentCSV_FileNamePath := %CSV_Identifier%CSV_FileNamePath
  Return %CurrentCSV_FileNamePath%
}
;####################################################################################################################
CSV_DeleteRow(CSV_Identifier, RowNumber)
{
  Local Row
  Local Col
  Local NewRow
  CurrentCSV_TotalRows := %CSV_Identifier%CSV_TotalRows
  CurrentCSV_TotalCols := %CSV_Identifier%CSV_TotalCols   
  Loop, %CurrentCSV_TotalRows%
  {
    Row := A_Index
    NewRow := Row + 1
    If Row < %RowNumber%
      Continue
    Else
      Loop, %CurrentCSV_TotalCols%
      {
        Col := A_Index
        %CSV_Identifier%CSV_Row%Row%_Col%Col% := %CSV_Identifier%CSV_Row%NewRow%_Col%Col%
      }
  }
  %CSV_Identifier%CSV_TotalRows --
}
;####################################################################################################################
CSV_AddRow(CSV_Identifier, RowData)
{
  global
  %CSV_Identifier%CSV_TotalRows ++
  CurrentCSV_TotalRows := %CSV_Identifier%CSV_TotalRows
  CurrentCSV_Delimiter := %CSV_Identifier%CSV_Delimiter
  ReturnDSVArray(RowData, CSV_Identifier . "CSV_Row" . CurrentCSV_TotalRows . "_Col", CurrentCSV_Delimiter)
}
;####################################################################################################################
CSV_DeleteColumn(CSV_Identifier, ColNumber)
{
  Local Row
  Local Col
  Local NewCol
  CurrentCSV_TotalRows := %CSV_Identifier%CSV_TotalRows
  CurrentCSV_TotalCols := %CSV_Identifier%CSV_TotalCols
  Loop, %currentCSV_TotalRows%
  {
    Row := A_Index
    Loop, %currentCSV_TotalCols%
    {
      Col := A_Index
      NewCol := Col + 1
      If Col < %ColNumber%
        Continue
      Else
        %CSV_Identifier%CSV_Row%Row%_Col%Col% := %CSV_Identifier%CSV_Row%Row%_Col%NewCol%
    }
  }
    %CSV_Identifier%CSV_TotalCols --   
}
;####################################################################################################################
CSV_AddColumn(CSV_Identifier, ColData)
{
  global
  %CSV_Identifier%CSV_TotalCols ++
  CurrentCSV_TotalCols := %CSV_Identifier%CSV_TotalCols
  CurrentCSV_Delimiter := %CSV_Identifier%CSV_Delimiter
  _tmpColItems:=ReturnDSVArray(Coldata, "_tmpCOL", CurrentCSV_Delimiter)
  Loop, %_tmpColItems%
   {
    %CSV_Identifier%CSV_Row%A_Index%_Col%CurrentCSV_TotalCols% := _tmpCOL%A_Index%
   _tmpCOL%A_Index%:= ; clear mem
   }
   _tmpColItems= ; clear mem
 }
;####################################################################################################################
CSV_ModifyCell(CSV_Identifier, Value, Row, Col)
  {
   global
    %CSV_Identifier%CSV_Row%Row%_Col%Col% := Value
  }
;####################################################################################################################
CSV_ModifyRow(CSV_Identifier, Value, RowNumber)
{
  CurrentCSV_Delimiter := %CSV_Identifier%CSV_Delimiter
  ReturnDSVArray(Value, CSV_Identifier . "CSV_Row" . RowNumber . "_Col", CurrentCSV_Delimiter)
}
;####################################################################################################################   
CSV_ModifyColumn(CSV_Identifier, Coldata, ColNumber)
{
  global
  CurrentCSV_Delimiter := %CSV_Identifier%CSV_Delimiter
  _tmpColItems:=ReturnDSVArray(Coldata, "_tmpCOL", CurrentCSV_Delimiter)
  Loop, %_tmpColItems%
   {
  %CSV_Identifier%CSV_Row%A_Index%_Col%ColNumber% := _tmpCOL%A_Index%
    _tmpCOL%A_Index%:= ; clear mem
  }
 _tmpColItems= ; clear mem
}
;####################################################################################################################
CSV_Search(CSV_Identifier, SearchText, Instance=1)
{
  Local Row
  Local Col
  Local FoundInstance
  CurrentCSV_TotalRows := %CSV_Identifier%CSV_TotalRows
  CurrentCSV_TotalCols := %CSV_Identifier%CSV_TotalCols   
 
  Loop, %CurrentCSV_TotalRows%
  {
    Row := A_Index
    Loop, %CurrentCSV_TotalCols%
    {
      Col := A_Index
      CurrentString := %CSV_Identifier%CSV_Row%Row%_Col%Col%
      IfInString, CurrentString, %SearchText%
      {
        FoundInstance ++
        CurrentCell = %Row%`,%Col% 
        If FoundInstance = %Instance%
          Return %CurrentCell%
      }
    }
  }
  Return 0
}
;####################################################################################################################
CSV_SearchRow(CSV_Identifier, SearchText, RowNumber, Instance=1)
{
  Local Col
  Local FoundInstance
  CurrentCSV_TotalCols := %CSV_Identifier%CSV_TotalCols
 
  Loop, %CurrentCSV_TotalCols%
  {
    Col := A_Index
    CurrentString := %CSV_Identifier%CSV_Row%RowNumber%_Col%Col%
    IfInString, CurrentString, %SearchText%
    {
     FoundInstance ++
     If FoundInstance = %Instance%
       Return %Col%
    }
  }
  Return 0
}
;####################################################################################################################
CSV_SearchColumn(CSV_Identifier, SearchText, ColNumber, Instance=1)
{
  Local Row
  Local FoundInstance
  CurrentCSV_TotalRows := %CSV_Identifier%CSV_TotalRows

  Loop, %CurrentCSV_TotalRows%
  {
    Row := A_Index
    CurrentString := %CSV_Identifier%CSV_Row%Row%_Col%ColNumber%
    IfInString, CurrentString, %SearchText%
    {
      FoundInstance ++
      If FoundInstance = %Instance%
        Return %Row%
    }
  }
  Return 0
}
;####################################################################################################################
CSV_MatchCell(CSV_Identifier,SearchText, Instance=1)
{
Local Row
Local Col
Local FoundInstance
CurrentCSV_TotalRows := %CSV_Identifier%CSV_TotalRows
CurrentCSV_TotalCols := %CSV_Identifier%CSV_TotalCols

Loop, %CurrentCSV_TotalRows%
  {
    Row := A_Index
    Loop, %CurrentCSV_TotalCols%
    {
      Col := A_Index
      CurrentString := %CSV_Identifier%CSV_Row%Row%_Col%Col%
      IfEqual, CurrentString, %SearchText%
      {
        FoundInstance ++
        CurrentCell = %Row%`,%Col%
        If FoundInstance = %Instance%
          Return %CurrentCell%
      }
    }
  }
  Return 0
}
;####################################################################################################################
CSV_MatchCellColumn(CSV_Identifier, SearchText, ColNumber, Instance=1)
{
  Local Row
  Local FoundInstance
  CurrentCSV_TotalRows := %CSV_Identifier%CSV_TotalRows

  Loop, %CurrentCSV_TotalRows%
  {
    Row := A_Index
    CurrentString := %CSV_Identifier%CSV_Row%Row%_Col%ColNumber%
   IfEqual, CurrentString, %SearchText%
    {
      FoundInstance ++
      If FoundInstance = %Instance%
        Return %Row%
    }
  }
  Return 0
}
;####################################################################################################################
CSV_MatchCellRow(CSV_Identifier, SearchText, RowNumber, Instance=1) ; fix http://www.autohotkey.com/forum/viewtopic.php?

p=400669#400669
{
  Local Col
  Local FoundInstance
  CurrentCSV_TotalCols := %CSV_Identifier%CSV_TotalCols
 
  Loop, %CurrentCSV_TotalCols%
  {
    Col := A_Index
    CurrentString := %CSV_Identifier%CSV_Row%RowNumber%_Col%Col%
   IfEqual, CurrentString, %SearchText%
    {
     FoundInstance ++
     If FoundInstance = %Instance%
       Return %Col%
    }
  }
  Return 0
}
;####################################################################################################################
CSV_MatchRow(CSV_Identifier, SearchText, Instance=1)
{
  Local Col
  Local Row
  Local CurrentRow
  Local FoundInstance
  CurrentCSV_TotalRows := %CSV_Identifier%CSV_TotalRows
  CurrentCSV_TotalCols := %CSV_Identifier%CSV_TotalCols
  Loop, %CurrentCSV_TotalRows%
  {
    Row := A_Index
    CurrentRow =
    Loop, %CurrentCSV_TotalCols%
    {
      Col := A_Index
      CurrentRow .= %CSV_Identifier%CSV_Row%Row%_Col%Col%
      If Col <> %CurrentCSV_TotalCols%
        CurrentRow .= "`,"
      IfEqual, CurrentRow, %SearchText%
      {
        FoundInstance ++       
        If FoundInstance = %Instance%
          Return %Row%
      }
    }
  }
  Return 0
}
;####################################################################################################################
CSV_MatchCol(CSV_Identifier, SearchText, Instance=1)
{
Local Col
Local Row
Local CurrentCol
Local FoundInstance
CurrentCSV_TotalRows := %CSV_Identifier%CSV_TotalRows
CurrentCSV_TotalCols := %CSV_Identifier%CSV_TotalCols   
Loop, %CurrentCSV_TotalCols%
  {   
    Col := A_Index
    CurrentCol =
    Loop, %CurrentCSV_TotalRows%
    {
      Row := A_Index
      CurrentCol .= %CSV_Identifier%CSV_Row%Row%_Col%Col%
      If Row <> %CurrentCSV_TotalRows%
        CurrentCol .= "`,"
      IfEqual, CurrentCol, %SearchText%
      {
        FoundInstance ++
        If FoundInstance = %Instance%
          Return %Col%
      }
    }
  }
Return 0
}
;####################################################################################################################
CSV_ReadCell(CSV_Identifier, Row, Col)
{
  Local CellData
  CellData := %CSV_Identifier%CSV_Row%Row%_Col%Col%
  Return %CellData%
}
;####################################################################################################################
CSV_ReadRow(CSV_Identifier, RowNumber) ; http://www.autohotkey.com/forum/viewtopic.php?p=453352#453352
{
  Local CellData
  CurrentCSV_TotalCols := %CSV_Identifier%CSV_TotalCols 
	RowData= 
  Loop, %CurrentCSV_TotalCols%
  {
    RowData .= %CSV_Identifier%CSV_Row%RowNumber%_Col%A_Index%
    If A_Index <> %CurrentCSV_TotalCols%
      RowData .= "`,"
  }
  Return %RowData%
}
;####################################################################################################################
CSV_ReadCol(CSV_Identifier, ColNumber) ; fix http://www.autohotkey.com/forum/viewtopic.php?p=453352#453352
{
  Local CellData
  CurrentCSV_TotalRows := %CSV_Identifier%CSV_TotalRows   
  ColData=
  Loop, %CurrentCSV_TotalRows%
  {
    ColData .= %CSV_Identifier%CSV_Row%A_Index%_Col%ColNumber%
    If A_Index <> %CurrentCSV_TotalRows%
      ColData .= "`,"
  }
  Return %ColData%
}
;####################################################################################################################
CSV_LVLoad(CSV_Identifier, Gui=1, x=10, y=10, w="", h="", header="", Sort=0, AutoAdjustCol=1)
{
  Local Row
  CurrentCSV_TotalRows := %CSV_Identifier%CSV_TotalRows
  CurrentCSV_TotalCols := %CSV_Identifier%CSV_TotalCols
 
  If %CSV_Identifier%CSV_LVAlreadyCreated =
  {
    Gui, %Gui%:Add, ListView, v%CSV_Identifier% x%x% y%y% w%w% h%h%, %header%
    %CSV_Identifier%CSV_LVAlreadyCreated =
  }
  ;Set GUI window, clear any existing data
  Gui, %Gui%:Default
  GuiControl, -Redraw, %CSV_Identifier%
  Sleep, 200
  LV_Delete()

  ;Add Data
  Loop, %currentcsv_totalrows%
    LV_Add("", "")
  Loop, %currentcsv_totalrows%
  {
    Row := A_Index
    Loop, %currentCSV_TotalCols%
    LV_Modify(Row, "Col" . A_Index, %CSV_Identifier%CSV_Row%Row%_Col%A_Index%)
  }
  ;Display Data
  If Sort <> 0
    LV_ModifyCol(Sort, "Sort")

  If AutoAdjustCol = 1
    LV_ModifyCol()
  GuiControl, +Redraw, %CSV_Identifier%
}
;####################################################################################################################
CSV_LVSave(FileName, CSV_Identifier, Delimiter=",",OverWrite=1, Gui=1)
{
  Gui, %Gui%:Default
  Gui, ListView, %CSV_Identifier%
  Rows := LV_GetCount()
  Cols := LV_GetCount("Col")

  IfExist,2 %FileName%
    If OverWrite = 0
      Return 0
  FileDelete, %FileName%

  Loop, %Rows%
  {
    FullRow =
    Row := A_Index
    Loop, %Cols%
    {
      LV_GetText(CellData, Row, A_Index)
      FullRow .= Format4CSV(CellData) ; fix http://www.autohotkey.com/forum/viewtopic.php?p=453352#453352
      If A_Index <> %Cols%
        FullRow .= Delimiter
    }
    stringreplace, checkforemptyrow, fullrow, %Delimiter%,,all ; /----- exclude empty rows
    If (checkforemptyrow = "")
    	Continue ; /-------
    If Row <> %Rows%
      FullRow .= "`n"
    EntireFile .= FullRow
  }
  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
}
;#################################################################################################################### 


SoLong&Thx4AllTheFish
  • Members
  • 4999 posts
  • Last active:
  • Joined: 27 May 2007
Here is CSV_AddColumn, needs serious testing but at first glance DOES seem to work. BUT as you may notice the endresult is missing the last col item in the last row, this is due to the StringTrimRight in CSV_Save which doesn't seem to play nice with CSV_AddColumn so perhaps adding a check if the last char is `n or `r might be useful.... or introduce a fix elsewhere (you might know better).
file=
(
1,2,3
4,"5,5",6
7,8,9
)
FileDelete, test1.csv
FileAppend, %file%, test1.csv
CSV_Load("test1.csv", "File1")
CSV_AddColumn("File1", "x,y,z")
CSV_Save("test2.csv", "File1", 1)

ExitApp

CSV_AddColumn(CSV_Identifier, ColData)
{
  global
  %CSV_Identifier%CSV_TotalCols ++
  CurrentCSV_TotalCols := %CSV_Identifier%CSV_TotalCols
  ;Loop, Parse, ColData, `,
  _tmpColItems:=ReturnDSVArray(Coldata, "_tmpCOL")
  MsgBox % _tmpColItems
  MsgBox % _TmpCol3
  Loop, %_tmpColItems%
   {
    %CSV_Identifier%CSV_Row%A_Index%_Col%CurrentCSV_TotalCols% := _tmpCOL%A_Index%
	_tmpCOL%A_Index%:= ; clear mem
   }
   _tmpColItems= ; clear mem
 }
mmm perhaps I'll dust of my transpose csv function and add it ... :wink:

Edit: for CSV_Save
replace
If (Row <> %CSV_Identifier%CSV_TotalRows)
with
If (Row <= %CSV_Identifier%CSV_TotalRows)
that should fix it but do test it with all you other functions.

SoLong&Thx4AllTheFish
  • Members
  • 4999 posts
  • Last active:
  • Joined: 27 May 2007
Here is ModifyColumn needs to be tested but at first glance seems to work
CSV_ModifyColumn(CSV_Identifier, Coldata, ColNumber)
{
  ;Loop, Parse, Value, `,
  global
  _tmpColItems:=ReturnDSVArray(Coldata, "_tmpCOL")
  Loop, %_tmpColItems%
   {
  %CSV_Identifier%CSV_Row%A_Index%_Col%ColNumber% := _tmpCOL%A_Index%
 	_tmpCOL%A_Index%:= ; clear mem
  }
 _tmpColItems= ; clear mem
}

edit: If you add these two functions change the post of your title something like:

CSV Library [lib]

because it is a library (collection of related functions)

Also: you may wish to post in the other CSV threads of trueski and neXt and add a link to this post so people searching the forum will also find this new(est) library.

Edit2: I now see I didn't apply the %CSV_Identifier%CSV_Delimiter so you may wish to add that if you want to incorporate these two updated functions...

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

Here is CSV_AddColumn, needs serious testing but at first glance DOES seem to work. BUT as you may notice the endresult is missing the last col item in the last row, this is due to the StringTrimRight in CSV_Save which doesn't seem to play nice with CSV_AddColumn so perhaps adding a check if the last char is `n or `r might be useful.... or introduce a fix elsewhere (you might know better).


Thanks for the contribution hugov. The two new functions have been added to the code above. I also ran into the StringTrimRight problem when coding today. how do you test for `n or `r characters? I tried the code below but it seems to have no effect....

loop,
	{
		stringright, test, EntireFile, EntireFile, 1
		if test = `n or test = `r
			stringtrimright, EntireFile, EntireFile, 1
		Else
			break
	}

Also, Shouldn't this:
	If (Row <= %CSV_Identifier%CSV_TotalRows) 
		EntireFile .= "`n"

Be this:
	If (Row < %CSV_Identifier%CSV_TotalRows) 
		EntireFile .= "`n"

Seems to me that a `n should only be created when Row is less then CSV_TotalRows. I can't figure out why you would need a `n after the last row as been appended to the variable. this would also fix the need for a stringtrimright(the question above).

thanks!

SoLong&Thx4AllTheFish
  • Members
  • 4999 posts
  • Last active:
  • Joined: 27 May 2007
if (test = "`n") or (test = "`r")
should do it.

I suggest you test the library carefully with <= and < and with/without stringtrimright if you want to make sure which option works best, the < does seem logical as you say but you know better as you have worked and modified this lib.

derRaphael
  • Members
  • 872 posts
  • Last active: Mar 19 2013 04:42 PM
  • Joined: 23 Nov 2007
nice to know somebody finds the DSV function usefull at last

probly u should remove all the msgbox debug stuff from the lib file, since it osnt very usefull for others who might want to use this lib.

especially lines like MsgBox, blah shouldnt appear at all

all in all .. keep up the good work

greets
dR

All scripts, unless otherwise noted, are hereby released under CC-BY

kdoske
  • Members
  • 138 posts
  • Last active: Nov 06 2012 01:58 AM
  • Joined: 17 Dec 2008
Thanks DerRaphael, all debug messages have been removed. Forgot all about removing them.

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

if (test = "`n") or (test = "`r")
should do it.

I suggest you test the library carefully with <= and < and with/without stringtrimright if you want to make sure which option works best, the < does seem logical as you say but you know better as you have worked and modified this lib.


Yeah I removed the <= and replaced with a <. I also removed the Stringtrimright. Only time will tell if a problem surfaces with use but from going over the code now it seems to work great. Yeah I tried that if stament above the other day to remove returns and it didn't work. I tried it again by creating a CSV file with a return at the end of the file, CSV_loaded it, and CSV_saved it. It didn't seem to fix it but oh well, I'll leave it anyway. I left a StringReplace, temp, temp, `r`n`r`n, `r`n, all in the CSV_Load and CSV_Save. Seems to me it should be there to ensure CSV integrity--especially on the CSV_Load.

SoLong&Thx4AllTheFish
  • Members
  • 4999 posts
  • Last active:
  • Joined: 27 May 2007
How about a function to clear / free the memory of the CSV variables.
CSV_Clear(CSV_Identifier)
	{
	 CurrentCSV_TotalRows := %CSV_Identifier%CSV_TotalRows
	 CurrentCSV_TotalCols := %CSV_Identifier%CSV_TotalCols
	 Loop, %currentcsv_totalrows%
		{
		 Row := A_Index
		 Loop, %currentCSV_TotalCols%
			{
			 Col := A_Index
			 VarSetCapacity(%CSV_Identifier%CSV_Row%Row%_Col%Col%, 0) 
			}
		}	
	 VarSetCapacity(CurrentCSV_TotalRows, 0) 
	 VarSetCapacity(CurrentCSV_TotalCols, 0) 
	 VarSetCapacity(%CSV_Identifier%CSV_TotalRows, 0) 
	 VarSetCapacity(%CSV_Identifier%CSV_TotalCols, 0) 
	 VarSetCapacity(%CSV_Identifier%CSV_Delimiter, 0) 
	 VarSetCapacity(EntireFile, 0)
	 VarSetCapacity(%CSV_Identifier%CSV_FileName, 0)
	 VarSetCapacity(%CSV_Identifier%CSV_FileNamePath, 0)
	 VarSetCapacity(%CSV_Identifier%CSV_Path, 0)
	 VarSetCapacity(Temp, 0) 
	 VarSetCapacity(test, 0)
	 VarSetCapacity(tmp, 0)
;	 VarSetCapacity(CurrentCell, 0)
;	 VarSetCapacity(CurrentCSV_Delimiter, 0)
;	 VarSetCapacity(CurrentCSV_FileName, 0)
;	 VarSetCapacity(CurrentCSV_FileNamePath, 0)
;	 VarSetCapacity(CurrentCSV_Path, 0)
;	 VarSetCapacity(CurrentCSV_TotalCols, 0)
;	 VarSetCapacity(CurrentCSV_TotalRows, 0)
;	 VarSetCapacity(CurrentRow, 0)
;	 VarSetCapacity(CurrentString, 0)
;	 VarSetCapacity(RowData, 0)
	}

Some further remarks:
- I've commented some variable names above as I've haven't studied the lib to closely, you will know if they should be "emptied" or not to per CSV_Identifier.
- In CSV_Load you use a variable temp which could also be an OS environment variable so I suggest you change + make it local
- there is also a tmp variable used, probably should be local too

kdoske
  • Members
  • 138 posts
  • Last active: Nov 06 2012 01:58 AM
  • Joined: 17 Dec 2008
Thanks again Hugov, good idea with the memory clearer. I'll make the changes in a couple days and post for all.

adamrgolf
  • Members
  • 442 posts
  • Last active: May 22 2017 09:16 PM
  • Joined: 28 Dec 2006

Thanks again Hugov, good idea with the memory clearer. I'll make the changes in a couple days and post for all.


Where you able to make the needed changes? I'd love to see a clear memory function.

Thanks!

nnah
  • Members
  • 16 posts
  • Last active: Jul 07 2010 05:17 PM
  • Joined: 02 Jul 2010
I am very new to autohotkey but it's seems like its going to be my life saver for some work that I need to get completed before the weekend is over.

If someone can help me or at least get me started in the right direction. I have a huge text file that contains sample data below...which i will call File A.

20100517|L00015099|97001GP|1||||||
20100517|L00014977|97001GP|1||||||
20100517|L00015024|97001GP|1||||||
20100517|L00015081|97530GP|4||||||
20100517|L00014996|97110GP|2||||||
20100517|L00014996|97530GP|1||||||


I need a script that can compare the data in column 2 (L00015099) to the file below and replace column 2 with the corresponding value in file B.

L00015099|4200010
L00014977|4200042
L00015024|4200066
L00015081|4200166
L00014996|4200001
L00014996|4200111

So the end result in File A would look something like this after the script is done.

20100517|4200010|97001GP|1||||||
20100517|4200042|97001GP|1||||||
20100517|4200066|97001GP|1||||||
20100517|4200166|97530GP|4||||||
20100517|4200001|97110GP|2||||||
20100517|4200111|97530GP|1||||||


If someone can please start me off...I have this huge file that I need to manipulate and upload into the system before the weekend is over....I really need help.

Thanks,

SoLong&Thx4AllTheFish
  • Members
  • 4999 posts
  • Last active:
  • Joined: 27 May 2007
You didn't even try did you? Why ask 4-5 times but don't take the time to look into the answers people give you. This will solve your problem.

a= ; this is just some test code you don't need it in your final script
(
20100517|L00015099|97001GP|1||||||
20100517|L00014977|97001GP|1||||||
20100517|L00015024|97001GP|1||||||
20100517|L00015081|97530GP|4||||||
20100517|L00014996|97110GP|2||||||
20100517|L00014996|97530GP|1||||||
)
FileDelete, atest.csv 
FileAppend, %a%, atest.csv 

b= ; this is just some test code you don't need it in your final script
(
4200010
4200042
4200066
4200166
4200001
4200111
)
FileDelete, btest.csv
FileAppend, %b%, btest.csv
; real script starts here
CSV_Load("atest.csv", "A", "|") ; replace atest.csv with the name of File A
FileRead, ColB, btest.csv ; Read File B into variable ColB
StringReplace, ColB, ColB, `r`n, |, All ; as you can see CSV_ModifyColumn needs a parameter with the new values for column B (or rather column 2) so we replace the newlines with the delimiter char |
; CSV_ModifyColumn(CSV_Identifier, [color=red]"NewValue1,NewValue2..."[/color], ColNumber))
CSV_ModifyColumn("A", ColB, 2) ; replace 2nd column in CSV A with the new values for Column 2 (B)
;CSV_Save(FileName, CSV_Identifier, OverWrite?)                                     ; Save CSV file
CSV_Save("Result.csv", "A")                                     ;Save CSV file
ExitApp ; close script


Edit: added comments. And you can still do this any any spreadsheet program with a copy/paste action

nnah
  • Members
  • 16 posts
  • Last active: Jul 07 2010 05:17 PM
  • Joined: 02 Jul 2010
@hugov

Thanks so much for your help, I wasn't just posting all over the place, and I tested the results that was given to me with the TF option but that didn't work.

Emergency room sucks! today has been a crazy day couldn't even work on this project all day. Spent half the day with my baby boy in the emergency room. He will be ok though!

I need to make an adjustment to the code?

Can you explain this line of code? StringReplace, ColB, ColB, `r`n, |, All


#Include csv.ahk
CSV_Load("atest.csv", "A", "|")
FileRead, ColB, btest.csv
StringReplace, ColB, ColB, `r`n, |, All
; CSV_ModifyColumn(CSV_Identifier, "NewValue1,NewValue2...", ColNumber))
CSV_ModifyColumn("A", ColB, 2)
;CSV_Save(FileName, CSV_Identifier, OverWrite?) ;Save CSV file
CSV_Save("Result.csv", "A") ;Save CSV file

nnah
  • Members
  • 16 posts
  • Last active: Jul 07 2010 05:17 PM
  • Joined: 02 Jul 2010
How do I adjust the code below to replace the data in File A column 2 with the data in File B Column 2?

Do I have to split the strings first? or is there a different command/variable I can use? Thanks!

a=
(
20100517|L00015099|97001GP|1||||||
20100517|L00014977|97001GP|1||||||
20100517|L00015024|97001GP|1||||||
20100517|L00015081|97530GP|4||||||
20100517|L00014996|97110GP|2||||||
20100517|L00014996|97530GP|1||||||
)
FileDelete, atest.csv
FileAppend, %a%, atest.csv

b=
(
L00015099|4200010
L00014977|4200042
L00015024|4200066
L00015081|4200166
L00014996|4200001
L00014996|4200111 
)
FileDelete, btest.csv
FileAppend, %b%, btest.csv

CSV_Load("atest.csv", "A", "|")
FileRead, ColB, btest.csv
StringReplace, ColB, ColB, `r`n, |, All
; CSV_ModifyColumn(CSV_Identifier, "NewValue1,NewValue2...", ColNumber))
CSV_ModifyColumn("A", ColB, 2)
;CSV_Save(FileName, CSV_Identifier, OverWrite?)                                     ;Save CSV file
CSV_Save("Result.csv", "A")                                     ;Save CSV file