Batch Input multiple .xlsb files

[bunzzz]

Good Morning Community!

I have a problem myself and @dcampana2 are trying to solve. We have large quantity .xlsb files that we need to input. The files have different names but they all have the same named tab. Is there a way to input them all? I have tried numerous "tricks" but none have worked thus far.

[GiuseppeC]

Hi @bunzzz,

have all files got the same schema and are they all the in the same directory?

If so, you could just use a wildcard and you'll get the data from all files imported as a single table. See below:

Hope this helps!

Giuseppe

[bunzzz]

I tried the example and put all of them in the same directory but I received this error.

Input Data (1) Error opening table: Microsoft Access Database Engine: 'Alteryx Import$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.\3125 = -537199594

What can I do to get past this?

Typically these files will not be in the same directory either unless I move them myself. Any thoughts on that section as well?

[GiuseppeC]

@bunzzz,

do you have the Microsoft Access driver installed on your machine? You need it to be able to input .xlsb files.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Excel-xlsb-Files-in-2018-1/td-p/159152

Hops this helps!

Giuseppe

[bunzzz]

Yes I have it installed but that has not helped, is there a limit to the amount .xlsb files that can be brought in?

[PeterS]

Hi @bunzzz 

This post on the Community might offer another idea

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Batch-Macro-File-Must-be-Specified/m-p...

[GiuseppeC · Accepted solution]

Hi @bunzzz,

the error that you are getting is due to the fact that the Sheet name is most likely not identical in all files.

I was able to replicate it by creating 3 .xlsb files, 2 containing a sheet named Sheet1 and the the third, SheetA.

There are a few posts and solutions on Community on how to import multiple files with different sheet names.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/using-dynamic-input-on-excel-files-wit...

Finally, to your point of your files not being in the same directory, I'd suggest to replicate this approach to all the different directories you have files in, import multiple files from each, and using the Union tool to stack the data from the multiple connections.

Hope this helps!

Giuseppe