Load csv file data into tables

Created tables as below :

source:([id:`symbol$()] ric:();source:();Date:`datetime$())
property:([id:`symbol$()] Value:())


Then i have two .csv files which include two tables datas.

.csv

property.csv showing as below :

property.csv

id,Value
TEST1,1
TEST2,2


source.csv showing as below :

source.csv

id,ric,source,Date
1,TRST,QO,2017-07-07 11:42:30.603
2,TRST2,QOT,2018-07-07 11:42:30.603


Now , how to load csv file data into each tables one time

csv

2 Answers
2

You can use the 0: to load delimited records. https://code.kx.com/wiki/Reference/ZeroColon

The most simple form of the function is (types; delimiter) 0: filehandle

(types; delimiter) 0: filehandle

The types should be given as their uppercase letter representations, one for each column or a blank space to ignore a column. e.g using "SJ" for source.csv would mean I wanted to read in the id column as a symbol and the value column as a long.

source.csv

The delimiter specifies how each columns is separated, in your case Comma Separated Values (CSV). You can pass in the delimiter as a string "," which will treat every row as part of the data and return a nested list of the columns which you can either insert into a table with matching schema or you can append on headers and flip the dictionary manually and then flip to get a table like so: flip `id`value!("IS";",") 0: `:test.txt.

","

flip `id`value!("IS";",") 0: `:test.txt

If you have column headers as the first row in the csv you can pass an enlisted delimeter enlist "," which will then use the column headers and return a table in kdb with these as the headers, which you can then rename if you see fit.

enlist ","

As the files you want to read in have different types for the columns and are to bed into you could create a function to read them in for examples

{x insert (y;enlist ",") 0:z}'[(`source;`property);("SSSP";"SJ");(`:source.csv;`:property.csv)]


Which would allow you to specify the name of the table that should be created, the column types and the file handle of the file.

I would suggest a timestamp instead of the (depreciated) datetime as it is stored as a long instead of a float so there will be no issues with comparison.

you can use key to list the contents of the dir ;

key

files: key `:.; /get the contents of the dir
files:files where files like "*.csv"; /filter the csv files
m:`property.csv`source.csv!("SJ";"JSSZ"); /create the mappings for each csv file
{[f] .[first ` vs f;();:; (m@f;enlist csv) 0: hsym f]}each files


and finally, load each csv file; please note here the directory is 'pwd', you might need to add the dir path to each file before using 0:

each

csv

0:

whats the :m:property.csvsource.csv!("**";"****").why it still return property.csv`source.csv!("**";"****")
– James
2 days ago

s the :m:

source.csv!("**";"****").why it still return

Since your csv headers are different, you can specify how you want to load the individual csv file. e.g. m:property.csvsource.csv!("SJ";"JSSZ")
– nyi
2 days ago

property.csv

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.