Change Data Type of Input Data before Reading

[mrose7]

Hello!  

I am running a workflow that pulls info from multiple files in a folder and consolidates this information into one new Excel file.  The columns are all the same; however, two of the columns are sometimes entered as text and sometimes entered as numbers, so Alteryx is recognizing the data as having "different schemas" in Excel.  My goal is for the workflow to pull all of this info, regardless if they are text or number.  It doesn't seem to do this, as every time I run the workflow it only pulls the data that matches the first file in the folder (text instead of number).  Is there a way to change the input data to all be text before it is read by Alteryx for my output?  I know you can change it once it is input, but my problem lies in that Alteryx isn't pulling all of the data in the first place.

Any help would be greatly appreciated!  Thank you!

[Luke_C]

Hi @mrose7 

There's no way to change it before reading, but the common work around for this is to leverage a batch macro. There's lots of threads on this topic, but the key thing is to make sure to configure it by name/position, not the schema (or else you'll have the same issue):

 

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Batch-Macros-for-Multiple-exce...

[Qiu]

@mrose7 
I checked on my side that the Dynamic Input is a no-go.
As @Luke_C mentioned, we can modify the Output mode of Batch macro and make it work or add one Auto Field tool to alter the data type inside the batch macro.

[OTrieger]

@mrose7 
The simplest way to do it when you design the original workflow put a select tool at the very beginning and set the data type that you want  to have.

If for example there is a field that it set to Double and you want to it will always be Double then in the select tool you can configure this field to be Forced Double, it means that this will always will be Double data type no matter how it comes from the next file. 

So if you set Forced data type in the select tool, you will solve your issue.

[mrose7]

Thank you Luke!  The batch macro worked!  I appreciate your help!

[mrose7]

Hi Qiu!  This is very helpful, thank you!  I am new to Alteryx so I appreciate your help as I continue learning! 

[mrose7]

Hi OTrieger!  Thank you for your helpful reply!

[Qiu]

@mrose7 
Thank you for your feedback and glad to know it helps a bit.

[jirikrecek]

I had mixed results with this. This approach with using a Select tool at the start of the flow works vast majority of times, but where it fails is when there is formatting in Excel - like drop down lists and I noticed this even when there was conditional formatting in Excel, it threw a schema error, even if I was forcing a specific data type in a select tool right off the gate.
The Batch macro is a nuclear option - difficult to set up, but it always works.
It was a life saver for me where I was collecting dozens of files end users filled out and sometimes they would copy paste data into them and mess up the original formatting of the Excel file. I could not prevent user errors like this, so ended up going the batch macro route.

[jirikrecek]

@Qiu There was a beautiful article on this where someone described three independent methods of solving the schema error. I cannot find it, but if I do I flagged this thread and will add it here. It was a real life saver and one of the hardest problems for me to solve in Alteryx. 
Extremely frustrating when you let people "mess about" with your source files! :-(