Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Conversion Errors when using Input Data - How to avoid?

elamp6
7 - Meteor

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.

10 REPLIES 10
Treyson
13 - Pulsar
13 - Pulsar

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

Treyson Marks
Senior Analytics Engineer
elamp6
7 - Meteor

@Treyson -

 

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

danilang
19 - Altair
19 - Altair

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

 

Legacy.png

 

2. Use this formula to get the original number back

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

 

Dan

elamp6
7 - Meteor

@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:

Capture.PNG

 

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
19 - Altair
19 - Altair

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
7 - Meteor

@danilang -

It is an .xlsx

 

A few sample rows of data are attached!

Treyson
13 - Pulsar
13 - Pulsar

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

Treyson Marks
Senior Analytics Engineer
danilang
19 - Altair
19 - Altair

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
7 - Meteor

 

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!

Labels