community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

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

Atom

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

 

Pulsar

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.

 

image.png

Atom

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

Pulsar

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:

 

image.png

Atom

Thanks for figuring this out.  Much appreciated.

Labels