H2 database - CSVREAD - skip loading header line of the csv file into db

Question

I am using H2 DB in my java application. I want to load a .csv file to the DB. This file contains column headers as the first line of the file. So while loading the file into the DB through CSVREAD command, H2 is trying to parse the first line also and thus failing.

So how to skip loading the first line. Below the query I am using to load the file to the DB:

"CREATE TABLE TEST (CIRCLE VARCHAR_IGNORECASE(50), MSISDN VARCHAR_IGNORECASE(50), PORT_IN_DATE TIMESTAMP, OPERATOR VARCHAR_IGNORECASE(255), PRODUCT_TYPE VARCHAR_IGNORECASE(255), PORT_ID VARCHAR_IGNORECASE(255)) AS SELECT * FROM CSVREAD('src/test/resources/test.csv', "

The statement in your question is truncated; could you add the missing part? Please also add newlines if possible so it's easier to read. — Thomas Mueller
– Thomas Mueller, Commented Jan 6, 2012 at 20:50

Thomas Mueller · Accepted Answer · 2012-01-06 20:53:38Z

17

The CSVREAD function supports both files with and without column header. If the file contains a column header, then don't supply the column list in the function, for example:

SELECT * FROM CSVREAD('test.csv');
SELECT * FROM CSVREAD('data/test.tsv', null, 'rowSeparator=' || CHAR(9));

and if the file doesn't contains the column header, then supply the column list in the function call, for example:

SELECT * FROM CSVREAD('test2.csv', 'ID|NAME', 'charset=UTF-8 fieldSeparator=|');

answered Jan 6, 2012 at 20:53

Thomas Mueller

50.4k16 gold badges123 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dnivog Over a year ago

For tsv file fieldSeparator should be char(9) instead of rowSeparator, hence SELECT * FROM CSVREAD('data/test.tsv', null, 'fieldSeparator=' || CHAR(9)); is correct for tsv.

Snekse Over a year ago

What does the | mean in that fieldSeparator?

Collectives™ on Stack Overflow

H2 database - CSVREAD - skip loading header line of the csv file into db

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related