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

[pratik_jangale]

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

[kane_glendenning · Accepted solution]

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

[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

[kane_glendenning]

Hi @petermc129,

This could be caused by a number of things. In order, the things to check would be:

Have a look at the output of the formula tool to make sure that it is not being changed before the dynamic input.

Next is make sure that there is nothing in 'Modify SQL Query'

Test that you can download one of these files via the normal input tool by copy and pasting the link from the output of the Formula tool into an input tool. This will tell you whether the error is before your dynamic input or not.

I can't think of any other reason why the dynamic input tool might be concatenating the path. Can you paste a screenshot of the DI tool?

Kane

[chris_love]

Can you actually use a sharepoint URL like that with Alteryx? It's been a while since I used Sharepoint but I didn't think you could just use the URL to load an Excel sheet.

Does a single workbook work okay in an Input tool?

Chris 

[petermc129]

Thanks for the reply.  I was able to figure it out.  It turns out there are two issues:

1. I had to remove 'https:' from the path.  So, it is looking for the location where the file is stored.

2. I had to use Internet Explorer 10. In my case using IE 11 in 10 emulator mode worked.  I only discovered this after I was getting the error and it was working on a coworker's machine that had IE 10 installed.  This is a very unexpected result, and I would encourage the Alteryx team to investigate this further. It seems strange that it uses IE to resolve paths, a problem that IE 11 doesn't work, and strange (but nice in my case) that emulator mode does work.

Ultimately, my solution was to use the Sharepoint path using 1. above, along with the Input Directory tool.  I then used a macro to iterate over the sheets for each file, then the Union tool to append all of them into a single result set.

Peter

[_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