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

Get help with using AutoHotkey and its commands and hotkeys
haste
Posts: 8
Joined: 04 Aug 2017, 03:43

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

11 Aug 2017, 09:04

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] [Download] GeSHi © Codebox Plus

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] [Download] GeSHi © Codebox Plus

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

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

11 Aug 2017, 09:18

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
Posts: 10
Joined: 17 Jul 2017, 16:55

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

11 Aug 2017, 09:55

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
User avatar
evilC
Posts: 3368
Joined: 27 Feb 2014, 12:30

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

11 Aug 2017, 10:36

Code: [Select all] [Download] GeSHi © Codebox Plus

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
Posts: 847
Joined: 13 May 2014, 17:15

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

11 Aug 2017, 11:34

A standard to use columns that contains a comma would be to change the delimiter to a semi-colon:
aaa; bbb, ccc; ddd
User avatar
AlphaBravo
Posts: 295
Joined: 29 Sep 2013, 22:59

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

11 Aug 2017, 12:54

evilC wrote:

Code: [Select all] [Download] GeSHi © Codebox Plus

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] [Expand] [Download] GeSHi © Codebox Plus

User avatar
evilC
Posts: 3368
Joined: 27 Feb 2014, 12:30

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

11 Aug 2017, 13:16

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
User avatar
AlphaBravo
Posts: 295
Joined: 29 Sep 2013, 22:59

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

11 Aug 2017, 13:37

evilC wrote: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.

evilC wrote: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] [Download] GeSHi © Codebox Plus

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


Edit:
Not "quite simple"

Code: [Select all] [Download] GeSHi © Codebox Plus

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
User avatar
evilC
Posts: 3368
Joined: 27 Feb 2014, 12:30

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

12 Aug 2017, 14:29

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] [Download] GeSHi © Codebox Plus

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


The AHK code would be:

Code: [Select all] [Download] GeSHi © Codebox Plus

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

pos := 1
while (pos := RegExMatch(str, "U)(?<!\\)""(.*)(?<!\\)""", match, pos+StrLen(match))){
MsgBox % match
}
User avatar
AlphaBravo
Posts: 295
Joined: 29 Sep 2013, 22:59

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

12 Aug 2017, 16:04

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 :-)

Return to “Ask For Help”

Who is online

Users browsing this forum: AlleyArtwork, Bing [Bot], derz00, Nightwolf85, Trigg, Yahoo [Bot] and 19 guests