Trying to Parse a CSV that has line breaks the way excel does it Topic is solved

[jsong55]

Code: Select all

#Requires AutoHotkey v2.0-a
#SingleInstance Force
#Include <JSON>
SetWorkingDir(A_ScriptDir)
csv_text:="
(LTRIM
col1,col2,col3
1,Ben,"my name
is Ben
and here are my details"
2,Jason,My name is jason with 1 line
)"

join(str) {
 out := ""
 Loop Parse, str, "`n", "`r"
  If Ord(A_LoopField) = 32 ; Space
       out .= Trim(A_LoopField)
  Else out .= (out = "" ? "" : "`n") A_LoopField
 Return out
}

msgbox(csv_text)
final_array:=[]
loop parse join(csv_text), "`n"
{
    i:=A_index
    final_array.push([])
    loop parse A_LoopField, "CSV"
    {
        final_array[i].push(A_LoopField)
    }
}
msgbox(JSON.Stringify(final_array))

not working as I expected. Any help will be greatly appreciated

[mikeyww]

You have to look at what the code does. Your function looks for a leading space, right? That would need to be changed to accommodate your data's format.

Code: Select all

#Requires AutoHotkey v2.0
csv_text := '
(
col1,col2,col3
1,Ben,"my name
is Ben
and here are my details"
2,Jason,My name is jason with 1 line
)'
MsgBox csv_text '`n`n' join(csv_text), 'Input and output', 'Iconi'

join(str) {
 out := '', more := False
 Loop Parse str, '`n', '`r' {
  StrReplace(A_LoopField, '"',,, &n)  ; n = Number of quotation marks
  out  .= (more ? ' ' : out = '' ? '' : '`n') A_LoopField
  more ^= Mod(n, 2)
 }
 Return out
}

[jsong55]

I'm trying to create 2d array actually. Got the join function from your answer in a ahk 1 thread

[mikeyww]

My post updates the "join" function for use with your new data format. You can use it in your script. If you run my demonstration script, it shows you the joined text.

[flyingDman]

@jsong55 it is unclear to me what you expect from your script. I also believe the premise might be wrong. When Excel exports to a CSV, cells that include a line break will show a linefeed (`n) while rows are delimited by a carriage return and a linefeed (`r`n). The image clarifies.
Because certain editors (incl. Scite) replace a single `n with `r`n, it is tricky to run your script the way you do. It is unclear whether they are single `n or if they have been replaced with `r`n. It is better to use fileread() as that will preserve the single `n's and not introduce unwanted "`r". That said parsing with the "CSV" option should still be the best way to deal with these files.
So, do you want to eliminate these single linefeeds i.e. "flatten" the cells that have multiple rows (=replace the `n with " ")? or do you want to preserve the linefeeds ?. Do you want to output to a string or to an array?
The code below will do both based on the csv file shown in the picture.

Code: Select all

csv := fileread("testcsv2.csv")

for x,y in strsplit(csv,"`r`n")
	{
	loop parse, y, "csv"
		lst .= (a_index = 1 ? "":",") strreplace(a_loopfield,"`n"," ")
	lst .= "`n" 
	}
msgbox trim(lst,"`n")
	
tbl := []
for x,y in strsplit(csv,"`r`n")
	{
	rw := []	
	loop parse, y, "csv"
		rw.push(a_loopfield)
	(y) && tbl.push(rw)
	}
; show all cells 
for x,y in tbl
	for a,b in y
		msgbox b " (cell " chr(64 + a) x ")"

20240331_132248.jpg (19.03 KiB) Viewed 107 times

[jsong55]

@flyingDman you hit the nail on the head, just needed that 2d array

[jsong55]

@mikeyww
thanks for your input sorry I wasn't clearer