credits:
HugoV (for all bug reports and suggestions.)
Rhys (for his Format4CSV() beta 1 function.)
Format4CSV() is included with this code.
Code:
;+--------------+
;|function list.|
;+--------------+
/*
CSV_SET(r_file, r_header=0, r_delimiter=",")
CSV_FINAL(copy=0)
CSV_totalRows()
CSV_totalCols()
CSV_readCell(row = 1, column = 1)
XLS_readCell(range)
CSV_find(value, row=0, column=0, offset_x=0, offset_y=0)
CSV_exists(value)
CSV_sort(column=1, order="asc")
CSV_writeCell(row, column, value)
XLS_writeCell(range, value)
CSV_fileName()
CSV_filePath()
CSV_fullName()
*/
setBatchLines -1
CSV_SET(r_file, r_header=0, r_delimiter=",") {
global
r_delimiter = %r_delimiter%
r_header = %r_header%
r_row = 0
r_col = 0
if r_delimiter = ,
r_delimiter = CSV
SplitPath r_file, r_name, r_path, r_ext, r_nameNoExt
if(r_path = "")
r_path = %A_WorkingDir%
fileRead r_file, %r_file%
if (errorLevel) {
MsgBox unable to read the file
return %errorLevel%
}
loop parse, r_file, `n
{
r_row++
loop parse, A_LoopField, %r_delimiter%
{
;if(r_row = 1)
;r_col++
__matrix%r_row%_%A_Index% := regExReplace(A_LoopField, "\R?$")
if r_delimiter = CSV
{
if (A_Index = 1)
__line%r_row% := Format4CSV(__matrix%r_row%_%A_Index%)
else
__line%r_row% := __line%r_row% . "," . Format4CSV(__matrix%r_row%_%A_Index%)
}
else
{
if (A_Index = 1)
__line%r_row% := Format4CSV(__matrix%r_row%_%A_Index%)
else
__line%r_row% := __line%r_row% . r_delimiter . Format4CSV(__matrix%r_row%_%A_Index%)
}
}
}
r_fullName := CSV_fullName()
CSV_totalRows()
CSV_totalCols()
return
}
CSV_totalRows() {
global
r_lastRow := r_row
return %r_lastRow%
}
CSV_totalCols() {
global
r_lastCol = %r_col%
return %r_lastCol%
}
CSV_readCell(row = 1, column = 1) {
global
if (r_header)
row++
return % __matrix%row%_%column% ;%
}
XLS_readCell(range) {
global
if (r_header)
row++
StringLeft column, range, 1
StringReplace row, range, %column%
StringUpper column, column
column := ASC(column) - 64
return % __matrix%row%_%column% ;%
}
CSV_writeCell(row, column, value) {
global
if (r_header)
row++
__line%row% := regExReplace(__line%row%, __matrix%row%_%column%, Format4CSV(value))
__matrix%row%_%column% := Format4CSV(value)
r_updated := true
return
}
XLS_writeCell(range, value) {
global
if r_header = 1
row++
StringLeft column, range, 1
StringReplace row, range, %column%
StringUpper column, column
column := ASC(column) - 64
__line%row% := regExReplace(__line%row%, __matrix%row%_%column%, Format4CSV(value))
__matrix%row%_%column% := Format4CSV(value)
r_updated := true
return
}
CSV_fileName() {
global
return %r_name%
}
CSV_filePath() {
global
return % r_path . "\" ;%
}
CSV_fullName() {
global
return % r_path . "\" . r_name ;%
}
CSV_find(value, row=0, column=0, offset_x=0, offset_y=0) {
global
local i = 0
;full search
if (row = 0 && column = 0) {
loop %r_lastRow% {
i++
loop %r_lastCol% {
if (__matrix%i%_%A_Index% = value)
return % i + offset_x . "," . A_Index + offset_y ;%
}
}
}
;search specified row
if (row != 0 && column = 0) {
loop %r_lastCol% {
if (value = __matrix%row%_%A_Index%)
return % A_Index + offset_y ;%
}
}
;search specified column
if (row = 0 && column != 0) {
loop %r_lastRow% {
if (value = __matrix%A_Index%_%column%)
return % A_Index + offset_x ;%
}
}
return false
}
CSV_exists(value) {
global
if (InStr(r_file, value . "`,"))
return true
else
return false
}
CSV_sort(column=1, order="") {
global
r_file =
local i = 0
if (order = "desc")
order = R
loop %r_lastRow% {
If __matrix%A_Index%_%column% is not number
{
StringUpper sortBy, __matrix%A_Index%_%column%
sortBy := ASC(sortBy) + 1000000000000
}
else
sortBy := __matrix%A_Index%_%column%
__line%A_Index% := sortBy . r_delimiter . __line%A_Index% . "`n"
r_file := r_file . __line%A_Index%
}
Sort r_file, N %order%
;reload the array
loop parse, r_file, `n
{
i++
x = 0
loop parse, A_LoopField, %r2_delimiter%
{
if (A_Index = 1)
continue
x++
__matrix%i%_%x% := A_LoopField
if (x = 1)
__line%i% := Format4CSV(__matrix%i%_%x%)
else
__line%i% := __line%i% . r_delimiter . Format4CSV(__matrix%i%_%x%)
}
}
r_file =
loop %r_lastRow%
r_file := r_file . __line%A_Index% . "`n"
r_updated := true
return
}
CSV_FINAL(copy=0) {
global
if(r_updated) {
if (copy = 1) {
r_fullName = %r_path%\%r_nameNoExt%_copy.%r_ext%
ifExist %r_fullName%
fileDelete %r_fullName%
}
else
fileDelete %r_fullName%
loop %r_lastRow%
r_file := r_file . __line%A_Index% . "`n"
}
fileAppend %r_file%, %r_fullName%
return
}
;author Rhys.
;[Function] Convert String for CSV - Format4CSV() beta 1
;can be found at http://www.autohotkey.com/forum/viewtopic.php?t=27233
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
}
Note, reserved variables:
r_row
r_lastRow
r_col
r_lastCol
r_headerExists
r_file
r_name
r_path
r_ext
r_nameNoExt
r_delimiter
(2 underscores) __matrix%dim1%_%dim2% - is a reserved array
(2 underscores) __line%dim1% - is a reserved array
Functions:CSV_SET(r_file, r_header=0, r_delimiter=",")whenever you use this function, you write CSV file into an array.
From this point on you are now working with array, not the file itself.
Parameters:
file: file to be read
r_header: make it 1(true) if you have headers (0 is default)
r_delimiter: A_Tab or A_Space (coma is default)
Code:
CSV_SET("example.csv", 1)
the following will read "example.csv" into an array, first line is considered a header.
CSV_FINAL(copy=0)use if you want to apply written changes to fileParameters: make it as 1 to apply changes to file copy which will be created in the same directory as the original
CSV_totalRows()returns total amount of rows in fileParameters: None
CSV_totalCols()returns total amount of columns in fileParameters: None
CSV_readCell(row = 1, column = 1)reads specified cellParameters:
row: row to be read
column: column to be read
CSV_writeCell(row, column, value)writes specified cellParameters:
row: row to be written to
column: column to be written to
value: value to be written
Note, you can only overwrite already existing cells.
Creation of new rows or new columns is not yet implementedCSV_find(value, row=0, column=0, offset_x=0, offset_y=0)searchParameters:
value: what to search for
row: specify this if you know what row searched value is located in.
column: specify this if you know what column searched value is located in.
offset_x: specify this if you want to offset search result by a number of rows. Accepts positive and negative values.
offset_y: specify this if you want to offset search result by a number of columns. Accepts positive and negative values.
return values:
if row and column weren't specified in search, function returns row number followed by a column number separated with coma.
if row was specified, function returns column number
if column was specified, function returns row number
CSV_exists(value)returns 1(true) if searched text if found, 0(false) otherwiseParameters:
value: searched text.
CSV_sort(column=1, order="asc")sortingParameters:
Column: specify what column you want to sort by.
order: leave blank for ascending sort or specify "desc" for descending sort.
CSV_fileName()returns file nameParameters: None
CSV_filePath()returns file pathParameters: None
CSV_fullName()returns full file name(includes path)Parameters: None
Example:meet abc.csv it's going to be our lab rat.
Quote:
header 1,header 2,header 3
2,b,b2
3,c,c3
4,d,d4
5,e,e5
let's squeeze some info out of it:
Code:
CSV_SET("abc.csv")
MsgBox % "total rows: " . CSV_totalRows() . "`ntotal columns: " . CSV_totalCols()
MsgBox % "B2 content: " . CSV_readCell(2, 2) . "`nC1 content: " . CSV_readCell(1, 3)
MsgBox % "file name: " . CSV_fileName() . "`nfile path: " . CSV_filePath() . "`nfull name: " . CSV_FullName()
output:
Quote:
total rows: 5
total columns: 3
B2 content: b
C1 content: header 3
file name: abc.csv
file path: C:\
full name: C:\abc.csv
Here's an example with headers set to 1(true):
Code:
CSV_SET("abc.csv", 1)
MsgBox % CSV_readCell(1, 3)
output:
Quote:
b2
Let's do a search:
Code:
CSV_SET("abc.csv")
MsgBox % "d4 x,y: " . CSV_find("d4")
MsgBox % "d4 x: " . CSV_find("d4", 4)
MsgBox % "c3 y: " . CSV_find("c3", 0, 3)
MsgBox % "d offset by 1 row(back), offset 1 column: " . CSV_find("d", 0, 0, -1, 1)
output:
Quote:
d4 x,y: 4,4
d4 x: 4
c3 y: 3
d offset by 1 row(back), offset 1 column: 1,3
let's add some values to it:
Code:
CSV_SET("abc.csv")
CSV_writeCell(1, 1, "Updated header")
MsgBox % CSV_readCell()
this will write come new values to abc.csv, but it wont be saved just yet, so if you open the file you wont see any changes.
Let's save our changes:
Code:
CSV_SET("abc.csv")
CSV_writeCell(1, 1, "Updated header")
CSV_FINAL()
Now the file is updated.
Have fun
Login
Register
FAQ
Search
. I noticed that heresy is working on a text file manipulation library, i think i'll try to help him out instead.