I am facing an issue while importing a excel file(.xls) into hana table

[Former Member]

Hi Everyone,

When I try to import the excel file (spreadsheet) into a HANA table. its not taking date datatype for date column. Herewith i attached screenshots also.

.

Error:

Batch from record 2 to 14541 failed

java.lang.IllegalArgumentException

at java.sql.Date.strict_valueOf(Date.java:174)

at java.sql.Date.valueOf(Date.java:134)

[Amit_Tewatia · Accepted solution]

Hi Rajesh,

With error message "IllegalArgumentException" it seems like issue with your datatype for this date filed. Is it possible to share the excel details, also try to replicate the issue with trace on.

Regards,

Amit T

[Former Member · Accepted solution]

Hi Amit Tewatia,

                 thank for your reply, ya i will send that excel file

[former_member233898]

Hi Rajesh,

Could you please tell me what type of data you are loading to date field in HANA. Date values are in which format in excel?

Regards,

Jyothi

[Former Member]

Hi Amit Tewatia,

i need to load date datatype only. myexcel have given below format. when i import excel file.its automatically taking nvarachar datatype in hana studio.

type	date_time
Order	01-Apr-2014
Order	01-Apr-2014
Order	01-Apr-2014
Order	01-Apr-2014
Order	01-Apr-2014
Order	02-Apr-2014
Order	02-Apr-2014
Order	02-Apr-2014
Order	03-Apr-2014
Order	03-Apr-2014
Order	04-Apr-2014

[Amit_Tewatia]

Hi Rajesh,

Please use "nvarchar" as datatype for your date field while importing your excel in hana studio. you can use date format function to convert this nvarchar to date while consuming this data.

Hope this will help to resolve your issue.

Regards,

Amit T

[Former Member]

Hi Amit Tewatia,

                   colud you please tell how to convert nvarchar to date format. i dont know how to convert.

please give me the script.

[Amit_Tewatia]

sure, please share complete scenario, where you are using this data.

Regards,

Amit T

[Former Member]

Hi Amit Tewatia,

                      already imported date field datatype as nvarachar .i need to create time dimension by (Hour,Min,Sec,Day,Month) in sap Hana Studio. this time attribute values i have to use design studio

[Amit_Tewatia]

Hi Rajesh,

check below url, it shall guide you with date format:

regards,

Amit T

[Former Member]

Hi Amit Tewatia, 

                      i can't share the excel file in this conversion. please tell me how to share that file to you

[Amit_Tewatia]

This message was moderated.

[Amit_Tewatia]

you can direct message me in SCN , if possible.

Regards,

Amit T