Import Excel without Formatting

[Davidson919]

Hi,

I am currently encountering an issue where I have excel formulas formatted as dates in excel, which means when I import to Alteryx I am losing the data.

Below are example screenshots using fake data showcasing the issue. On sheet 1 (being imported into alteryx) cell Sheet 1 B2 is referencing Sheet 2 B2 on sheet 2, where both are formatted as dates:

When I import into alteryx:

I am creating a macro to input hundreds of these excels so manually changing the format is in each excel is not an attractive option. I have attached the example Excel to this post. Anyone have any ideas?

Thanks

[mbarone]

"abc" is not a valid date format so Alteryx doesn't know what to do with it, so it coverts it to a string (VString) and uses the 00:00:00 for the value.

When I enter a valid date format in your excel file (sheet 2), "2021-01-01", then Alteryx reads it in fine.

Does the real file contain actual dates on sheet 2?  Have any screen shots you can share?

[Davidson919]

Hi mbarone,

Yes the issue I'm having is that the values are not dates - they are strings. This is an example of one of the strings in the actual excel:

No clue about why they are formatted as dates, but the problem seems pretty consistent in all the excels the client has supplied. Is there a way to ignore the column/cell formatting on import and import all values as "General" without formatting?

[mbarone]

In the spreadsheet you originally attached to this thread, and also in your screenshot, the field in Excel is set to be "Date".  Excel is telling Alteryx that a date is coming in.  Whoever creates the Excel files need to set the field to General or Text.

[Davidson919]

So there is no way of changing the format of this column before import? Or changing the filetype which will remove formatting?

[AntDavis42]

Would say try changing file type to csv. But i can't confirm if that would work as it depend on the structure of the sheet. Else the source file it self needs to be edited

[mbarone]

Not that I know of, no.  You could probably manipulate the XML of the spreadsheet but that's out of my realm and still requires manipulation of the spreadsheet before getting it into Alteryx, or, at best, having Alteryx read the XML, change the XML, and output it as a new (or overwritten) spreadsheet. 

Having the process that creates the spreadsheet output it as a CSV instead of Excel would also solve your problem.

[Davidson919]

Creating an excel macro that loops through the worksheets and copies the necessary data out into a .csv then import the csv's into Alteryx. Not the end of the world.

Thanks for the help!

[mbarone]

Yep, that would work too!

[Bill_Richardson]

Suggestion to Alteryx: Add another option to the Input Data tool when working with Excel files.  Option would be a check box for "Read data as displayed".  If checked, Alteryx would read the data the same way in which it is displayed in Excel rather than assuming the data is really of the type indicated by the Excel formatting of the cell.  Excel is "smart" enough to allow a user to format a cell as a date but then the user can enter a string in the cell (or get string data from a reference formula) and it displays as text and not as an invalid date.  Alteryx needs an option to work the same way.  It's common for cells to be formatted with an option like "Date" but then be used as text.  It's not ideal, and we can play the "users must format their data properly card" all we want, but we will still have to deal with this kind of formatting and Alteryx should support it. 

From my testing of this issue, it seems the issue only surfaces when the formatted cell is getting its value from a reference formula, but a nice "Read data as displayed" option would solve the problem.