Failed to import CVS in HANA

[Former Member]

I attempted to import a txt file into a table, which contains BLOB type fields, but it failed. Although the import statement ran successfully, the table was empty. The SQL statement is like below:

create table TEST

(ID int primary  key,

content blob);

IMPORT FROM CVS FILE '/home/XM/TEST.txt' INTO "XM_CN".TEST

WITH THREADS 10 RECORD DELIMITED BY '\n' FIELD DELIMITED BY '\t';

The content of the txt file is:

ID	CONTENT
1	something

If I replace the "something" with "1010", the table will be filled.

And using insert statement to insert record is also OK:

insert into TEST values(2, 'another thing')

Can someone help advice this issue?

Thanks,

Min  Xu

[former_member182302 · Accepted solution]

Is the header line also present in the file?  Also can you share the error you got while trying to load from error log?

Regards,

Krishna Tangudu

[Former Member · Accepted solution]

Yes, the head line also presents in the file. No error, actually succeeded, but the table is empty. It seems that is due to BLOB type column.

[former_member182302 · Accepted solution]

Can you remove the header line and check once?

Also remove the existing error log file and ensure you have write permissions on that file and then use IMPORT

[Former Member · Accepted solution]

I believe nothing to do with the head line, because it is OK with other file. I did a testing as below:

1.Drop original table.

2.Create a new one but changed the column type from BLOB to NCLOB.

3.Use IMPORT FROM statement to import the file.

4.It is successful and all contents are in the table.

So I think "IMPORT FROM" statement doesn't support BLOB type data.

[former_member182302 · Accepted solution]

Hi Min,

I see what you are saying. Jst mentioned about the header in case.

seems like if we have BLOB as the data type it is expecting binary input in the file too like as you mentioned 1010

The below is the error log i got when i tried to load with 'something'.

Tested on : Rev 74

Regards,

Krishna Tangudu

[lbreddemann · Accepted solution]

Spot on Krishna!

It is all about the LOB value encoding that is expected here.

To see what would a proper LOB value look like in an export of the table, I inserted a record (1 ;TEST) via SQL and exported it.

Then it was obvious, as this is how the file looks like:

more data.csv

1,"54455354"

We find that the LOB column is stored in the binary format - visibile as a string, but interpreted as a series of bytes.

Changing this file to use a TAB instead of the comma still works.

And another mystery solved!

There you go, now you know...

- Lars

[Former Member · Accepted solution]

Thanks Krishna and Lars!

So it seems I need to convert the binary format strings in the TXT file into visible strings before using IMPORT FROM statement.

[lbreddemann · Accepted solution]

Well the problem here is: you provide non-binary data and want to feed it into a character column.

If your column would be of data type CLOB instead, your import would work.

Since your data seems to be just characters I recommend reconsidering your data type choice here.

[Former Member · Accepted solution]

Yes, we are also considering that change, i.e. BLOB -> CLOB.

Thanks!