 |
AutoHotkey Community
Let's help each other out
|
| View previous topic :: View next topic |
| Author |
Message |
kdoske
Joined: 17 Dec 2008
Posts: 80
|
 Posted: Fri Mar 19, 2010 8:36 pm Post subject: CSV Library [lib] |
 |
|
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...
EDIT:- Thread Post 2 + 3 changes from Hogov below have been added to the CSV Library.
- Added two new functions: CSV_MatchCellColumn and CSV_MatchCellRow. This finds exact matches in a specified row or column.
- Changed some things in CSV_Save that should allow it to be more reliable.
- Removed all Debug msg boxes from CSV Library
| Code: | ; AutoHotkey Version: 1.0.48
; Author: Kdoske, trueski
; 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%
StringReplace, temp, temp, `r`n`r`n, `r`n, all ;Remove all blank lines from the CSV file
Loop, Parse, temp, `n, `r
{
Col := ReturnDSVArray(A_LoopField, CSV_Identifier . "CSV_Row" . A_Index . "_Col", Delimiter)
Row := A_Index
Loop, Parse, A_LoopReadLine, %Delimiter%
{
Col := A_Index
%CSV_Identifier%CSV_Row%Row%_Col%Col% := A_LoopField
}
}
%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)
{
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, CurrentRow, %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)
{
Local CellData
CurrentCSV_TotalCols := %CSV_Identifier%CSV_TotalCols
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)
{
Local CellData
CurrentCSV_TotalRows := %CSV_Identifier%CSV_TotalRows
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 .= CellData
If A_Index <> %Cols%
FullRow .= Delimiter
}
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
}
;####################################################################################################################
|
Last edited by kdoske on Sun Mar 21, 2010 11:02 pm; edited 15 times in total |
|
| Back to top |
|
 |
SoLong&Thx4AllTheFish
Joined: 27 May 2007
Posts: 4999
|
| Posted: Sat Mar 20, 2010 8:15 am Post subject: |
|
|
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).
| Code: | 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 ... 
Edit: for CSV_Save
replace
| Code: | | If (Row <> %CSV_Identifier%CSV_TotalRows) |
with
| Code: | | If (Row <= %CSV_Identifier%CSV_TotalRows) |
that should fix it but do test it with all you other functions.
_________________
AHK Wiki FAQ
TF : Text files & strings lib, TF Forum |
|
| Back to top |
|
|
SoLong&Thx4AllTheFish
Joined: 27 May 2007
Posts: 4999
|
| Posted: Sat Mar 20, 2010 9:55 am Post subject: |
|
|
Here is ModifyColumn needs to be tested but at first glance seems to work | Code: | 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...
_________________
AHK Wiki FAQ
TF : Text files & strings lib, TF Forum |
|
| Back to top |
|
|
kdoske
Joined: 17 Dec 2008
Posts: 80
|
| Posted: Sun Mar 21, 2010 2:53 am Post subject: |
|
|
| hugov wrote: | 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....
| Code: | loop,
{
stringright, test, EntireFile, EntireFile, 1
if test = `n or test = `r
stringtrimright, EntireFile, EntireFile, 1
Else
break
} |
Also, Shouldn't this:
| Code: |
If (Row <= %CSV_Identifier%CSV_TotalRows)
EntireFile .= "`n"
|
Be this:
| Code: |
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! |
|
| Back to top |
|
|
SoLong&Thx4AllTheFish
Joined: 27 May 2007
Posts: 4999
|
| Posted: Sun Mar 21, 2010 8:05 am Post subject: |
|
|
| Code: | | 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.
_________________
AHK Wiki FAQ
TF : Text files & strings lib, TF Forum |
|
| Back to top |
|
|
derRaphael
Joined: 23 Nov 2007
Posts: 841
Location: ~/.
|
| Posted: Sun Mar 21, 2010 6:09 pm Post subject: |
|
|
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 |
|
| Back to top |
|
|
kdoske
Joined: 17 Dec 2008
Posts: 80
|
| Posted: Sun Mar 21, 2010 10:54 pm Post subject: |
|
|
| Thanks DerRaphael, all debug messages have been removed. Forgot all about removing them. |
|
| Back to top |
|
|
kdoske
Joined: 17 Dec 2008
Posts: 80
|
| Posted: Sun Mar 21, 2010 10:58 pm Post subject: |
|
|
| hugov wrote: | | Code: | | 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. |
|
| Back to top |
|
|
SoLong&Thx4AllTheFish
Joined: 27 May 2007
Posts: 4999
|
| Posted: Tue Mar 23, 2010 8:20 am Post subject: |
|
|
How about a function to clear / free the memory of the CSV variables.
| Code: | 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
_________________
AHK Wiki FAQ
TF : Text files & strings lib, TF Forum |
|
| Back to top |
|
|
kdoske
Joined: 17 Dec 2008
Posts: 80
|
| Posted: Fri Mar 26, 2010 2:24 am Post subject: |
|
|
| Thanks again Hugov, good idea with the memory clearer. I'll make the changes in a couple days and post for all. |
|
| Back to top |
|
|
adamrgolf
Joined: 28 Dec 2006
Posts: 440
|
| Posted: Fri May 21, 2010 8:06 am Post subject: |
|
|
| kdoske wrote: | | 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! |
|
| Back to top |
|
|
nnah
Joined: 02 Jul 2010
Posts: 16
|
| Posted: Sat Jul 03, 2010 2:00 pm Post subject: File manipulation |
|
|
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, |
|
| Back to top |
|
|
SoLong&Thx4AllTheFish
Joined: 27 May 2007
Posts: 4999
|
| Posted: Sat Jul 03, 2010 4:02 pm Post subject: |
|
|
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.
| Code: | 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, "NewValue1,NewValue2...", 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
_________________
AHK Wiki FAQ
TF : Text files & strings lib, TF Forum
Last edited by SoLong&Thx4AllTheFish on Sun Jul 04, 2010 9:42 am; edited 3 times in total |
|
| Back to top |
|
|
nnah
Joined: 02 Jul 2010
Posts: 16
|
| Posted: Sun Jul 04, 2010 2:48 am Post subject: File manipulation |
|
|
@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 |
|
| Back to top |
|
|
nnah
Joined: 02 Jul 2010
Posts: 16
|
| Posted: Sun Jul 04, 2010 3:32 am Post subject: |
|
|
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!
| Code: |
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
|
|
|
| Back to top |
|
|
|
|
You can post new topics in this forum
You can reply to topics in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
FAQ
Search
Memberlist
Register
Profile
Log in to check your private messages
Log in
