I have multiple Excel files (that have the same schema) which I am attempting to bring into alteryx using the Input Data tool. The input works as one would expect, but one field is a date field, and it imports the wrong date. I am not sure how to describe this, so pictures are below: This is the table I want to bring into alteryx. I have one file like this for each month for 8 years. Note that the Date column is a link from another worksheet When I bring this into alteryx I get this: I examined the source link from the Excel file and found that the 'Date' was really text, with each date having a leading space. Please note that except for the 'Date', these records have different values. I mention this in case someone suggests that I just import the table below (which by the way imports correctly). I am hoping that there is a way to solve this problem without having to open each of the Excel files. If anyone has an answer, I would greatly appreciate it. Peace

Importing an Excel field that is a 'date' in text format

I have multiple Excel files (that have the same schema) which I am attempting to bring into alteryx using the Input Data tool. The input works as one would expect, but one field is a date field, and it imports the wrong date. I am not sure how to describe this, so pictures are below:

This is the table I want to bring into alteryx. I have one file like this for each month for 8 years. Note that the Date column is a link from another worksheet

Excel file.JPG

When I bring this into alteryx I get this:

Alteryx view.JPG

I examined the source link from the Excel file and found that the 'Date' was really text, with each date having a leading space.

Please note that except for the 'Date', these records have different values. I mention this in case someone suggests that I just import the table below (which by the way imports correctly).

Import into Excel.JPG

I am hoping that there is a way to solve this problem without having to open each of the Excel files.

If anyone has an answer, I would greatly appreciate it. Peace

Input

Accepted answers

danrh

Not sure why this works, but it seems to --- in the Input Data tool, for File Format select 'Microsoft Excel Legacy'. When I do this, it brings in the original text string, which you can then convert to a date:

All comments

danrh

I'm not able to replicate the issue. Could you strip out sensitive data and post an excel file that's having the problem?

If a date is coming in as a text field, you can use the DateTime tool to convert it to a date --- and after some experimenting, it looks like leading white space doesn't affect it.

capoley

Thanks for trying this, here is one of the files.

SRMC Apr 2015 - sample.xlsx

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703