Skip to content

0: File Text

Read or write text

The File Text operator 0: has five forms:

Prepare Text table as a list of delimited strings

Save Text write a list of strings to file

Load CSV field-delimited string, list of strings, or file, as a list or matrix

Load Fixed fixed-format list of strings, or file, as a list or matrix

Key-Value Pairs delimited string as key-value pairs

Prepare Text

Represent a table as a list of delimited strings

delimiter 0: t                          0:[delimiter;t]

Where

  • delimiter is a char atom
  • t is a table in which the columns are either vectors or lists of strings

returns a list of character strings containing text representations of the rows of t separated by delimiter. 

q)csv 0: ([]a:1 2 3;b:`x`y`z)
"a,b"
"1,x"
"2,y"
"3,z"

q)"|" 0: (`a`b`c;1 2 3;"xyz")
"a|1|x"
"b|2|y"
"c|3|z"

Temporals are represented according to ISO 8601.

q)show q:.z.p
2022.03.14D16:12:57.427499000
q)show t:flip`d`t!flip"dt"$/:2#q
d          t
-----------------------
2022.03.14 16:12:57.427
2022.03.14 16:12:57.427
q)csv 0:t
"d,t"
"2022-03-14,16:12:57.427"
"2022-03-14,16:12:57.427"

Any cells containing delimiter will be embraced with " and any embedded " doubled.

q)t:([]x:("foo";"bar,baz";"qu\"ux";"fred\",barney"))
q)t
x
---------------
"foo"
"bar,baz"
"qu\"ux"
"fred\",barney"
q)-1@","0:t;
x
foo
"bar,baz"
qu"ux
"fred"",barney"

Since 4.1t 2023.08.18, csv export of symbol or character vector values containing newlines "\n" are enclosed in double quotes.

q)csv 0:([]("foo\nbar";"baz"))
,"x"
"\"foo\nbar\""
"baz"

Columns that are neither vectors nor lists of strings

Prepare Text signals a type error if a column of its right argument is neither a vector nor a list of strings.

q)t:([]Actual:1.47 0.03 300;FiscalTag:("FY2022Q2";"FY2022Q2";enlist"FY2022H1"))

q)t
Actual FiscalTag
------------------
1.47   "FY2022Q2"
0.03   "FY2022Q2"
300    ,"FY2022H1"
q)csv 0:t
'type
  [0]  csv 0:t
           ^

You cannot diagnose this condition with meta, which examines only the first row of its argument

q)meta t
c        | t f a
---------| -----
Actual   | f
FiscalTag| C

but type each is your friend.

q)cols[t] where 1<(count distinct type each)each t cols t
,`FiscalTag

Q for Mortals §11.4.3 Preparing Text

Save Text

Write a list of strings to file

filesymbol 0: strings                   0:[filesymbol;strings]

Where

  • filesymbol is a file symbol
  • strings a list of character strings

strings are saved as lines in the file. The result of Prepare Text can be used as strings.

q)`:test.txt 0: enlist "text to save"
`:test.txt
q)`:status.txt 0: string system "w"
`:status.txt

If filesymbol

  • does not exist, it is created, with any missing containing directories
  • exists, it is overwritten

save, rsave

Load CSV

Interpret a field-delimited string, list of strings, or file as a list or matrix

(types;delimiter     ) 0: y             0:[(types;delimiter);y]
(types;delimiter;flag) 0: y             0:[(types;delimiter;flag);y]

Where

  • y is a file descriptor, string, or a list of strings
  • types is a string of column type codes in upper case
  • delimiter is a char atom or 1-item list
  • flag (optional, default 0, since V3.4) is a long atom indicating whether line-returns may be embedded in strings: 0 or 1

returns a vector, matrix, or table interpreted from the content of y.

With column names

If delimiter is enlisted, the first row of the content of y is read as column names and the result is a table; otherwise the result is a list of values for each column.

/load 2 columns from space-delimited file with header 
q)t:("SS";enlist" ")0:`:/tmp/txt

Use optional argument flag to allow line returns embedded within strings.

q)("I*";",";1)0:("0,\"ab\nc\"";"1,\"def\"")
0       1
"ab\nc" "def"

Where y is a string and delimiter an atom, returns a single list of the data split and parsed accordingly. 

q)("DT";",")0:"20130315,185540686"
2013.03.15
18:55:40.686

Without column names

If the CSV file contains data but no column names:

0,hea,481
10,dfi,579
20,oil,77

We can read the columns:

q)("ISI";",") 0:`data.csv
0   10  20
hea dfi oil
481 579 77

Create a column dictionary and flip it:

table: flip `a`b`c!("ISI";",") 0:`data.csv

Column names must not be the null symbol `

Multithreaded Load

CSV load (excluding embedded line return mode) can use multiple threads when kdb+ is running in multithreaded mode.

q)v:` sv 10000000#","0:10 10#til 100
q)system"s 10";(10#"J";",")0:v

Since 4.1t 2021.09.28.

Load Fixed

Interpret a fixed-format list of strings or file as a list or matrix

(types; widths) 0: y                    0:[(types;widths);y]

Where

returns a vector or matrix interpreted from the content of y.

q)sum("DT";8 9)0:enlist"20130315185540686"
,2013.03.15D18:55:40.686000000
q)("DT";8 9)0:("20130315185540686";"20130315185540686")
2013.03.15   2013.03.15
18:55:40.686 18:55:40.686
q)dates:("Tue, 04 Jun 2013 07:00:13 +0900";"Tue, 04 Jun 2013 07:00:13 -0500")
q)sum(" Z T";5 20 1 5)0:dates
2013.06.04T16:00:13.000 2013.06.04T02:00:13.000

Load Fixed expects either a \n after every record, or none at all.

/reads a text file containing fixed-length records
q)t:("IFC D";4 8 10 6 4) 0: `:/q/Fixed.txt

Tips for Load CSV and Load Fixed

  • To load a field as a nested character column or list rather than symbol use "*" as the identifier
  • To omit a field from the load use " ".

Multithreaded Load

Fixed width load can use multiple threads when kdb+ is running in multithreaded mode

Since 4.1t 2021.09.28.

Key-Value Pairs

Interpret a delimited string as key-value pairs

x 0: string                             0:[x;string]

Where x is a 3- or 4-char string: 

key-type
field-separator
[asterisk]
record-separator

and key-type is S for symbol, I for integer, or J for long, returns a 2-row matrix of the keys and values. 

q)"S=;"0:"one=1;two=2;three=3"
one  two  three
,"1" ,"2" ,"3"

q)"S:/"0:"one:1/two:2/three:3"
one  two  three
,"1" ,"2" ,"3"

q)"I=;"0:"1=first;2=second;3=third"
1       2        3
"first" "second" "third"

q)s:"8=FIX.4.2\0019=339\00135=D\00134=100322\00149=JM_TEST1\00152=20130425-06:46:46.387"
q)(!/)"I=\001"0:s
8 | "FIX.4.2"
9 | "339"
35| ,"D"
34| "100322"
49| "JM_TEST1"
52| "20130425-06:46:46.387"

The inclusion of an asterisk as the third character allows the delimiter character to appear harmlessly in quoted strings. (Since V3.5.)

q)0N!"I=*,"0:"5=\"hello,world\",6=1";
(5 6i;("hello,world";,"1"))
q)0N!"J=*,"0:"5=\"hello,world\",6=1";
(5 6;("hello,world";,"1"))
q)0N!"S=*,"0:"a=\"hello,world\",b=1";
(`a`b;("hello,world";,"1"))

Q for Mortals §11.5.3 Key-Value Records

Column types and formats

B        boolean     /[01tfyn]/i
G        guid        /[0-9a-f]{8}(-[0-9a-f]{4}){3}-[0-9a-f]{12}/i
X        byte      
H        short       [0-9][0-9]
I        int    
J        long   
E        real   
F        float  
C        char   
S        symbol     
P        timestamp   date?timespan
M        month       [yy]yy[?]mm
D        date        [yy]yy[?]mm[?]dd or [m]m/[d]d/[yy]yy
Z        datetime    date?time
N        timespan    hh[:]mm[:]ss[[.]ddddddddd]
U        minute      hh[:]mm
V        second      hh[:]mm[:]ss
T        time        hh[:]mm[:]ss[[.]ddd]
(blank)  skip           
*                    literal chars

.j namespace for JSON
Datatypes, File system
How do I import a CSV file into a table?
Q for Mortals §11.4.1 Reading and Writing Text Files