Error: Date values are showing "?'

[apurvashah]

Hey folks,
Good day!
Getting ‘?’ under Date column after importing the excel to Knime.

[apurvashah]

Original date shows like below;

[takbb]

Hi @apurvashah, welcome to the KNIME community,

Would you be able to upload a sample of the Excel file you are trying to read, and also some screenshots of your Excel Reader configuration?

[apurvashah]

Sales_Data.xlsx (336.9 KB)

[takbb]

Hi @apurvashah , thank you for sharing the file. This is odd, and I’m not quite sure what to suggest at the moment. I can verify that I also see the same as you when I open the file with the Excel Reader,

e.g

But if I open the file in Excel, and simply save it again (I have to confirm leaving “protected view” and enable editing because I’ve downloaded the file), without making any changes, I can then see it correctly in the Excel Reader

e.g.

This is the same in both KNIME 4.7.4 and KNIME 5.1.

This looks to me like there is a slight corruption in the xlsx which gets fixed by re-saving but what I don’t know is how you can detect it apart from by seeing that the dates are “missing”.

[apurvashah]

Hey Thanks Takbb
I have redownloded… and its working…
I just need to know, are you a student or professional>

[takbb]

Hi @apurvashah , glad to hear it’s working for you, though I suspect there’s something lurking here that will bite somebody again, unfortunately.

re your question, I work professionally on data migrations/integrations and other data-analysis/transformation work using a variety of SQL databases; mostly Oracle and more recently Snowflake.

[apurvashah]

would you mind to help me in my assignment?

[takbb]

I’m happy to give guidance in areas I know, if you have specific problems, but I think it would be best that if you have things you don’t understand or can’t quite make work that you post your questions (your own questions, not the assignment!) on the forum showing what you’ve tried so far, so that somebody can give pointers or suggestions without necessarily giving the answers!

It’s a difficult path to tread as we of course want the forum to be helpful and educational but at the same time we don’t want to be over-helping where graded assignments are concerned.

Also I certainly don’t know all of KNIME, and for any given problem there may be somebody else who has the time or greater knowledge who can help.

[ssq]

This was and interesting problem, so I downloaded the excel and checked.

On my Mac, I got the same error as described above, but then I looked at the Excel file with Preview (select the file and press space).

The result was this:

So I believe the file is perfectly OK, but saved on with a Chinese locale. Take the first row’s order date (AD2018年2月24日) which means 2018 ‘year’ 2 ‘month’ 24 ‘day’.

Actually opening with Excel or Numbers on the Mac shows the Chinese-formatted dates perfectly OK.

That’s why resave works: it simply (?) resets the excel file’s locale. Or something like that

Cheers,
Sam

[takbb]

Thanks @ssq , that’s really useful.

When you said you looked at it with preview, is that preview within Mac’s file viewer, rather than the Excel Reader?

I’d briefly wondered about different locales but on Windows 10 I didn’t see anything to indicate where the problem was. I tried coding a Java snippet with Apache POI to read a date from a cell and it didn’t show anything wrong as it simply returned the excel serial date number as expected. I didn’t try previewing in Windows Explorer though.

I’ll have another dig around later to see if Windows 10 would give any clues now I know where to look!

[ssq]

The quick view with the Mac’s Finder (“file explorer” in windows) accessed with just selecting the file and pressing SPACE.

But note that that could be seen opening the file as well, both with Excel and Apple’s Numbers.

I’m not surprised that reading the cell directly gave the serial date number – because that is exactly what it should do, and it’s correct.

Cheers,
Sam

[takbb]

Thanks @ssq
From what I can see on Windows 10, there are no clues like the ones you see on the Mac. Certainly not on mine anyway. File preview in Windows Explorer shows this:

but the Excel Reader shows this:

and I haven’t found anything in my copy of Excel that will tell me the stored locale of the dates, as they just automatically appear in my locale. On the “format cells” dialog it just shows me my locale too, with nothing to suggest the sheet is in actually in any other locale

In KNIME, the Excel Reader itself doesn’t allow us to specify the locale of the xlsx, so it feels like this is actually a bug in the Reader not being able to interpret different locales correctly, but if that were the case then surely there would be more noise about this on the forum.

In summary, currently on my setup, I don’t see a way of programmatically identifying there is even a problem apart from the appearance of the missing values, and even if identified there doesn’t appear currently to be a way to resolve it outside of opening in Excel and re-saving. I’m therefore tagging this thread as a bug.

Bug summary - steps to reproduce

Download the file “Sales_Data.xlsx” from here:

Error: Date values are showing "?'

Sales_Data.xlsx (336.9 KB)

Open it in Excel Reader in KNIME AP 4.7 or 5.1 on Windows 10, (in my environment, the locale is UK English, but other locales which aren’t the locale of the xlsx (Chinese?) such as US will probably exhibit same problem)

The ORDERDATE column will appear with missing values:

Expected result : the ORDERDATE should contain the correctly interpreted date values from the xlsx.

Open the file in Excel. The ORDERDATE column is populated with dates

Do not change make any changes in Excel, but instead simply re-save it (you may need to “enable editing” to do this)

Close the Excel Reader config and then re-open the config for the the newly saved xlsx file in the Excel Reader. The dates are now available in KNIME.