Error:inserted value is too large for column in HANA studio

[former_member245927]

Hi ,I am trying to load data through flat file using Hana studio in to my hana server While loading the data I am facing trouble. An error has occured like". inserted value is too large for column"..can some body help how to resolve this

[Former Member]

Hi Chandrakala,

If you did not solve your issue, could you check your data of CSV file, it should be existing greater then column define length of table.

Hope this can help you.

Regards,

Jerry

[Former Member]

Hi Chandrakala,

As per your error message, it's look like column width problem.

Have you created the table earlier or you are creating the table while uploading the data..in a same script.

Please have a look at your table structure and then data within flat file.

Regards,

Debajit

[former_member245927]

Hi Debajit,

Thank you so much for responding to my query.

I have my schema created in the catalog. But I did not create any table earlier. While importing my data from flat file I am creating the table.

As you said its because of column width. So can you suggest me how to resolve this

Regards

kala

[Former Member]

Hi Chandrakala,

From remote, it is very difficult to find out the exact point - where error is generating.

In this case, I recommend the following :

1. Please create small chunk of data in .CSV file (may be for example 50 records) and try to do the load.

2. If you are successful for the above (even few records among 50), then the issue is purely data in .CSV

3. If you are not successful for even only 1-2 records, I would recommend to test the loading with simpler data or some test data loading from .CSV (e.g. EMP ID, EMP NAME, DEPT, SALARY). If that is successful, then you have to look deep into your earlier .CSV file.

Hope it helps....all the best.

Regards,

Debajit

[Former Member]

Hi Chandrakala,

You can split your file to over one file, or you can import data file by SQL.

Please refer to follow link.

http://help.sap.com/hana/html/sql_import_from.html

Regards,

Jerry

[former_member245927]

Hi,

The error is due  to large values in a column. So how can i resove it ...Can you suggest me some way

Regards

kala

[Former Member]

Hi Kala,  Normally, if you cannot delete greater part data, then you need to change column length or type ,  for example change short nvarchar to long nvarchar or change nvarchar to clobber and so on.  Could you tell me how data size imported to certain column? such as data size is 100MB and 1000000 characters.  Hope this can help you?  Regards, Jerry

[former_member245927]

Hi Jerry,

Thanks for your information. It helped me.Currently I defined the column type as nvarchar(1050).Then data loaded perfectly.

But is this is best practice of doing or not. Is there any other preferred data type of do suggest me.

Thanks

kala

[former_member184768]

Hi Kala,

Generally, you are supposed to have the information the source data i.e. the data type and length of the data. In case, it is completed unknown, then it is advisable to consider the data length at the higher side.

Can you please check the maximum length of the data in your column with statement like:

SELECT max(length(my_varchar_column)) from my_table;

Regards,

Ravi

[Former Member]

Hi Kala,

If you want to load ASCII character string to certain column, then you can define varchar column type as well, the load data is UNICODE character string to certain column, then you can define nvarchar column type as well.

Besides, if you want to use text search feature, then you can define shorttext column type.

anyway, the column type depends on your business definition and purpose.

Regarding as column type length, you can define the column data length based on your business data.

or user's requirement, general its length depends on history business data or user's requirement.

You can refer to below link.

http://help.sap.com/hana/SAP_HANA_SQL_Reference_en.pdf

Hope this can help you.

Regards.

Jerry