Thanks for this, I hadn't ever been interested in CSV before, but I thought I'd take a look at it.
- Nice to know that the Loop command can parse it also, although I'd suppose you'd have to handle CRs/LFs manually, and add double quotes back in where required, which is not too difficult.
- From previous experience with parsing text, things tend to have an odd/even pattern, which was also the case here, e.g. parsing command line parameters, or trying to create a Deref function for AutoHotkey that handles the % character.
- The odd/even nature of the problem, makes it awkward for RegEx to handle, although there may be a way.
- Anyway I think I've mostly sussed the main problems and nature of CSV, unless I've omitted something, which I'd be glad to know about. Cheers.
Code: Select all
;if you take a CSV string:
;e.g. "sally","doe","Tucson, AZ"
;or: sally,doe,"Tucson, AZ"
;and separate by double quotes,
;odd items contain separator commas,
;and even items contain literal commas:
; "sally","doe","Tucson, AZ","string,""string,"
;1 2 3 4 5 6 7 8 910 11
; sally,doe,"Tucson, AZ","string,""string,"
;1 2 3 4 56 7
;the special characters in CSV files are: " , CR LF
;(i.e. CR: carriage return, LF: linefeed)
;I saved a CSV file via Excel,
;it only added double quotes where necessary to avoid ambiguity:
;a,z,z
;"b1,b2",z,z
;"c1""c2",z,z
q:: ;replace separator quotes with ~ and literal quotes with _
vText = ;continuation section
(Join`r`n
"sally","doe","Tucson, AZ"
sally,doe,"Tucson, AZ"
a,z,z
"b1,b2",z,z
"c1""c2",z,z
)
vOutput := ""
VarSetCapacity(vOutput, (StrLen(vText)+2)*2)
Loop, Parse, vText, `n, `r
{
vTemp := A_LoopField
Loop, Parse, vTemp, % Chr(34)
{
if A_Index & 1
vOutput .= StrReplace(A_LoopField, ",", "~") Chr(34)
else
vOutput .= StrReplace(A_LoopField, ",", "_") Chr(34)
}
vOutput := SubStr(vOutput, 1, -1) "`r`n"
}
vOutput := SubStr(vOutput, 1, -2)
MsgBox, % vOutput
return