Read Multiple Excel Files with Input Data Tool

[cpet13]

Hello,

In my workflow I have an input data tool that is setup to bring in all files named a certain way. The issue I am running into is that the tool is skipping over one of the files because it has a different schema than the first file. I have attached a snip of the two different files and their schemas. The only difference I can find is that a column is being brought in as a V_String in one of the files and a V_WString in a different file. Is there a workaround for this?

 

[messi007]

@cpet13,

Please take a look on those link :

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

https://knowledge.alteryx.com/index/s/article/How-To-Import-Multiple-Excel-Sheets-or-a-Specific-Exce... 

Regards

[Raj · Accepted solution]

@cpet13 try using a macro for this.
you can use this attachment.

[cpet13]

@Rajthank you, that macro seems to do what I need, except for one thing. My excel files have multiple sheets, I need to specify a sheet. Each file has one sheet I am looking for, called the same thing in both of the files. I've tried updating the macro to do that, but that doesn't seem to work. Any suggestions?

[Raj]

@cpet13 you wanted to get a specific sheet form all the files?
is this the case.

if i am not wrong you have used a directory tool to pull all the excel files and sheets from a folder and you are trying to read a specific sheet so you have added a filter tool on the file path or file name and then feeding it to the macro.

if not please try this.

[cpet13]

@ra each excel file has more than one sheet, which makes the macro error. From each file, I only need the sheet called "Data".  I am using the directory tool to get the excel files, but it is not returning the sheets in each file. Is there a way to do that?

[Raj]

@cpet13 so use formula tool to add the sheet name to file path as every time sheet is named DATA this will not be a big issue and easily solved.

Try this trick
add a input tool an select any one excel file and the sheet which is required and get full path to understand how this should look and then modify the file path.

[cpet13]

@Raj okay, thank you, I'll try that.

[Raj]

@cpet13 please refer to the latest comment added.
keep me posted.

[cpet13]

@Raj I must have done something wrong when initially setting up the macro. I redownloaded it and set it up, and it is no longer giving me this issue. Thank you!