Hi everybody,
I am quite new to Knime (less than week), I mainly use to manipulate data and automate reporting and so far I love it.
However I have just noticed a recurrent issue.
One of the main data sources I use provides data in excel spreadsheets, th date format within is as follows:
“dd mmm yyyy” displaying as 27 Mar 2018
when I execute the node I get the following error:
“ERROR Excel Reader (XLS) 0:135 Execute failed: Date for created could not be parsed: 2018-04-09T10:00:10+01:00”
If I change the format in the excel spreadsheet to date ( “dd/mm/yyyy”") the node works just fine. (obviously I want to avoid this)
so the question I hope you guys can help me with:
Is there any way around this?
Any way to force this to be read as “string” so it can parse it?
Many Thanks
Roberto
Hi @RobertoNieto -
I tried to recreate your issue but was unsuccessful. I formatted a series of dates in an XLSX Excel spreadsheet as follows, and the Excel Reader (XLS) node parsed it without error. (I’m using KNIME 3.5.3.) But perhaps I’m not understanding the issue correctly.
Could you post an example file?
Thank you @ScottF,
After doing further testing I realised it does nothing to do with the date format as such.It is something to do with the file and Excel messing around.
If I try to read the original file I get “Could not load file” error, however if I open the file in excel and save it (without changing anything) it works.
So I am assuming Excel automatically changes something.
The original error
“ERROR Excel Reader (XLS) 0:135 Execute failed: Date for created could not be parsed: 2018-04-09T10:00:10+01:00”
comes from a reading multiple files loop where the first one have been saved but some of the others have not.
I am afraid I am not able to provide example file, as contains confidential information. ( and if I open and remove Excel corrects the issue)
Any ideas of what it is happening when I open/save in excel and how get around having to do this?
Again much appreciated the help and many thanks.
Roberto
Can you elaborate a bit more about the type of file that you’re working with? For example, is it XLS, XLSX, or possibly some other format that isn’t native to Excel, but that Excel can read?
Along those lines, do you have any additional information about how the file is being generated - from another software program, or on a particular machine?
Hey @RobertoNieto,
I’d like to extent @ScottF request by a sample file and your workflow so far.
Kind regards,
Patrick.
Hi @Patrick1974 @ScottF
Thanks for the response, apologies for the delay I haven’t had access to the internet over the weekend …
After a bit more testing I realised by saving the file without changing anything in increases its size by 3.
Some info about the file:
- File is .xlsx
- The file comes from a third party software.
I have tried to upload both files & images but I get the following error:
“Sorry, there was an error uploading that file. Please try again.”
I am not sure why it doesn’t allow me, is there any restriction for newbies?
Regards
Roberto
HI Roberto -
I’m not sure why you’re running into errors uploading your files, but please email them to me at scott.fincher@knime.com and I’ll take a look.
3 Likes
It turns out that it’s the file size that prohibits posting here in the forum - the limit is about 3 MB. I was able to reproduce the problem, but it’s not clear to me why it’s happening - I’ve asked one of our developers to weigh in.
I’ll update with some more details later!
1 Like
Hi @RobertoNieto -
I checked with our dev team and it turns out that this problem stems from a bug in Apache AOI. We’re working on a fix, but I don’t have an ETA for that right now.
Thanks for letting us know about the issue!
@RobertoNieto, we are working on a fix now and an update to the parser library seems to fix the problem. However, we need to use a certain read mode (all into memory vs. streaming) – which has certain drawbacks.
We’d like to understand one corner case better and wonder if you can create an excel file that also has the 2nd (or 3rd or … or any but the 1st) column populated?
