Skip to Content
Jan 29, 2016 at 01:23 AM

IMPORT preprocessing for CSV?


I'm seeing some unusual results from the IMPORT CSV command on HANA, with the flat file on the Linux system. Server is SPS09, with HANA Studio (on Windows) at patch 97. A typical import script looks like this:

import from csv file '/home/user/import/xxxx.txt'

into source.xxxx

with field delimited by '\|'

skip first 1 row

error log '/home/user/import/xxxx.errlog'


In particular, the error log messages listing the offending record are shown in a quote-enclosed, comma-delimited format even though the flat file is pipe ('|') delimited and has no framing quotes; this seems to occur only when a null input field is imported into a DECIMAL table column. If the column datatype is changed to NVARCHAR, then any error log messages are shown using the expected format (pipe-delimited, unquoted). Is there a conversion being done to each record before it is imported?

There appears also to be some differences in how the "wizard" import tool (in HANA Studio) and the sql import tool (on the HANA server) function:

* The wizard imports fields containing only 0x00 (ASCII 'null') as NULL, while the HANA import changes these to a single blank (0x20).

* The wizard does not seem to have a way to change the 'optionally enclosed by' character (does it default to """"?)

* The wizard does not allow an arbitrary character to be specified as a field delimiter; the CSV data we are importing use a pipe ("|") character.

* The HANA import does not have a way to set the input character set; any conversion to the character set of the database must be done beforehand.

* The documentation is for the HANA import is unclear as to how special and unprintable characters are encoded; for specials it appears that an escaped format (e.g. '\t' for TAB) or a doubled-character (e.g. entering two ""'s when one is desired).

* When a [ROLLBACK] error appears in the log, the field contents shown appear to be taken from the first occurrence and are not updated on subsequent occurences, even though the input records differ. This makes it difficult to correlate the source record in error.

I'm interested if there is some 'internal' documentation available that would help answer some of these questions or if the community has researched them.

Much thanks in Advance,