Import multiple excel file with specific sheet and labelling each sheet by month

[JingYih_Herbalife]

Hi,

I have inventory forecast and accuracy data for each month. The file names are labelled as such

Each file has multiple sheets, but I only require the data from the sheets with name "Accuracy"

Since each excel file is for a separate month, I want to label the data by month.

Which tool should I use for this? An example of the tools configuration and flow would help greatly.

Thanks and regards,

Jing Yih

[IraWatt]

Hey @JingYih_Herbalife,

Here's two way to do this:

Way One:

Usining a input data tool you can add a '*' asterisk. The asterisk acts as a wild card, the asterisk wild card means any number of characters. The import data tool will import any file which matches the pattern of the starting and ending text plus the asterisk which can be any text. The pattern shown above should collect all the data from your files.

Way Two

Start with a directory tool and configure it too look at the folder containing all your Excel Sheets.

As we just want the Accuracy sheets add the sheet name to the file path as shown above with ||| before the name using a formula tool.

Finally using the dynamic input tool you can pull all the files at once. First configure it by selecting one of the Excel files as a template then select the field full path and change the entire file path option.

You may need one or two extra steps if the columns differ between the files but this is the standard way I would do this.

Any questions or issues please ask :)
HTH!
Ira

[JingYih_Herbalife]

Hi @IraWatt ,

The solution doesn't work. Is it because for each file, the "Accuracy" tab name is show different because of the date behind? Also how do I go about labelling each of the data import by month?

[IraWatt]

@JingYih_Herbalife that's alright using method two you can add the extra sheet details into the formula tool like this:

[JingYih_Herbalife]

@IraWatt is my configuration correct for the dymamic input tool? I am getting an error as per below.

[IraWatt]

@JingYih_Herbalife it looks like you have put back slashes for the sheet information eg. "\\\accuracy" it should be pipes like this: "|||accuracy". Update your formula to this:

[FullPath]+"|||Accuracy"+left(Right([FileName],14),6)

[JingYih_Herbalife]

Alright that works. 

Do you know why some of the files are not read? 

And also based on the browser, the data shown does not identify the data are coming from which month. How do I sort this?

[IraWatt]

Nice now you have the file paths sorted. The dynamic Input tool skips files when the schemas between the files are different eg(different number of columns, different field data types, ect...). You can compare each files fields using the field info tool:

This will tell you which column Names or Type is different.

To solve this easily you can ether use my macro on the gallery: Dynamic Input Allow Different Fields Tool - Alteryx Community 

Or you can make your own Macro to loop though and import each file 

A macro will let you decide how the data is combined as you import it:

There are some great videos on the community explain how you can automate things with macros here: Interactive Lessons - Alteryx Community

[jboyette]

Ira,

Will your Dynamic Input Allow Different Fields tool output the filename?  I am currently using the standard Input tool, which has an option to output the filenames as a field.  From this, I'm parsing to get a date that is embedded in the filename.  The problem I have with the standard Input is that, when I'm using the * wildcard to input multiple Excel files from the same directory, one of them has a column set to V_Wstring instead of V_string, which throws off the input and doesn't import that file.  

I thought I could use this tool, but I can't seem to find out if it can output the filenames of the files.

Jason