File Transfer

MonetDB supports the non-standard COPY INTO statement to load a CSV-like text file into a table or to dump a table into a text file. This statement has an optional modifier ON CLIENT to indicate that the server should not try to open the file on the server side but instead ask the client to open it on its behalf.

For example::

	COPY INTO mytable FROM 'data.csv' ON CLIENT
	USING DELIMITERS ',', E'\n', '"';

For security reasons, monetdb-nodejs enforces files to be relative to the current working directory of the Node.js process.

Skip rows and early cancellation

MonetDB's COPY INTO statement allows you to skip, for example, the first line in a file using the modifier OFFSET 2 and load n records from the file using the RECORDS modifier.

	COPY 100 RECORDS OFFSET 2 INTO mytable FROM 'data.csv' ON CLIENT

For detailed documentation on COPY INTO statement, please vist MonetDB documentation on data import and export (opens in a new tab).

Examples

Upload data from file

Assume data.csv with the following content:

cat<<EOF>data.csv
1|one
2|two
3|three
EOF

One can upload this file into MonetDB using:

import {Connection} from 'monetdb';
 
const conn = new Connection({database: 'test'});
const ready = await conn.connect();
await conn.execute('create table foo(i int, a varchar(10))');
let res = await conn.execute(`copy into foo from \'data.csv\' on client`);
res = await conn.execute('select * from foo order by i');
console.log(res.data);
// [[1, 'one'], [2, 'two'], [3, 'three']]

Download data to file

// Download
const ready = await conn.connect();
let res = await conn.execute('copy (select * from sys.generate_series(1,1001)) into \'foo.csv\' on client');
console.log(res.affectedRows);
// 1000