Date format interpretation for Excel xlsx-files

Question

Dear Community,

I am reading an Excel-file with 228 sheets.

The XLSX-version of Excel has the option to give me a list of all sheets in the file. But the data in the excel-file is not read correctly. Excel makes the date-format dd-mm-yyyy. But Alteryx reads the date as yyyy-mm-dd. This gives a different date. (xx-xx-2010 will be 1905-xx-xx)

I tried to change to een XLS-version of Excel, but then I can't get a list of sheet names. I tried to use the list of sheet names of XLSX on the XLS version, but that doesn't work, because XLSX accepts more different characters.

I can't use the Parse:DateTime because the previous value is forgotten. Alteryx only knows the new wrong date.

Is there a way to:

A. Change the way Alteryx interprets the date-format;

B. Import an XLSX-file without all the formatting information given by excel;

C. Another option?

Benjamin_TheCoder · Answer

Thank you for your reaction @cgoodman3, but I didn't find the solution. I used another script outside Altery to retrieve the information.

I already had the configurations as 'first row contains data', but Alteryx didn't read the information the same way as I saw it in excel.

The use of the formula tool is too late, because it already read the information wrong.

Thank you very much for your time.

cgoodman · Answer

Hi

When you import your Excel file there is a an option in the configuration pane to select ‘first row contains data’. If you select this it will force each field to be read in as a string value.

When you do this how are the date fields displayed? If it looks like a number then it will be easy to convert via a formula to the correct format. Excel converts dates where a number of 1 represents the 1 January 1900, and a 2 is 2 January 1900.

Using the formula

DateTimeAdd('1900-01-01', [NumericDate]-2, "days")

as described in this post - https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Convert-excel-numeric-date-to-date-format/td-p/442558 will conver the numeric value to the expected date.

Hope that helps, and if that’s not your issue can you post and example of your Excel.

Chris