Read Multiple Excel Files with Input Data Tool

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?

Excel Files.png

Input

Accepted answers

Raj

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

Batch Input.yxzp

All comments

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-Excel-Range-into-a-Workflow-1583461464206

Regards

Raj

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

Batch Input.yxzp

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!

Raj

@cpet13 this is solved, right?

cpet13

@raj yes it is. I'm not sure why it posted twice, sorry about that. Thank you for checking.

Here is the link to the thread that it was solved in.

Solved: Re: Read Multiple Excel Files with Input Data Tool - Alteryx Community

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703