Directory and Dynamic Input Tool for inputting multiple excel files at once

Hi,

I was hoping if some one could tell me an easier way campared to Batch Macros for inputting multiple files using directory and dynamic input tools? And if you could tell me if the files would be appended as output of dynamic input?

I am able to use "text input" tool for inputting multiple sheets of one file but cant use it for multiple files.

Thanks.
Pratik

Developer Tools

Accepted answers

kane_glendenning

Hi Pratik,

I'm not sure that I understand your question entirely. Firstly, for the Dynamic Input tool, you want the inputs all in the same format, and they will be appended. Also, is the reason that you donm't want to use a Batch Macro, because, you would like a simpler solution? I'll go through a couple of the possible scenarios, of course there is many more depending on your volume and different filetypes.

If you want to input multiple files from one directory, then the directory tool will give you a list of the files in a directory. There are a couple of cases from there:
- If all the files are the same format, then the directory tool can just be run into a dynamic input tool. The files will then be unioned coming out of the dynamic input tool.
- If the files are different formats, then you may want to look twice at why you want them all brought in together, if you do want them just unioned, not caring about fields matching, then the wildcard input tool from the CReW Macro Pack will bring in all files using your selected search terms. (This does use a Batch Macro insode this Macro)
- If you are dealing with spreadsheets, all with multiple sheets and the sheet names are the same between files, then a Directory tool, with your text input then (maybe a bit of parsing) appended should provide the input for a Dynamic Input tool.
- If you are dealing with spreadsheets, all with different names of sheets, and different metadata, then the solution would have to be more specific to your case. I advise taking a look at some of Ned's blog posts on wildcard inputs. you can find one of them here and it will link to some of the others.

Kane

All comments

kane_glendenning

petermc129

Hi Kane,

I have a similar use case, but your solutions do not quite work. Let me describe.

I have a several Excel worksheets, stored within a sharepoint library (I can't use the Sharepoint List connector b/c it doesn't play well with libraries). The Excel worksheets have the same structure (same tab names and field names). I have a list of urls stored as a variable.

I am attempting to use the dynamic input tool to grab a single tab from each sheet and append them. I am using an Excel template with the same structure as the sharepoint sheets. The template is stored locally. The problem that I am running into is that the tool is attempting to append the file locations (sharepoint urls) onto the workflow directory, which is also local. I do not understand why this is happening, since I am using the 'Read a List of Data Sources' option to substitute in my url variable with the 'Change Entire File Path' option. In the attached image I have highlighted the sharepoint path (with some blacked out). As you can see it is being appended to the local path.

Does this make sense? Any ideas? I have tried other tools, like the Download connector, also to no avail.

Thanks,

Peter

alteryx error.pdf

_Oum_

Hello,

I have a similar issue, I want to input multiple files with different formats and I want them unioned by field name and not by position.

The directory+dynamic input doesn't work for me, and the wildcard input doesn't solve the problem since I have an output for files with the same format and another one for files with different format (for this output, the columns are not named and I have my headers as rows in the output file).

Could you please advice on what I should use to have my consolidated file?

Thank you

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703