.Q.fs fails if the file is missing newline after last line

[Sam]

.Q.fs[{`trade insert flip `date`open`high`low`close`volume`sym!("DFFFFIS";",")0:x}]`:file.csv

.Q.fs fails when the last line does not end in a new line. I also want to skip the header of the file as it contains column headers.

How can I skip the first line and put a new line after the last line when reading a .csv file ?

thanks

[Bohák András]

To skip the headers, you can use this form of "0:":
> ("DFFFFIS";enlist ",")0:x
This will use the headers as column names and return a table directly. If your headers in the table do not conform to the ones used in trade, you can use xcol to rename them.

I got an error too, if there was no newline after the last line. Interesting, I do not know why it happens, must be something with how Q.fs chunks the file. As a last resort you could write some script to append every file with a new line.

a

> --
> You received this message because you are subscribed to the Google Groups "Kdb+ Personal Developers" group.
> To view this discussion on the web visit https://groups.google.com/d/msg/personal-kdbplus/-/hzQ6M0Deu80J.
> To post to this group, send email to personal...@googlegroups.com.
> To unsubscribe from this group, send email to personal-kdbpl...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/personal-kdbplus?hl=en.

[Sam]

Thanks. For some reason, it doesn't work when I use it with .Q.fs

q).Q.fs[{`trade insert ("DFFFFIS";enlist ",")0:x}]`:file.csv   /file.csv with header

{`trade insert ("DFFFFIS";enlist ",")0:x}

'mismatch

insert

`trade

+`2006.10.03`24.5`24.51`23.79`24.13`19087300`AMD!(2006.10.03 2006.10.03 2006...

This works -

.Q.fs[{`trade insert flip `date`open`high`low`close`volume`sym!("DFFFFIS";",")0:x}]`:file.csv    /removed header from file.csv

The file is -

date,open,high,low,close,volume,sym

2006.10.03,24.5,24.51,23.79,24.13,19087300,AMD

2006.10.03,27.37,27.48,27.21,27.37,39386200,MSFT

2006.10.04,24.1,25.1,23.95,25.03,17869600,AMD

2006.10.04,27.39,27.96,27.37,27.94,82191200,MSFT

2006.10.05,24.8,25.24,24.6,25.11,17304500,AMD

2006.10.05,27.92,28.11,27.78,27.92,81967200,MSFT

2006.10.06,24.66,24.8,23.96,24.01,17299800,AMD

2006.10.06,27.76,28,27.65,27.87,36452200,MSFT

[Sam]

Thanks. For some reason, it doesn't work when I use it with .Q.fs. I've a large file and I want to process it in segments to avoid loading everything in memory.

q).Q.fs[{`trade insert ("DFFFFIS";enlist ",")0:x}]`:file.csv   /file.csv with header

{`trade insert ("DFFFFIS";enlist ",")0:x}

'mismatch

insert

`trade

+`2006.10.03`24.5`24.51`23.79`24.13`19087300`AMD!(2006.10.03 2006.10.03 2006...

This works -

.Q.fs[{`trade insert flip `date`open`high`low`close`volume`sym!("DFFFFIS";",")0:x}]`:file.csv    /removed header from file.csv

The file is -

date,open,high,low,close,volume,sym

2006.10.03,24.5,24.51,23.79,24.13,19087300,AMD

2006.10.03,27.37,27.48,27.21,27.37,39386200,MSFT

2006.10.04,24.1,25.1,23.95,25.03,17869600,AMD

2006.10.04,27.39,27.96,27.37,27.94,82191200,MSFT

2006.10.05,24.8,25.24,24.6,25.11,17304500,AMD

2006.10.05,27.92,28.11,27.78,27.92,81967200,MSFT

2006.10.06,24.66,24.8,23.96,24.01,17299800,AMD

2006.10.06,27.76,28,27.65,27.87,36452200,MSFT

[Sam]

I understand why it doesn't work. It expects to read a header for every block and fails.

[Bohák András]

I thougth about this, but it seems to fail on the first chunk, not the second. And it clearly interprets the first line of data as col names. Are you sure you had the headers in the file when you got this error:

> 'mismatch
> insert
> `trade
> +`2006.10.03`24.5`24.51`23.79`24.13`19087300`AMD!(2006.10.03 2006.10.03 2006...

> To view this discussion on the web visit https://groups.google.com/d/msg/personal-kdbplus/-/fUA0U8eGGU8J.

[Sam]

yes I had the the headers. But since the header appears only in the first chunk, the remaining chunks use an intermediate data line  as header and fail to insert into table due to column mismatch; hence the mismatch error.