Writing Csv File with .Q.fs[]

[Andrew McLaughlin]

Hi all, I'm new to Kdb and have been having trouble writing big tables to .Csv files under the W32 version due to WSFULL

and I am trying to work around it with .Q.fs[] chunking.

The following code fails with wsfull on Kdb32:

/------------------------

DeviceData:();

 

/ Create 12.5M Element Lists.

did: 10 + 12500000?9;

dt1: 0.000000 + 12500000?999.999999999;

dt2: 1.000000 + 12500000?999.999999999;

dt3: 2.000000 + 12500000?999.999999999;

ct: 12500000?24:00:00.000;

cd: 2016.01.01d + 12500000?31; / Jan.

cdt: "z" $ (cd + ct);

 

/ Create Table.

DeviceData:([] DeviceId:did; Data1:dt1; Data2:dt2; Data3:dt3; CreateDate:cdt);

 

/ Reorder Table.

DeviceData: `CreateDate xasc DeviceData;

 

/ Write File to Csv Fails for more than 5M records for Kdb32.

`:d:/BusLocal/Intelligo/Benchmark/DeviceData.Csv 0: csv 0:  DeviceData;

/------------------------

 

/ m trying to use .Q.fs[] to address this by writing the file in chunks but I’m failing miserably presumably due to bad syntax:

/ Doesn’t Work - There on;y seem to be examples for reading files, not writing...

.Q.fs[{`:d:/BusLocal/Intelligo/Benchmark/DeviceData.Csv 0: csv 0: x}; DeviceData];

 

What’s wrong with my syntax please?

Thank you,

Andrew McLaughlin

 

Thank you.

[Jonathon McMurray]

.Q.fs is only used for reading in files. However, there are ways to do this.

 

Here is an example of writing a CSV file in chunks:

q)h:hopen `:DeviceData.csv                                        /open handle to CSV file

q)(neg h) csv 0: 0#DeviceData;                                    /write column headers to CSV

q){(neg h) csv 0: value flip x} each 1000000 cut DeviceData;      /write data to CSV

 

Here chunks of 1M records are used. value flip x is used to remove the headers from the data before writing to CSV, so that headers only appear at the top of the file. Writing to a handle instead of writing to the file directly with `:DeviceData 0: appends to the end of the file, instead of overwriting it each time.

Hope this helps

Jonathon

--
You received this message because you are subscribed to the Google Groups "Kdb+ Personal Developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to personal-kdbpl...@googlegroups.com.
To post to this group, send email to personal...@googlegroups.com.
Visit this group at https://groups.google.com/group/personal-kdbplus.
For more options, visit https://groups.google.com/d/optout.

 

This e-mail message is intended to be received only by persons entitled to receive the confidential information it may contain. E-mail messages to clients of AquaQ Analytics may contain information that is confidential and legally privileged. Please do not read, copy, forward, or store this message unless you are an intended recipient of it. If you have received this message in error, please forward it to the sender and delete it completely from your computer system.

[Andrew McLaughlin]

Thank you Jonathon.

My mistake was thinking that .Q.fs[] what a more general function.

Your code by far the neatest solution I have found thank you - well done:

/----------------------------

h:hopen `:d:/db/TestFiles/DeviceData.csv;     / Open handle to CSV file.
(neg h) csv 0: 0#DeviceData;                                                       / Write column headers to CSV.
{(neg h) csv 0: value flip x} each 1000000 cut DeviceData;         / Write data to CSV.
hclose h;                                                                                       / Close file handle.

/----------------------------

Regards,

Andrew

To post to this group, send email to persona...@googlegroups.com.

Visit this group at https://groups.google.com/group/personal-kdbplus.
For more options, visit https://groups.google.com/d/optout.