Multiple excel sheets with spaces in sheet names

[jnewland1234]

I followed the instructions on the below blog and was able to get most of my sheets to pull in from my excel file. The problem is that it will not pull in my tabs that have spaces and gives me the error message listed in the attachment. Any ideas what I add to the macro to get this to work?

https://www.concentra.co.uk/blog/how-import-multiple-excel-sheets-and-files-different-schemas-altery...

[ivoller]

Hi,

Your macro worked for me using with various worksheet names including/excluding spaces etc. Can you provide a sample of data from Test Database.xlsx so that I can test against the original flow?

Cheers,

Iain

[jnewland1234]

Sorry about that, attached the wrong file. Here is the excel file.

[ivoller · Accepted solution]

Hi,

Looks like the first CrossTab Tool was changing 'special' characters to underscores.

How about this as a replacement?

[jnewland1234]

Can you update in my Tableau template and send back? Or provide me the formula to copy in?

[ivoller]

See below. The formula just replaces FileName with the text up to the delimiter '|||' (they are pipe characters even though they look like slashes). The Select changes the name back to File Name (may not be necessary depending on actions later in the flow)

[jnewland1234]

I don't seem to have an option for a formula that says Split. Also will changing this still allow me to select just the sheets I want out of the database? My original excel file is larger and there is only certain sheets I want to pull in.

[ivoller]

Not sure why you don’t have that function. You could use the Left and find string functions to achieve the same thing or a regular expression.

As far as only allowing a subset of the sheets through, Alteryx has a very powerful filter tool that allows complex custom expressions that can be used to limit the data. Ie if there is some sort of name convention that can identify the sheets that should be used, the filter can be set up to exclude others.

[ivoller]

Split is part of a package I installed. Sorry about that.

You should be able to use something like

Trim(Left([FileName],FindString([FileName], "|||")))

[jnewland1234]

Thank you so much for your help.