CSVRead

Signatures

CSVRead(VARCHAR path);
CSVRead(VARCHAR path, VARCHAR columnNameHeader, VARCHAR stringDecode);

Description

This function is a part of H2.

Please first consult its documentation on the H2 website.

Reads a CSV file. All columns are of type VARCHAR.

Optional variable columnNameHeader is a list of column names separated by the field separator. If NULL, the first line of the file is interpreted as the column names.

Optional variable stringDecode is a space-separated string for setting CSV options. If NULL, its default value is used:

charset=UTF-8 fieldDelimiter=" fieldSeparator=, lineSeparator=\n writeColumnHeader=true

Examples

Separated file

CREATE TABLE AREA AS
    SELECT * FROM CSVRead('/home/user/area.csv') LIMIT 2;

Answer:

THE_GEOM

ID

POLYGON((-10 109, 90 9, -10 9, -10 109))

1

POLYGON((90 109, 190 9, 90 9, 90 109))

2

Separated file containing the column names on the first line

CREATE TABLE AREA AS
    SELECT * FROM CSVRead('/home/user/area.csv',
                          NULL,
                          'fieldSeparator=;') LIMIT 2;

Answer:

THE_GEOM

ID

POLYGON((-10 109, 90 9, -10 9, -10 109))

1

POLYGON((90 109, 190 9, 90 9, 90 109))

2

Separated file with no column names on the first line

CREATE TABLE AREA AS
    SELECT * FROM CSVRead('/home/user/area.csv',
                          'COLUMN1; COLUMN2',
                          'fieldSeparator=;') LIMIT 2;

Answer:

COLUMN1

COLUMN2

POLYGON((-10 109, 90 9, -10 9, -10 109))

1

POLYGON((90 109, 190 9, 90 9, 90 109))

2

Import a csv file (here centroid.csv) and create a geometric table using coordinates columns (here coord_x and coord_y)

-- centroid.csv
| id | coord_x | coord_y |
|----|---------|---------|
| 1  |    2    |    3    |
| 2  |    4    |    5    |
| 3  |    6    |    7    |

CREATE TABLE POINTS(ID INT PRIMARY KEY,
                    THE_GEOM GEOMETRY) AS
        SELECT ST_MakePoint(coord_x, coord_y) THE_GEOM, id
        FROM CSVREAD('/home/user/centroid.csv');

SELECT * FROM POINTS;

Answer:

ID

THE_GEOM

1

POINT(2 3)

2

POINT(4 5)

3

POINT(6 7)

See also