How to deal with a comma inside a column of a comma seperated file?

[haste]

I've got a comma seperated file. One of the columns contains a variable with a comma inside. How can I deal with this?

Example CSV file:

Code: Select all

Startdate,Enddate,Class,Candidate,Birthdate,Code
"1-8-2017","31-7-2018","1L","Student 1126, Carol","24-4-2002","PEKZDAS7JT7E"
"1-8-2017","31-7-2018","1L","Student 1235, James","12-5-2001","NGSQL5SE98RG"

The column candidate contains a comma. With my code it will only show me 'Student 1126' and 'Student 1235' and not their names. What can I do to change this?

Code: Select all

FileRead, StartCode, startcode.csv
	Loop, Parse, Startcode, `n, `r
	{
		Stringsplit, parts, A_LoopField, `,, ""
		{	
		MsgBox %parts5%
		}	
	}	

[Guest]

In this you shouldn't use StringSplit but a Loop, parse, string, CSV which take into account the properly formatted CSV and splitt cells, not text. You can use the loop to create sequentially name variables part%A_Index%:=A_LoopField

[KeypressGuy]

To split a string that is in standard CSV (comma separated value) format, use a parsing loop since it has built-in CSV handling, instead of StringSplit.

Loop, parse, A_LoopField, CSV

[evilC]

Code: Select all

str := """1-8-2017"",""31-7-2018"",""1L"",""Student 1126, Carol"",""24-4-2002"",""PEKZDAS7JT7E"""
pos := 1
while (pos := RegExMatch(str, "U)\""(.+)\""", match, pos+StrLen(match))){
	MsgBox % match
}

[BoBo]

A standard to use columns that contains a comma would be to change the delimiter to a semi-colon:
aaa; bbb, ccc; ddd

[AlphaBravo]

Code: Select all

str := """1-8-2017"",""31-7-2018"",""1L"",""Student 1126, Carol"",""24-4-2002"",""PEKZDAS7JT7E"""
pos := 1
while (pos := RegExMatch(str, "U)\""(.+)\""", match, pos+StrLen(match))){
	MsgBox % match
}

That won't work on a general CSV file, it is only specific to your "str" format where all fields are surrounded by double-quotes, try this:

Code: Select all

Str = a1,"""b1","c,1","d1""","""e,1""",f;1
pos := 1
while (pos := RegExMatch(str, "U)\""(.+)\""", match, pos+StrLen(match))){
	res .= match "`n"
}

res .= "`n--------`n"
loop, parse, str, CSV
	res .= A_LoopField "`n"

MsgBox % res

[evilC]

That won't work on a general CSV file, it is only specific to your "str" format where all fields are surrounded by double-quotes

Click download on the sample data in the OP.
In that file, each field is surrounded by quotes, so it will indeed work.
My sample code merely simulates what you would get if you read a line of that file.
The only real shortcoming is if the values could contain escaped quotes, but it would be quite simple to remedy that

[AlphaBravo]

Click download on the sample data in the OP.
In that file, each field is surrounded by quotes, so it will indeed work.

I was merely stating that it won't work on a general file but yes, we're in agreement, it will work on the sample data in the OP.

The only real shortcoming is if the values could contain escaped quotes, but it would be quite simple to remedy that

Would you show the remedy for a sample CSV file where some values have escaped quotes. like this one:

Code: Select all

Str = "a1","""b1","c,1","d1""","""e,1""","f;1"

Edit:
Not "quite simple"

Code: Select all

Str = "a1","""b1","c,1","d1""","""e,1""","f;1","g"",1"
while pos := RegExMatch(Str, "(?|(.*?),(?=(([^""]*""){2})*[^""]*$)|(.+)$)", m, A_Index=1?1:pos+StrLen(m))
	res .= m1 "`n"
MsgBox % res

[evilC]

This is all very much complicated by the way AHK escapes quotes.
I am assuming that the text comes from a different application, one which handles strings in a c-like manner (ie escape char is backslash)

In that case, the regex is rather simple: (?<!\\)"(.*)(?<!\\)"
All I did was add a look-behind to say that quotes are not allowed to be proceeded by an escape character ((?<!\\)")

So given this sample data:

Code: Select all

"1\"1\"11", "2\"2\"2", "33\"3\"", "ddd", ""

The AHK code would be:

Code: Select all

str :=  """\""1\""11"", ""2\""2\""2"", ""33\""3\"""", ""ddd"", """""

pos := 1
while (pos := RegExMatch(str, "U)(?<!\\)""(.*)(?<!\\)""", match, pos+StrLen(match))){
	MsgBox % match
}

[AlphaBravo]

Str = "a1","""b1","c,1","d1""","""e,1""","f;1","g"",1" came straight out of Excel / Save-as / CSV
loop, parse, str, CSV shows expected fields, but yes the way AHK escapes quotes is somewhat complicated