Search code examples
csvkdb

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
Solution

  • 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.