How to insert data from csv into an empty table.

Community Support
1

Hi All,

  A have an empty table in KDB.

  The corresponding data of the table is in a CSV file. How Can I insert the data into the table in one go? 

  The CSV file doesn’t contain the column names. 

  One way is: The first column of CSV can contain the column name.

  cmd :  test_table : (“ISI <column data types”; enlist “,”) 0:`data.csv  –> This will create the table and insert the data as well.

  But my approach is to insert the data in an already created table. 

  Could anyone please help.

Thanks and Regards,

  Pratap

2

Hi Pratap,

If you would like to insert the csv into an already existing table, you can user upsert or ,:

For example:

With a csv file with the contents:

a,b,c1,x,34,y,67,z,9

In a q session I have a table defined as:

q)table:([]a:int$();b:$();c:int$())q)tablea b c-----`

You can use upsert to insert the rows into the table:

q)table upsert (“ISI”;enlist “,”)0:data.csvtableq)tablea b c-----1 x 34 y 67 z 9`

Or you can use ,: to join the new data to the table:

q)table,:("ISI";enlist ",")0:data.csvq)tablea b c-----1 x 34 y 67 z 9`

However these only work if the table is a not a keyed table, as a keyed table would only have its data updated with regards to the keyed column rather than adding new rows.

Also, in the case that the csv does not contain headers, then you would have to add them.

Does this help your problem?

Regards,

Cameron

3

Hi Cameron,

 Thanks for the Swift reply.

 Even in the example. The CSV contain the column name (a,b,c)

a,b,c  –> Column name of the “table”
1,x,3
4,y,6
7,z,9  

But in my case, I have the empty table 

q)table:(a:int$();b:$();c:`int$())
q)table
a b c
----- 

And CSV data without the column names a,b,c

1,x,3
4,y,6
7,z,9  

If we won’t mention the column names in the table both approaches are not working.

table upsert ("ISI";enlist ",")0:data.csv  

 table,:(“ISI”;enlist “,”)0:`data.csv  

Thanks and Regards,

 Pratap

4

Hi Pratap,

As I mentioned before, one way to fix this would be to manually add the headers to the csv file.

However, you can do the following to accomplish adding the data into the existing table:

With a csv file with no headers:

1,x,34,y,67,z,9

You can load in the data to create a table with the same headers as the table you want to join it into (note you should not need to use enlist “,” when the csv does not contain headers):

q)flip (cols table)!("ISI";",")0:data.csva b c-----1 x 34 y 67 z 9`

This can then be inserted into the table as before:

q)table,:flip (cols table)!("ISI";",")0:data.csvq)tablea b c-----1 x 34 y 67 z 9`

I hope this solves your issue.

Regards,

Cameron

5

Hi Cameron,

Thanks a million.

This absolutely solved the problem.  :)

Regards,

Pratap