cancel
Showing results for 
Search instead for 
Did you mean: 

HANA Studio: Load blank values from FlatFiles

BenedictV
Active Contributor
0 Kudos

Hi,

I want to load a flat file using the wizard in HANA. The file I am trying to load has blank values for a lot of fields hence I am getting the error "cannot insert NULL or update to NULL".

The thing is that the table I am loading to is replicated from ECC so all the fields have 'Not Null' flag enabled. I tried using space, escape characters etc but nothing seems to work. Can I do something to load the blanks?

Even though the 'not null' flag is enabled the data replicated from ECC has blanks. Can someone explain?

Thanks,

Benedict

Accepted Solutions (1)

Accepted Solutions (1)

lbreddemann
Active Contributor
0 Kudos

With the NOT NULL flag set for columns, you prevent that NULL values are inserted into the table.

Either you allow NULL values (by unsetting the flag for the respective columns) or you make sure your source data doesn't contain NULL values.

- Lars

BenedictV
Active Contributor
0 Kudos

But Lars, all replicated tables have the 'not null' flag set. Is it a design thing? Can any settings be changed to avoid this? Even though this flag is set the blank values in ECC tables are replicated without any errors as blanks.

lbreddemann
Active Contributor
0 Kudos

You mistake BLANK with NULL here.

Typically ECC tables won't allow NULL values, since ABAP doesn't have a NULL concept.

Instead it uses BLANK/DEFAULT values.

There are many really good reasons for not using NULLs - if this is news for you, you definitively should read up about it. Dealing with NULL values can lead to very annoying and unexpected effects in SQL.

For your data load this means, you would probably have to either place BLANK or respective DEFAULT values into your source files or you would have to perform a staged loading and replace the NULL values in the second step.

- Lars

Former Member
0 Kudos

Lars - not sure if you have seen this but this is a common problem if you export tables from ECC to a flat file and try to load them into a HANA schema which has taken the tables from another HANA side-car environment.

With SLT you don't get this issue.

In this case, you can end up with "null" values in the export, which then cause this problem on the import.

The only solution I know is to use Data Services to do the load rather than flat files, and to do the mappings with a default value.

BenedictV
Active Contributor
0 Kudos

Thanks John, for your inputs. A team mate of mine came up with an idea to load blanks.

We have to replace all null values(,,) with one blank space(, ,) and then when using the import wizard , you have to uncheck the check-box which says "Ignore leading and trailing white space(s) in file".

This would load a blank, which is not the same as null, but it works for our scenario or of course we can replace it with null in a calculated column if needed.

Thanks,

Benedict

Former Member
0 Kudos

Right that makes sense. Just you have to parse the CSV carefully to make sure you don't get unintended side-effects. In practice that can be tough.

henrique_pinto
Active Contributor
0 Kudos

Yes, the problem is that in AS ABAP, the null value for a NUMC field of length X is the 0 digit X times. Of course, that is not null for any DB, including HANA itself.

Answers (0)