Import Excel without Formatting

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

Example Date Issue.xlsx

Input

Macros

Date Time

Accepted answers

All comments

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?

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.

Bill_Richardson

One more note on this. I found that if I use the Excel Legacy driver on the input, Alteryx picks up the values of these cells as displayed. See https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-to-Enable-Microsoft-Excel-Binary-Excel-Legacy-Access-accdb/ta-p/513178 for how to enable the Excel Legacy driver as it is not installed by default with Alteryx currently.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703