Issue loading csv data

Contributor

Hi Nico...

Yes, I having a little trouble loading data to Exasol.

We have several data files, txt or csv, the issue is that the first line of every file is different to the rest... i mean, I have a data file, but the first line is the control data with 5 or 6 columns, and from from second row and on, is the data.... I have no problem loading data from second rows on, but i need to read the first row and only the first row of every file, to populate a control table on exasol.

 

Can you help me with this.??

 

Best Regards

Alejandro

6 REPLIES 6

Community Manager
Community Manager

Hey @adiazv so you only want to insert the first row of data? Any you want to ignore the rest of the file?

Does the rest of the file look exactly the same as the first row? 

Sports Enthusiast. Database Guy. Member of Team Exasol.
Having trouble? Just let me know!

Please Give Kudos if you find this post useful and also mark this as Accepted Solution if this is the required solution

Contributor

Hi @exa-Nico yes, I only want to insert into a control table the first row of data file, from the second row and on, go to data table...

The File has the first row different from the rest rows.... like:

1 Row :DATAFILE_01_20201120.csv, 2020-11-20, 5, 0

2 Row: 2020-11-20,"ABCD",123,456,"COUNTRY",13413,1324134,2432435,2345245,2345245,23452435

3 Row: 2020-11-20,"ABCD",123,456,"COUNTRY",13413,1324134,2432435,2345245,2345245,23452435

4 Row: 2020-11-20,"ABCD",123,456,"COUNTRY",13413,1324134,2432435,2345245,2345245,23452435

5 Row: 2020-11-20,"ABCD",123,456,"COUNTRY",13413,1324134,2432435,2345245,2345245,23452435

6 Row: 2020-11-20,"ABCD",123,456,"COUNTRY",13413,1324134,2432435,2345245,2345245,23452435

I can load the data (from row 2 to 6) to data table, but i need to load the row 1 and only the row 1 to a control table..

 

Best Regards

Alejandro

 

 

Community Manager
Community Manager

You could use the "ERRORS INTO" clause in the IMPORT statement to do this. Since the columns are different, for the control table, you can specify to import the file and say that any errors will be put into a table instead of the entire statement failing. This will lead to only one row being inserted - the header - because the rest have too many columns. Here's an example:

CREATE TABLE TEST.CONTROL_TABLE (CSV_NAME VARCHAR(2000), DATE_INSERTED DATE, NUMBER_OF_ROWS INT, EXTRA_COLUMN INT);

CREATE TABLE TEST.DATA_TABLE (C1 DATE, C2 VARCHAR(20000), C3 INT, C4 INT, C5 VARCHAR(20000), C6 INT, C7 INT, C8 INT, C9 INT, C10 INT, C11 INT);


IMPORT INTO TEST.CONTROL_TABLE FROM LOCAL CSV FILE 'C://Temp/test.csv' row separator = 'CRLF' errors into test.error_table replace reject limit unlimited;
IMPORT INTO TEST.DATA_TABLE FROM LOCAL CSV FILE 'C://Temp/test.csv' row separator = 'CRLF' skip=1;

select * from test.control_table;
select * from test.data_table;

The IMPORT documentation gives you more information about how to use it. Basically, you will then perform 2 IMPORTs using the same file, one for the control table, and one for the data. Since you don't care about the errors, you can just delete or ignore the error_table then and all of the entries.

Hope it helps!

Sports Enthusiast. Database Guy. Member of Team Exasol.
Having trouble? Just let me know!

Please Give Kudos if you find this post useful and also mark this as Accepted Solution if this is the required solution

Contributor

@adiazv ,

Can you try something like below.

From File_1.txt, create another file File_1_header.txt by running a shell script, then upload both files separately. 

Thanks,
Sreekanth

 

Contributor

Hi @skarangi , I have already think in that solution, but I think maybe exist another solution directly on Import command... I mean directly on Exasol Commands.

A new question, is there an option to use "IO." LUA commands in Exasol UDF? or Execute and Powershell Script or SH script directly from UDF?

 

Regards

Alejandro 

Contributor

Hi ,Yes from PYTHON UDF this can be achieved. I believe LUA can also be used but never tried before.