Excel Import parse error

0
Hi I am seeing the following error when I try to parse with the template I created via the [new template by excelfile] option: The following error occured when importing document: fiscal calendar.xlsx java.text.ParseException - Unparseable date: "12/28/14" Can someone advise how I should get around this issue? I have tried several different ways of formatting the data, including converting the dates into yyyy-mm-dd strings, exporting to csv, and then importing with control over the columns so that they don't turn into dates...
asked
3 answers
1

Hi James,

If you trying to import just an attribute that is of type datetime then ensure that the date is formatted ‘mm/dd/yyyy’.

The date should look like 1/12/2020 and not 01/12/2020.

This worked in my cases, and I found that if the date was structured 01/12/2020, even though it was a date, it could not be parsed.

answered
0

James

I have always had success formatting the column as date in Excel (so that it displays 12/28/14 but in reality is an integer representing the serial number of days since january 1, 1900. Note: I learned, after a bit of head scratching, that Mac 2008 and earlier use the number of days since January 1, 1904, so if you tried to import a spreadsheet that originated on an older Mac, you'll see your dates about 4 years different than what you think they should be.

Mike

answered
0

Hello James,

The way i do it: - first define an exta column in the excel spreadsheet to convert the date to a string column: Formula (in dutch version) : =TEKST(CW2;"d-m-jjjj") - define an action in the Excel importer to call a microflow which converts the string column to a date field. define a date field en use: parseDateTime($String, 'd-M-yyyy')

In my case i don't want the time-component and i defined the date-field as NON-localized my conversion was extended to: addDays(trimToDaysUTC(parseDateTime($String, 'd-M-yyyy')),1)

Perhaps it's not the best way but it works for me.

if you convert '01-01-2015' using parseDateTime($String, 'd-M-yyyy'); this results in '31-12-2014:23:00' if you convert '01-01-2015' using addDays(trimToDaysUTC(parseDateTime($String, 'd-M-yyyy')),1); this results in '01-01-2015:00:00'

answered