CODEWITHSUNDEEP
csvkdb+
Ivan Kush
Ivan Kush

Reputation: 3157

How can I load a CSV file with " as the delimiter in kdb?

How can I load a CSV file with " delimiters? See 2nd row. dd is loaded in symbolCol, not stringCol

$cat kdb.log
longCol"floatCol"symbolCol"stringCol
1"4"b"bb
2"5""dd

I'm loading using

tab:("JFSS";enlist "\"") 0: `$"/home/..../kdb.log"

and dd is loaded in symbolCol, not stringCol

q)tab
longCol floatCol symbolCol stringCol
------------------------------------
1       4        b         bb      
2       5        dd                
q)tab[`symbolCol]
`b`dd

Upvotes: 0

Views: 474

Answers (2)

Ivan Kush
Ivan Kush

Reputation: 3157

kdb support advised this code. kdb+ uses double-quotes to detect and ignore embedded delimiters in the file. So instead of using 0: we can parse the csv file as follows.

split:"\"" vs' read0 `$":/tmp/kdb.log"; 
headers:`$split[0];   
tab:flip headers!"JFS*"$flip 1_split;

Upvotes: 0

terrylynch
terrylynch

Reputation: 13572

This actually looks like it could be a bug in the parser - worth raising with the Kx team. Using " as a delimiter is pretty rare so they probably haven't accounted for it.

The only way around it that I could find was to replace the " with a better delimiter. Ideally you should change the delimiter outside of kdb but if you have to do it in kdb you could do:

q)("JFSS";enlist",") 0: ssr[;"\"";","]each read0`$":/home/..../kdb.log"
longCol floatCol symbolCol stringCol
------------------------------------
1       4        b         bb
2       5                  dd
q)

Use a delimiter that won't ever appear in the file.

Upvotes: 1

Related Questions