importing excel files, same structure, different field type

[markstephens]

hi,

BACKGROUND

im importing a set of 150 excel files from a folder, these are templates that have been completed by others.

i can import about 75 using wildcard on input tool, the other half wont import as they are different schema.

i compared the files that will import and they ALL have no entries in a number of columns where a "Y" might  be used. Using a select tool i can see alteryx is assuming these to be Double field types.

The other 75 or so files that WONT import have a Y in at least one of these columns, and alteryx then assumes that field is a String type.

MY PROBLEM

Ive got the ones that WILL import. I can turn all the empty fields into strings using Select tool.

Ive got a list of the files that WONT import using wildcard input cos at least one of the fields may be a String field but i dont know what field until i open that particular file 

Is there a way of taking this list of files and turning them ALL into String fields so i can then union with the rest?

hope this is clear!

[DavidP · Accepted solution]

There's a couple of things you can do.

1. With the multi-field tool, you can select fields based on type and change their data type. Make sure you uncheck the option to add a prefix to the field name and in the expression part, just put [_CurrentField_].

2. Use a batch macro with an input data tool that allows you to load all the files even if they have different data types. The easiest option here is the wildcard XLSX macro from the CreW macros pack.

[markstephens]

thanks for that

i downloaded the crews macro set, and used that wildcard xlsx macro and worked a treat

Do you know if there is helpfile or description anywhere of the macros in that set?

[DavidP]

Some have descriptions when you just click on them in the palette, like normal tools. I normally just play around with then to figure out what they do. You'll get plenty of support on the Community as well if you have specific questions.