 |
AutoHotkey Community
Let's help each other out
|
| View previous topic :: View next topic |
| Author |
Message |
neXt
Joined: 19 Mar 2007
Posts: 463
|
 Posted: Tue Jun 17, 2008 11:35 pm Post subject: simplified csv operations. |
 |
|
credits:
HugoV (for all bug reports and suggestions.)
Rhys (for his Format4CSV() beta 1 function.)
Format4CSV() is included with this code.
| Code: | ;+--------------+
;|function list.|
;+--------------+
/*
set_CSV(r_file, r_header=0, r_delimiter=",")
finalize_CSV(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
set_CSV(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 = ,
r2_delimiter = CSV
else
r2_delimiter = %r_delimiter%
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, %r2_delimiter%
{
if(r_row = 1)
r_col++
__matrix%r_row%_%A_Index% := regExReplace(A_LoopField, "\R?$")
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 - 1
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++
regExReplace(__line%row%, __matrix%row%_%column%, Format4CSV(value))
__matrix%row%_%column% := Format4CSV(value)
r_updated := false
return
}
XLS_writeCell(range, value) {
global
if r_headerExists = 1
row++
StringLeft column, range, 1
StringReplace row, range, %column%
StringUpper column, column
column := ASC(column) - 64
regExReplace(__line%row%, __matrix%row%_%column%, Format4CSV(value))
__matrix%row%_%column% := Format4CSV(value)
r_updated := false
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
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 =
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
}
finalize_CSV(copy=0) {
global
if (copy = 1) {
r_fullName = %r_path%\%r_nameNoExt%_copy.%r_ext%
ifExist %r_fullName%
fileDelete %r_fullName%
}
else
fileDelete %r_fullName%
if(r_updated = "false") {
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
Functions:
set_CSV(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: | | set_CSV("example.csv", 1) |
the following will read "example.csv" into an array, first line is considered a header.
finalize_CSV(copy=0)
use if you want to apply written changes to file
Parameters: 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 file
Parameters: None
CSV_totalCols()
returns total amount of columns in file
Parameters: None
CSV_readCell(row = 1, column = 1)
reads specified cell
Parameters:
row: row to be read
column: column to be read
CSV_writeCell(row, column, value)
writes specified cell
Parameters:
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 implemented
CSV_find(value, row=0, column=0, offset_x=0, offset_y=0)
search
Parameters:
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) otherwise
Parameters:
value: searched text.
CSV_sort(column=1, order="asc")
sorting
Parameters:
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 name
Parameters: None
CSV_filePath()
returns file path
Parameters: 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: |
set_CSV("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: |
set_CSV("abc.csv", 1)
MsgBox % CSV_readCell(1, 3)
|
output:
Let's do a search:
| Code: |
set_CSV("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: |
set_CSV("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: |
set_CSV("abc.csv")
CSV_writeCell(1, 1, "Updated header")
finalize_CSV()
|
Now the file is updated.
Have fun 
Last edited by neXt on Mon Aug 04, 2008 5:18 pm; edited 21 times in total |
|
| Back to top |
|
 |
shajul
Joined: 15 Sep 2006
Posts: 26
Location: India
|
| Posted: Wed Jun 18, 2008 6:28 pm Post subject: very nice |
|
|
very nice!!
but may involve lot of read-writes to disk, if multiple ops are to be carried out! also code for creating backups may be prudent before filedelete.
_________________
Shajul. |
|
| Back to top |
|
|
neXt
Joined: 19 Mar 2007
Posts: 463
|
| Posted: Thu Jun 19, 2008 3:52 am Post subject: |
|
|
Good idea about back up, i'll work on that.
Yeah, you are right, it is a lot of requests to disk, i though of using some "start" trigger, do all operations in memory and then write to disk, but haven't worked on it yet. |
|
| Back to top |
|
|
Wouther
Joined: 01 May 2007
Posts: 78
Location: The Netherlands
|
|
| Back to top |
|
|
n-l-i-d
Guest
|
| Posted: Thu Jun 19, 2008 10:59 am Post subject: |
|
|
| Nice. It might be good to add a "custom delimiter" option, since CSV files are not automatically always really comma-seperated files (could be semi-colon or tab) |
|
| Back to top |
|
|
neXt
Joined: 19 Mar 2007
Posts: 463
|
| Posted: Thu Jun 19, 2008 3:14 pm Post subject: |
|
|
n-l-i-d, thanks!
I'll abandon this btw  . I noticed that heresy is working on a text file manipulation library, i think i'll try to help him out instead. |
|
| Back to top |
|
|
neXt
Joined: 19 Mar 2007
Posts: 463
|
| Posted: Fri Jun 20, 2008 11:01 pm Post subject: |
|
|
Ok, i guess it might take them a while to develop their library, so here's my update(see first post).
Now it reads file in 2 dimensional array and works with it until changes are finalized, that's when changes are applied.
Have fun. |
|
| Back to top |
|
|
HugoV
Joined: 27 May 2007
Posts: 499
|
| Posted: Fri Jun 20, 2008 11:20 pm Post subject: |
|
|
Hi there,
this is a correct CSV file" | Quote: | "1,1",2,3
4,5,6
7,8,9 |
Now try | Code: | set_CSV("simplecsv.csv")
MsgBox % CSV_totalCols() |
and it says 4.
It's easily fixed if you use loop, parse, string, CSV as documented
in the helpfile. Now if you want to use another delimiter like
A_Tab for example it isn't going to work because the CSV parameter
expects a , not a TAB.
[/code] |
|
| Back to top |
|
|
neXt
Joined: 19 Mar 2007
Posts: 463
|
| Posted: Fri Jun 20, 2008 11:29 pm Post subject: |
|
|
| Hmmm, nice one . I'll fix that. |
|
| Back to top |
|
|
HugoV
Joined: 27 May 2007
Posts: 499
|
| Posted: Fri Jun 20, 2008 11:45 pm Post subject: |
|
|
CSV files may contain a header line. This header will contain
names corresponding to the fields. But of course it shouldn't
count when you count nor have an effect on any edit actions.
E.g. editing cell 1,1 would edit the fieldname and not the actual
cell which would be 2,1 in your case.
I pass on a extra parameter in one of my functions I'm working on:
TXT_TransposeCSV(TextFile,HeaderLine = 0)
where I can specify whether or not to account for the header line.
While parsing the file:
| Code: | Loop, Parse, Str, `n, `r
{
If (A_Index = 1) AND (HeaderLine=1)
Continue
Row:=A_Index - HeaderLine
... |
|
|
| Back to top |
|
|
neXt
Joined: 19 Mar 2007
Posts: 463
|
| Posted: Sat Jun 21, 2008 12:19 am Post subject: |
|
|
This is a nice suggestion, i should implement that too, thanks for your comments on this .
I also thought of adding an additional way of reading and writing using "excel" coordinates(A1, B4 etc.) |
|
| Back to top |
|
|
neXt
Joined: 19 Mar 2007
Posts: 463
|
| Posted: Sat Jun 21, 2008 2:06 am Post subject: |
|
|
| Ok, updated. |
|
| Back to top |
|
|
HugoV
Joined: 27 May 2007
Posts: 499
|
| Posted: Sat Jun 21, 2008 8:55 am Post subject: |
|
|
It still gives the incorrect number of columns if you try the simply
test CSV I gave above. Using CSV will give the correct answer
but in that case you can't use any other characters as delimiter
such as TAB for example.
| Code: | set_CSV(r_file, r_header=0, r_delimiter=",") {
global
if r_header = 1
r_headerExists = 1
setBatchLines -1
SplitPath r_file, r_name, r_path
if(r_path = "")
r_path = %A_WorkingDir%
r_row = 0
r_col = 0
fileRead r_file, %r_file%
loop parse, r_file, `n
{
r_row++
loop parse, A_LoopField, CSV
{
if(r_row = 1)
r_col++
__matrix%r_row%_%A_Index% = %A_LoopField%
}
}
return
} |
Also consider this write action: | Code: | | CSV_writeCell(1, 2, "1,234,1234.65") |
if you add that to your test CSV you will have an incorrect CSV
file because of the , in the value parameter. I suggest you have a look at
[Function]Convert String for CSV - Format4CSV() beta 1
http://www.autohotkey.com/forum/viewtopic.php?t=27233
by Rhys
Try to work with this test data:
http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm#ExampleData
I've been using Format4CSV() with the above data and it works
really well as far as I can tell.
Edit: see also http://www.autohotkey.com/forum/viewtopic.php?t=32938 |
|
| Back to top |
|
|
neXt
Joined: 19 Mar 2007
Posts: 463
|
| Posted: Sat Jun 21, 2008 4:27 pm Post subject: |
|
|
I fixed it, thanks !
Simple If statement did the trick.
I also added a feature, now you can apply changes to a file copy, not the original. |
|
| Back to top |
|
|
HugoV
Joined: 27 May 2007
Posts: 499
|
| Posted: Sat Jun 21, 2008 7:30 pm Post subject: |
|
|
| neXt wrote: | | Simple If statement did the trick. |
| Code: | if r_delimiter = ,
delimiter = CSV
....
loop parse, A_LoopField, %delimiter% |
I thought of something similar last night, but this is a more elegant
solution 
Instead of | Code: | value := Format4CSV(value)
__matrix%row%_%column% := value |
doesn't this work as well | Code: | | __matrix%row%_%column% := Format4CSV(value) |
Saves you a line of code ... |
|
| 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
