Load large number of excel files

[sh_mryan]

Hi All,

I have been sent 67 excel files ranging between 4MB to 15MB in size. I have been trying to load the data via DIR and Dynamic Input tool but it appears Alteryx isn't able to cope this is amount of data and gets stuck on the first file. 

Does anyone have a suggestion on how I can load the data ?

Thanks in advance

Matt

[caltang]

You are using a Directory tool, which only gives you a list of files that are contained in a directory and relevant file attributes.

You need to use a Batch Macro / Dynamic Input to call in all 67 files. 

Answer me this:

1. Are your 67 files of the same structure / data frame? Meaning they have the same Column Names, Column Counts, and Data Types?
2. From your 67, do you intend to make it one large data set or do you have something else in mind?

Depending on your answer to No. 1, if your structure / data frame is not the same, then you need a Batch Macro configured to call the appropriate names / position / order. If they are the same, you can explore the Dynamic Input function.

Better still, can you provide us some sample data and at least 4 random files from the 67?

Thanks.

[sh_mryan]

caltang, thanks for responding

Are your 67 files of the same structure / data frame? Meaning they have the same Column Names, Column Counts, and Data Types?

Yes they all have the same structure / data frame

From your 67, do you intend to make it one large data set or do you have something else in mind?

I was thinking of one large data set.

I can't provide sample data due to confidentiality, I have mocked up example of the files look like and the workflow I have been trying to make work.

Note, this workflow examples works because the files are small. When I apply the workflow to the 67 files, Alteryx struggles.

[caltang · Accepted solution]

No worries @sh_mryan 

This is how:

[senthoorenvelummylum]

Hi Caltang ,

Your macro is really great. I am running into a tiny issue and can't figure it out. I have a Field that has dollar values with decimals (think double or float) but when using below macro it automatically rounds the values to either no decimals or 2 decimals. So when I compare the total in Excel directly vs what was loaded in Alteryx its off by a few dollars. 

Is there a way to capture the full value of the decimal values while using this macro you created?

[johnaberham]

I tried with these two, not working: