Importing multiple excel files

All,

I'm importing multiple excel files in a data set. I've found that when I import all files it ignores some saying that they have a different schema than the first one. I bring up the first and one that is rejected and the only difference I see is that the rejected one has several rows of empty data. I've include some sample files that fails with same message. Any idea of something I'm not seeing?

I've tried to include the files but not sure whether they made it or not.

Daily 2018-02-14.xlsx

Input

Accepted answers

david_fetters

I assume you're using the asterisk "*" in the file name to import multiple files from one location? If that's the case, then what @LordNeilLord said is correct. Schema includes field names and data types, which must be identical for the wildcard trick to work on the Input tools.

If the field names are always the same and always exist, I usually create a small batch macro to handle situations like this. Inside the macro use the select node to set a fixed type for each of the fields, so that when they output from the macro they will all have the same type and be unioned together. Then you just use the folder tool to pass all of the file paths into the batch macro and it will go through, open each file, set the types of the fields, and output each file. It only takes a couple of tools, as below.

david_fetters

You can have a look at the Date Time functions in the help manual

Use a formula tool to create a new field to hold the properly formatted date, and set the format to Date or Datetime. For the formula use:

DateTimeParse([YOUR_FIELD],"%m/%d/%Y")

Instead of the field [YOUR_FIELD], put the column containing the date from your original data. This function will take data in a mm/dd/yyyy format and parse it into alteryx's datetime format. If you have multiple date time formats (e.g. some are mm/dd/yyyy, some are mm/dd/yy) it will make it much easier to get them into the same format before loading into Alteryx.

As a note, you do not need a union tool after your batch macro, as the batch macro itself will union the resulting data from all of its batches before outputting it into your data stream. Otherwise you're doing great! Building a batch macro and getting it to run isn't a 100 level course topic.

All comments

jeffv

Trying again to include both files...

Daily 2018-01-29.xlsx

Daily 2018-02-14.xlsx

LordNeilLord

Hey @jeffv

So, it's not just the column headers that need to match it's also the field types that also need to match.

If the first few lines are null then Alteryx will read the field type as "string" but on the next file the field might be populated with a date...so Alteryx reads this field type as "date" hence an data mismatch.

In the example you sent, the one file was completely empty....is that ever the case or was it just this example?

I'm thinking, using your new found Excel SQL knowledge...you could filter out all the null ID's on import?

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

Data Lover

david_fetters

jeffv

@david_fetters,

Thanks for idea of trying a batch macro. I'm still trying to understand batch macros (actually all macros). So with your suggested approach... I pass a list of files and the macro imports the data and changes the datatypes and passes back the data. Does the driver program does something in particular to merge the data from the various macro outputs?

bdholm

I'm new to macros. How do you configure the macro shown so that it reads in multiple files?

arsharsh

Could you upload this macro file please? Thank you!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703