CODEWITHSUNDEEP
h2
SenSur
SenSur

Reputation: 1

H2-Database CommandCentre: CSVREAD skips loading the first(!) csv-Line of Data

folks,

H2 skips/drops the FIRST line of the following csv-Dataset ... and I couldn't find a solution or workaround. I have already looked through the various H2-tutorials and of course skimmed the internet ...

Am I the only one (newbie - my "home" is the IBM-Mainframe) who has such a problem inserting into a H2-database by using CSVREAD?

I expected here in this example the CSVREAD-Utility to insert 5(five!) lines into the created table "VL01T098".

!!! there is no "Column-Header-Line" in the csv-dataset - I get the data this way only !!!

AJ52B1;999;2013-01-04;2014-03-01;03Z;A
AJ52C1;777;2012-09-03;2012-08-19;03Z; 
AJ52B1;;2013-01-04;2014-03-01;;X
AJ52B1;321;2014-05-12;;03Z;Y
AJ52B1;999;;2014-03-01;03Z;Z

And here is my SQL (from the H2-joboutput):

DROP TABLE IF EXISTS VL01T098;

Update count: 0 (0 ms)

    CREATE TABLE VL01T098 (
                 MODELL        CHAR(6)
               , FZG_STAT      CHAR(3)
               , ABGABE_DATUM  DATE
               , VERSAND_DATUM DATE
               , FZG_GRUPPE    CHAR(3)
               , AV_KZ         CHAR(1))
    AS SELECT * FROM
                CSVREAD
               ('D:\VL01D_Test\LOAD-csv\T098.csv', 
                 null, 
                'charset=UTF-8 fieldSeparator=; lineComment=#');
    COMMIT;
    select count(*) from VL01T098;

    select * from VL01T098;
MODELL  FZG_STAT ABGABE_DATUM   VERSAND_DATUM   FZG_GRUPPE  AV_KZ
AJ52C1  777      2012-09-03     2012-08-19      03Z         null
AJ52B1  null     2013-01-04     2014-03-01      null        X
AJ52B1  321      2014-05-12     null            03Z         Y
AJ52B1  999      null           2014-03-01      03Z         Z

(4 rows, 0 ms)

? Where is just the first csv-line gone ... and why is it lost?

Could you please help a H2-newbie ... with some IBM-DB2-experience

Many thanks in advance

Achim

Upvotes: 0

Views: 963

Answers (1)

Thomas Mueller
Thomas Mueller

Reputation: 50097

You didn't specify a column list in the CSVREAD function. That means the column list is read from the file, as documented:

If the column names are specified (a list of column names separated with the fieldSeparator), those are used, otherwise (or if they are set to NULL) the first line of the file is interpreted as the column names.

Upvotes: 3

Related Questions