Reading CSV, but maintaining linebreaks and carriage returns between double quotes Topic is solved

[BradleyS]

I have the following code shown below, which imports a CSV file from a path. I have also included the sample text of the CSV file, which should be shown in the list view as 2 rows and 2 columns of data
However, the carriage returns are causing it to be shown as 5 lines.
How can I show the data as 2 rows, by either ignoring the carriage returns, or something else?

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
#SingleInstance force
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.

F1 = ExampleText.csv

/* EXAMPLE OF TEXT IN CSV WHEN OPENED IN NOTEPAD
"Retain comma, between double quotes","Retain carriage return 
 between double quotes"
This has no comma between double quotes,This has no carriage returns between double quotes
"Retain carriage return 
 between double quotes","Retain comma, between double quotes"
*/

Gui, Add, ListView, grid x15 yp+35 hp+280 w605 -LV0x10 vMyList, Column1|Column2|ID
Gui, Show
GoSub, ShowInList
return

ShowInList:
GuiControl, -Redraw, MyList
LV_Delete()
;THIS CODE DEALS WITH TEXT ENCLOSED BETWEEN " " WERE IT INCLUDES A COMMA
Loop Read, % F1
{ 
  arr := []
  Loop Parse, A_LoopReadLine, CSV
    arr.Push(A_LoopField)
  LV_Add("", arr[1],arr[2],A_Index)
}
;BUT HOW DO i DEAL WITH CARRIAGE RETURNS, WHICH ARE MESSING UP THE LISTVIEW DISPLAY?
LV_ModifyCol(1,"280")
LV_ModifyCol(2,"280")
LV_ModifyCol(3,"30 Integer")
GuiControl, 1:+Redraw, MyList
return

GuiClose:
Exitapp

[mikeyww]

Code: Select all

#Requires AutoHotkey v1.1.33.11
filePath := "test.txt"
FileRead txt, % filePath
MsgBox 64, Joined, % join(txt)

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

[BradleyS]

Thank you for this, but I'm not sure how to apply it to my example code

[mikeyww]

Code: Select all

#Requires AutoHotkey v1.1.33.11
filePath := "ExampleText.csv"
FileRead txt, % filePath
Gui Font, s10
Gui Color, FFFF9E
Gui Add, ListView, Grid w600 BackgroundE0E9F3 vLV, ID|Column1|Column2
Loop Parse, % join(txt), `n    ; Loop through each CSV row
{ arr := [A_Index]             ; Column 1 is the ID number
  Loop Parse, A_LoopField, CSV ; Loop through each cell in the row
   arr.Push(A_LoopField)       ; Add the cell's contents to the array
  LV_Add(, arr*)               ; Add the row to the ListView
}
LV_ModifyCol(1, "30 Integer Center")
LV_ModifyCol(2, 280)
LV_ModifyCol(3, 280)
Gui Show,, Text
Return

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

image240326-1044-001.png (82.98 KiB) Viewed 517 times

[BradleyS]

Is it possible to expand on this bit of code, so that it also caters for any empty lines, shown below in the last item of text
If there are any empty lines they are ignored, so that it shows as a single line of text, like the others?

/*
"Retain comma, between double quotes","Retain carriage return
between double quotes"
This has no comma between double quotes,This has no carriage returns between double quotes
"Retain carriage return
between double quotes","Retain comma, between double quotes"
"Retain carriage return, with empty line

between double quotes","Retain comma, between double quotes"
*/

[mikeyww]

The current loop checks for one thing: a leading space. It then acts accordingly, with one of two possible actions. You can modify this approach to check for two things: a leading space or a blank line. The script can then act accordingly, with one of three possible actions.

[just me]

See Trying to Parse a CSV that has line breaks the way excel does it

Code: Select all

CsvLines := StrSplit(CsvFileContent, "`r`n")

works reliably in v1.1 too.

[BradleyS]

The current loop checks for one thing: a leading space. It then acts accordingly, with one of two possible actions. You can modify this approach to check for two things: a leading space or a blank line. The script can then act accordingly, with one of three possible actions.

I've been struggling with this for 2 days now. Where should I put the reference to ignore/remove the empty lines?
I have tried entering the txt := RegExReplace(txt, "`am)^\s+|\h+$|\R\z") in quite a few place in the script but cannot get it to work.
Sorry to be a pain

Code: Select all

#Requires AutoHotkey v1.1.33.11

Gui Font, s10
Gui Color, FFFF9E
Gui Add, ListView, Grid w600 BackgroundE0E9F3 , Column1|Column2|ID
Gui Show,, Text
GoSub, ShowInList
return

+esc::
GuiEscape:
GuiClose:
ExitApp

ShowInList:
filePath := "ExampleText.csv"
FileRead txt, % filePath

Loop Parse, % join(txt), `n    ; Loop through each CSV row
{ 
  arr := []             ; Column 1 is the ID number
  Loop Parse, A_LoopField, CSV ; Loop through each cell in the row
   arr.Push(A_LoopField)       ; Add the cell's contents to the array
  
  LV_Add("", arr[1],arr[2],A_Index)
  ;LV_Add(, arr*)               ; Add the row to the ListView
}
LV_ModifyCol(1, 280)
LV_ModifyCol(2, 280)
LV_ModifyCol(3, "30 Integer Center")
Return

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

[mikeyww]

Your new example has multiple changes. Leading spaces are no longer present. I think that this means that you are not yet sure what your set of samples will be and how they will be formatted. After you decide, you can post that information. The script depends on your input data because it acts upon their format.

[BradleyS]

All I'm trying to do is ignore everything between the " ", so that it shows as a single line within the list view
In fact it's only needed for the 1st column of data, as the 2nd column is restricted to a single line and 30 characters anyway.

What should happen is the listview reads from the csv file and shows each comma separated content as a single line in each column of the list view.
The only change I made was to make sure all the contents of the CSV file is enclosed in double quotes, as I thought that this would make it easier.
For example: "text","text"
The objective is to show any line breaks, empty lines or commas or any special character between the " " as a single line of text in the list view.

Then when I double-click on a line in the list view, it will take the original contents of the 1st column (which will be everything between the double quotes ) and add it to the windows clipboard. Therefore this will now include the unaltered text (raw text) as it is written in the actual csv file, i.e. including all empty lines, commas, etc..

This will then enable me to paste the contents into different applications as raw text. The storage file doesn't have to be a CSV file if it help, it could be a txt file, if it would make things easier.

I'm just trying to create a method of saving popular text I use all the time, so I can just copy and paste, rather than having to continually write the same text out multiple times.

Does that help?

[mikeyww]

Perhaps:

Code: Select all

join(str) {
 out := "", odd := False
 Loop Parse, str, `n, `r
  out .= (odd ? " " : "`n") Trim(A_LoopField), StrReplace(A_LoopField, """",, cnt), odd ^= Mod(cnt, 2)
 Return Trim(RegExReplace(out, " +", " "), " `t`n")
}

[BradleyS]

Thank you so much for this. It will save me a lot of time.
It's very much appreciated.

[flyingDman]

Thanks, @just me. It is a similar issue.
As long as you do not have any `r`n (i.e. CRLF = a carriage return and a linefeed together) inside double quoted text*, you can strsplit(xxx,"`r`n)and use the CSV parsing loop to achieve your 2 objectives: 1) a multi-line cell shown as a one-liner inside a listview and 2) restore the CRs and/or LFs (but no CRLF) when you extract the content of a listview cell:

Code: Select all

F12::
filedelete, csvtest4.csv
var = a,"b,b",c`r`naa,"bb`rbb`n`nbb",cc
fileappend, % var, csvtest4.csv

Gui Add, ListView, gedit -hdr, Column1|Column2|ID
gui, +AlwaysOnTop +E0x08000000
Gui Show, NoActivate, Text                             ; this and the prior line allow you to send to the window below by (double)clicking
filePath := "csvtest4.csv"
FileRead txt, % filePath

for x,y in strsplit(txt,"`r`n")
	{
	rw := []
	loop, parse, y, CSV	
		rw.push(a_loopfield)
	LV_add("",rw*)
	}
LV_ModifyCol()
Return

edit:
If(A_GuiEvent = "DoubleClick")
	{
	LV_GetText(text, A_EventInfo,2)              ; gets content of column 2
	send % text
	}

* I believe having a `r`n inside "" makes the CSV a "malformed" CSV. If you have these `r`n inside "" you can use @mikeyww 's script, but would need to tweak the script to achieve your second objective.
Note: cell (2,2) will show up in the listview as bbbbbb rather than bb bb bb. You can achieve the latter by replacing the `n or `r with a non-breaking space - Chr(160) - and when you double click replace the non-breaking spaces with either `n or `r (or `r`n).

Spoiler

Code: Select all

F12::
filedelete, csvtest4.csv
var = a,"b,b",c`r`naa,"bb`rbb`n`nbb",cc
fileappend, % var, csvtest4.csv

Gui Add, ListView, gedit -hdr, Column1|Column2|ID
gui, +AlwaysOnTop +E0x08000000
Gui Show, NoActivate, Text
filePath := "csvtest4.csv"
FileRead txt, % filePath

for x,y in strsplit(txt,"`r`n")
	{
	rw := []
	loop, parse, y, CSV	
		rw.push(regexreplace(a_loopfield,"`n|`r",Chr(160)))
	LV_add("",rw*)
	}
LV_ModifyCol()
Return

edit:
If(A_GuiEvent = "DoubleClick")
	{
	LV_GetText(text, A_EventInfo,2)
	send % regexreplace(text,Chr(160),"`n")
	}

Example:

20240405_121216.jpg (16.8 KiB) Viewed 234 times