Skip to content

Working with text files

This page explains how to read and write text files in q: delimited and fixed-width records, streaming an input too large to hold in memory, writing and formatting text, and converting between text formats.

Reading text

Use read0 to read a text file as a list of strings, one per line. Given a file test.txt containing:

hello
world

You can read the file with:

q)read0 `:test.txt
"hello"
"world"

Pass an offset and a length to read part of the file. Here q skips the first 6 characters and reads the next 5:

q)first read0 (`:test.txt;6;5)
"world"

To read the file as a single string with its line breaks intact, see reading text as bytes.

Delimited files

Use 0: to load a file of delimited fields, such as a CSV. You supply a type character per field, and q parses each record accordingly; refer to column types and formats for the full set. Given a file test.csv containing:

DOB,NAME,ID
20130315,Jim,3404
19760113,Sam,2311
33120212,Ian,2211

Enlisting the delimiter tells q the first row holds column names, so the result is a table:

q)("DSJ";enlist ",") 0: `:test.csv
DOB        NAME ID
--------------------
2013.03.15 Jim  3404
1976.01.13 Sam  2311
3312.02.12 Ian  2211

Any single character works as a delimiter, so the following example reads a tab-separated file:

q)("DSJ";enlist "\t") 0: `:tab.txt
DOB        NAME ID
--------------------
2013.03.15 Jim  3404
1976.01.13 Sam  2311
3312.02.12 Ian  2211

Omit the enlist when the file has no header row. The result is then a list of columns rather than a table, which flip turns into a table when you supply the names:

q)("DSJ";",") 0: `:nohdr.csv
2013.03.15 1976.01.13 3312.02.12
Jim        Sam        Ian
3404       2311       2211
q)flip `dob`name`id!("DSJ";",") 0: `:nohdr.csv
dob        name id
--------------------
2013.03.15 Jim  3404
1976.01.13 Sam  2311
3312.02.12 Ian  2211

The type character "*" keeps a field as a string instead of converting it, which is useful for free text or identifiers that you don't want interned as symbols:

q)("D*J";",") 0: `:nohdr.csv
2013.03.15 1976.01.13 3312.02.12
"Jim"      "Sam"      "Ian"
3404       2311       2211

Generate the loader instead of writing it

For files too large/wide to use this approach, KxSystems/kdb/utils/csvguess.q infers the column types and writes the loader script for you. It can also emit an on-disk sorter, and a loader that enumerates every symbol column so that parallel loaders need only read the sym file. Its command-line options are documented at the top of the source file.

Fixed-width records

Use 0: for text files of fixed-length records. Provide each field's width in characters alongside the type characters. Given a file users.txt:

1001  198.00James  STAFF 1997.01.01
1002  142.00Sandra STAFF 1976.01.12
1003  144.12Fred   STAFF 2000.01.23

Each record contains a 4-character long, an 8-character float, a 7-character symbol, a 6-character field to discard, and a 10-character date. The space type " " marks a field to skip, which is why the constant STAFF column is dropped:

q)("JFS D";4 8 7 6 10) 0: `:users.txt
1001       1002       1003
198        142        144.12
James      Sandra     Fred
1997.01.01 1976.01.12 2000.01.23

A width doesn't need to match the natural width of its type; it can be narrower if the value range is small, or wider if the field is padded with whitespace.

Because records are a fixed length, you can use an offset and length to select a range of them. Each record above is 36 characters including its line break, so an offset of 36 and a length of 72 read the two records after the first:

q)("JFS D";4 8 7 6 10) 0: (`:users.txt;36;72)
1002       1003
142        144.12
Sandra     Fred
1976.01.12 2000.01.23

Read on record boundaries

The offset and length are byte counts, not record counts, and nothing checks them against the record layout. A range that starts or ends mid-record parses the misaligned bytes into whatever the type string asks for, giving wrong values rather than an error.

Both loaders are multithreaded

CSV and fixed-width loads read in parallel when q runs with secondary threads, which is worth enabling for large files.

Both loaders also accept a list of strings in place of a file handle, since that is what a text file is once read. This is what lets the same type string parse a whole file, a slice of one, or a chunk handed over by streaming:

q)("JFS D";4 8 7 6 10) 0: read0 `:users.txt
1001       1002       1003
198        142        144.12
James      Sandra     Fred
1997.01.01 1976.01.12 2000.01.23

Streaming from large files

Loading a whole file into memory is not always possible. Streaming reads it in sequence, a chunk at a time, applying a function to each chunk so that only one chunk is resident.

By line

.Q.fs reads a file in chunks of complete lines and applies a unary function to each. It returns the number of bytes read. Given a file data.csv:

2019-10-03, 24.5,  24.51, 23.79, 24.13, 19087300, AMD
2019-10-03, 27.37, 27.48, 27.21, 27.37, 39386200, MSFT
2019-10-04, 24.1,  25.1,  23.95, 25.03, 17869600, AMD
2019-10-04, 27.39, 27.96, 27.37, 27.94, 82191200, MSFT
2019-10-05, 24.8,  25.24, 24.6,  25.11, 17304500, AMD
2019-10-05, 27.92, 28.11, 27.78, 27.92, 81967200, MSFT
2019-10-06, 24.66, 24.8,  23.96, 24.01, 17299800, AMD
2019-10-06, 27.76, 28,    27.65, 27.87, 36452200, MSFT

This file is small enough to arrive as one chunk, so 0N! shows the whole list of lines, followed by the byte count:

q).Q.fs[{0N!x}]`:data.csv
("2019-10-03, 24.5,  24.51, 23.79, 24.13, 19087300, AMD";"2019-10-03, 27.37, 2..
436

The chunk is a list of lines, so 0: parses it into typed columns exactly as it would do for a whole file:

q).Q.fs[{0N!("DFFFFIS";",")0:x}]`:data.csv
(2019.10.03 2019.10.03 2019.10.04 2019.10.04 2019.10.05 2019.10.05 2019.10.06 ..
436

Stream into a database

Accumulating each chunk into an in-memory table would defeat the purpose: a file too large for memory produces a table too large for memory. The point of streaming is that the function writes each chunk out and keeps nothing, so a file of any size can be loaded in a bounded amount of memory.

Name the columns, flip the parsed chunk into a table, and append it to a splayed table on disk with Amend At. .Q.en enumerates the symbol columns against the database's sym file, which splaying requires:

q)colnames:`date`open`high`low`close`volume`sym
q)fn:{.[`:db/trade/; (); ,; .Q.en[`:db] flip colnames!("DFFFFIS";",")0:x]}
q).Q.fs[fn]`:data.csv
436

The result is a database, not a variable — a trade directory of column files, and the sym file that its symbol column enumerates against:

q)key `:db
`s#`sym`trade
q)key `:db/trade
`s#`.d`close`date`high`low`open`sym`volume

Load it and the rows are all there, with sym restored to symbols:

q)\l db
q)count trade
8
q)2#trade
date       open  high  low   close volume   sym
------------------------------------------------
2019.10.03 24.5  24.51 23.79 24.13 19087300 AMD
2019.10.03 27.37 27.48 27.21 27.37 39386200 MSFT
q)exec distinct sym from trade
`sym$`AMD`MSFT

For a date-partitioned database, write each chunk to the partition its rows belong to rather than to one splayed table, then sort and apply the parted attribute once the load is finished — see partitioned tables.

.Q.en locks the sym file — enumerate by hand to go faster

.Q.en opens the sym file, extends it and writes it back on every call, taking a lock while it does. That lock is what makes parallel loaders safe: two processes cannot corrupt the sym file by extending it at once.

It is also a per-chunk cost, and on a long streaming load it can dominate. The alternative is to enumerate against an in-memory sym variable with enum extend and write the sym file once, at the end:

q)sym:`symbol$()
q)colnames:`date`open`high`low`close`volume`sym
q)fn:{.[`:db2/trade/; (); ,; update sym:`sym?sym from flip colnames!("DFFFFIS";",")0:x]}
q).Q.fs[fn]`:data.csv
436

Nothing has touched the database's sym file yet — the domain so far exists only in memory:

q)sym
`AMD`MSFT
q)key `:db2
`s#,`trade

So writing it is the step that completes the load. Forget it and the symbol column is unreadable:

q)`:db2/sym set sym
`:db2/sym

What you give up:

  • No parallel loading. Without the lock, two processes writing the same database will produce inconsistent enumerations.
  • No partial recovery. The mapping lives in memory until that final write, so if the loader dies part-way the data already on disk is meaningless and has to be discarded and reloaded.

Use it for a single-process bulk load you can afford to restart; use .Q.en otherwise.

Three things that bite on a real bulk load
  • Re-running a loader duplicates data. Splayed and partitioned tables cannot be keyed, so appending the same file twice appends the rows twice, silently. Keep a table of the files already loaded and check it before loading, keying on the filename or a hash of the contents.
  • Aborting mid-write can leave a broken table. Ctrl-C, kill -9 or a wsfull part-way through a write can leave some column files longer than others, which is an invalid table rather than merely a short one. Recovering means truncating the column files back to a common length by hand.
  • Parallel loaders must not share a target. Two processes may load different files concurrently only if they write to different partitions. .Q.en protects the sym file, but nothing protects a column file from two simultaneous appends.

By size

.Q.fsn is .Q.fs with an explicit chunk size in bytes, given as an int. Given a file numbers of two 10-digit lines:

0123456789
0123456789

Reading it in 3-byte chunks splits each line into four pieces, the last of which is the single remaining character:

q).Q.fsn[{0N!x};`:numbers;3i]
,"012"
,"345"
,"678"
,,"9"
,"012"
,"345"
,"678"
,,"9"
22

From a pipe

.Q.fps and .Q.fpn are the counterparts that read from a named pipe rather than a file. This lets another process produce the data, so a compressed archive can be decompressed straight into q without ever writing the expanded file to disk.

Given t.csv compressed into t.zip, unzip -p writes to stdout, which a FIFO carries to .Q.fps:

q)system"rm -f fifo && mkfifo fifo"
q)trade:flip `sym`time`ex`cond`size`price!"STCCFF"$:()
q)system"unzip -p t.zip > fifo &"
q).Q.fps[{`trade insert ("STCCFF";",")0:x}]`:fifo
q)trade

For a gzip archive, gunzip -cf plays the same role:

q)system"rm -f fifo && mkfifo fifo"
q)trade:flip `sym`time`ex`cond`size`price!"STCCFF"$:()
q)system"gunzip -cf t.gz > fifo &"
q).Q.fps[{`trade insert ("STCCFF";",")0:x}]`:fifo
q)trade

Writing text

Apply 0: to a list of strings with a file handle on the left, and each string becomes a line:

q)`:test.txt 0: ("hello";"world")
`:test.txt

test.txt then contains:

hello
world

A single string is a list of characters, not a list of strings, so enlist it to write one line:

q)`:test.txt 0: enlist "hello"
`:test.txt

Appending text with hopen

0: replaces a file. To build a file up over several writes, open it with hopen and apply the resulting handle to each string. Always hclose the handle when you are finished:

q)h:hopen `:/tmp/new.txt
q)h "one";
q)read0 `:/tmp/new.txt
"one"
q)h "two";
q)read0 `:/tmp/new.txt
"onetwo"

The handle writes exactly the characters you give it, which is why "one" and "two" ran together. Negate the handle to append a line break after each string:

q)neg[h] "three";
q)neg[h] "four";
q)read0 `:/tmp/new.txt
"onetwothree"
"four"
q)neg[h] ("five";"six";"seven");
q)read0 `:/tmp/new.txt
"onetwothree"
"four"
"five"
"six"
"seven"
q)hclose h

Saving to a text format

save writes a global variable to a file whose extension selects the format. To save a table as a CSV file:

q)t: ([] a: 1 2 3; b: 4 5 6)
q)save `t.csv
`:t.csv

Which produces t.csv:

a,b
1,4
2,5
3,6

The same table saved as XML:

q)save `t.xml
`:t.xml

Produces t.xml:

<R>
<r><a>1</a><b>4</b></r>
<r><a>2</a><b>5</b></r>
<r><a>3</a><b>6</b></r>
</R>

The recognized file extensions are .csv (comma-separated), .txt (tab-separated), .xls, .xml, and .json. If no extension is specified, the KDB-X format is used instead.

The file name is derived from the variable when using save. To choose the file name, format the text yourself and write it with 0: — the next section shows how.

Formatting a table as text

A third form of 0: neither reads nor writes: given a delimiter on the left and a table on the right, it returns the formatted lines as a list of strings. The built-in constant csv is simply ",":

q)t: ([] c1: `a`b`c; c2: 1 2 3)
q)csv
","
q)csv 0: t
"c1,c2"
"a,1"
"b,2"
"c,3"

Any delimiter works:

q)"|" 0: t
"c1|c2"
"a|1"
"b|2"
"c|3"

Pairing it with the writing form of 0: saves a table under any name you like — the two 0: calls here are different operations that happen to share a glyph:

q)`:/tmp/out/report.csv 0: csv 0: t
`:/tmp/out/report.csv

Key-value records

A fourth form of 0: parses key-value text. The left argument is three characters: the key type (S for string, I for int, J for long), the character separating the key from the value (= in this example), and the character delimiting pairs (l here):

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

The result is a two-item list of keys and values, and you can use ! to easily make a dictionary:

q)(!). "S=;" 0: "one=1;two=2;three=3"
one  | ,"1"
two  | ,"2"
three| ,"3"

Converting between text formats

Beyond the text keywords built into the q language — ssr, sv, vs and others — two namespaces convert whole objects to and from text.

.Q.s renders an object the way the console would, as a single string:

q).Q.s til 20
"0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19\n"

The .j namespace converts to and from JSON. .j.j serializes:

q)d: `a`b!(0 1; ("hello"; "world"))
q)d
a| 0       1
b| "hello" "world"
q)j: .j.j d
q)j
"{\"a\":[0,1],\"b\":[\"hello\",\"world\"]}"

And .j.k parses:

q).j.k j
a| 0       1
b| "hello" "world"

Because JSON is just a string in q, you can compose with all syntax from reading and writing text.

The .h namespace offers further markup conversions, mostly for HTML and XML.

You rarely need .h for CSV

.h includes CSV helpers, but 0: and save already directly handle CSV in both directions.

Next steps