Conversion Errors when using Input Data - How to avoid?

[elamp6]

I have a dataset that I'm importing using the Input Data tool. When I run the workflow, I'm hit with thousands of conversion errors (one for every row). It appears to be interpreting a five digit ID number as a "date" rather than a string or numerical data type and this is what is causing the error. Is there any way to eliminate this issue via some setting in the input data tool? I believe it's causing the workflow to run much more slowly than it would otherwise.

[Treyson]

Check the "File Format" option 2 on the options panel.

[elamp6]

@Treyson -

Not sure what I'm supposed to do from there...It is an excel workbook and I am importing it as such.

[danilang]

Hi @Treyson 

It sounds like Alteryx is interpreting the numbers as dates, possibly because of the format within the excel file.   Excel stores it's dates internally as doubles offset from 1899-12-30.  When Alteryx see these, it converts them to dates.  There are a few options to fix this.

1. Try the Microsoft Excel Legacy driver to import your file.  It may interpret the number properly

2. Use this formula to get the original number back

DateTimeDiff([InputDate],"1899-12-30","days")

Dan

[elamp6]

@danilang -

I don't think I was clear enough on the error... the number shows up fine. The error I'm getting is as follows:

I don't have any problems with the data, just with the number of conversion errors and the time it takes alteryx to generate them. I was hoping your solution of using the "legacy" version of excel would still work, but that's not an option as a file format for me!

[danilang]

hi @elamp6 

Is it an .xls file?  Can you post a sample with just the first few rows of data? After scrubbing all confidential data, or course.

Dan

[elamp6]

@danilang -

It is an .xlsx

A few sample rows of data are attached!

[Treyson]

Hey there!

Sorry I wasn't more clear yesterday. @danilang 's explanation is what I was getting at. I just ran this example and wasn't getting any of those errors. It may be because I don't have those records that you are getting errors with. Just to check, can you open up a new workbook and connect to your same file and see if you are still getting the error?

Thank you,

Treyson

[danilang · Accepted solution]

Hi @elamp6 

When I Input your file, I don't get any date fields created at all.  All the fields are either string or double.  Did you create the sample by copy/pasting to a new file?  That may have changed the format.  Can you try creating a copy of the file in Windows and deleting the extra rows from the copy and posting that?

Dan

[elamp6]

Ahhh I think I've found the issue @danilang @Treyson . Treyson, per your suggestion I ran my sample data and had no issues. So I copied and pasted the exact same dataset into a new excel workbook (without changing anything), and everything works perfectly fine, so I think the problem is actually with the original Excel file. Come to think of it, that file always has to be repaired when I open it in Excel too. It looks like the "tax code" field was triggering as a date in Alteryx for some reason because of that and was then turning up null.

Thanks for all the help!