Import data from multiple sheets at once

[vishtrack]

Hi Folks,

I have scenario where users have an Excel file which contains about 500+ sheets (different tabs) amd needs to be imported for analysis. How do I achieve this? Thank you in advance.

[jdminton]

@vishtrack This is one of the most frequently asked questions. Here is a guide on how to do this. In essence, if your fields are the same for all sheets, you can use a dynamic input. If they are different (any difference), you will need to use a batch macro.

https://knowledge.alteryx.com/index/s/article/The-Ultimate-Input-Data-Flowchart-1583459854309

[alexnajm]

Here is a good reference point as well: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Importing-an-Excel-file-with-M...

[Raj]

@vishtrack you can use this macro 
Hope this helps.

[vishtrack]

Thank you all for the response.

I tried using the provided workflows changing the file.

I am getting the same error on all "Record#100: Tool #1 'xyz tab name' does not match a sheet or nand range in 'file name xx' or the file is corrupted".

I am able to open and edit the Excel file, I don't think the file is corrupted. Need help here pls. Thank you

[jdminton]

@vishtrack did you import the list of sheet names? It isn't really possible to tell what is wrong without the workflow.

[mzak89]

@vishtrack Make sure that you have indicated the end of the sheet name with the $ sign and wrap the sheet name around a single quote like this:

"My path|||'Sheet Name$'"

Otherwise you will end up with an error if there is a space or other white character in your sheet name

[vishtrack]

@mzak89 Hi- i think you are right, I am getting error only in the ones with leading spaces. Tried cleansing though days cleansing tool, but this is still showing the same error.

I am using 'avoid Excel which has missing sheet in Dynamic input tool' workflow shared in the chat.

Where do I need to add this in the workflow? Thank you

[mzak89 · Accepted solution]

@vishtrack  I can't see the workflow anywhere.

But do not cleanse.

I do not know how your macro is constructed. But if in the macro you use "replace a specific string" feature, instead of cleansing, modify your sheet names like this with formula tool

Replace([Sheet Names], [Sheet Names], "'" + [Sheet Names] + "$'")

[vishtrack]

Works like a charm, thank you.