Alteryx Excel Sheet Input with Invalid Characters

[Yupei]

I am trying to read in a sheet from an excel with the sheet name "Prep Info & Run Data".

I pass this string to my dynamic input:

R:\Excel.xls|||Prep Info & Run Data$

The error I get back is:

Error: Dynamic Input (8): Error opening table: Microsoft Access Database Engine: 'Prep Info & Run Data$' 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

Error: Dynamic Input (8): Error opening table: Microsoft Access Database Engine: Invalid bracketing of name ' Prep Info & Run Data$'.\3126 = -530252778

Is there anyway around this? Renaming the sheet is out of the question because there are 1300 excels named this way that I need to read in.

Thanks!

[Yupei]

Further updates:

If I create a dummy excel file with & in the tab name I can read it in no problem.

If I use an input tool directly instead of a dynamic input I can read in the .xlsx files no problem.

Using input tool still throws the same error for .xls files.

[danilang]

Hello @Yupei , @Thableaus, @CharlieS 

The problem seems to be that the name of the sheet in the excel file starts with a space ' Prep Info & Run Data' as opposed to  'Prep Info & Run Data'.  I don't know the syntax to specify the sheet name to a dynamic input if it starts with a space and I can't find any info on it either.   Do any of you?  I tried ...\16-303 - Copy.xls||| Prep Info & Run Data, ...\16-303 - Copy.xls|||' Prep Info & Run Data', ...\16-303 - Copy.xls|||" Prep Info & Run Data"

I was able to recreate the issue with an other file that has a sheet name that starts with a space

Edit:  Even after I removed the space, I still wasn't able to open it

Dan

[CharlieS]

EDIT: Removed suggestion because it gave the same error.

I'm testing with the attached file now. It's strange that I can read the named range from that sheet, but not the entire sheet. 

[Yupei · Accepted solution]

The solution was just renaming the excels using Python. After I get rid of the & sign I can read them in normally in Alteryx.

For anyone that stumbles upon this issue at a later time, here is the python code:

import os as os
from xlrd import open_workbook
from xlutils.copy import copy

directory_path = "//Directory/YC/STRIKES/PythonTest/"
start_sheetname = ' Prep Info & Run Data'
final_sheetname = 'Prep Info Run Data'

file_list = os.listdir(directory_path)
fixed_file_list = []
nosheet_file_list = []
locked_file_list = []

for file in file_list:
try:
rb = open_workbook(directory_path + file)
wb = copy(rb)
try:
idx = rb.sheet_names().index(start_sheetname)
wb.get_sheet(idx).name = final_sheetname
wb.save(directory_path + file)
fixed_file_list.append(file)
except:
print ("Sheet does not exist!")
nosheet_file_list.append(file)
except:
print ("------------------FILE IS LOCKED!")
locked_file_list.append(file)

print ("Fixed files: ", fixed_file_list)
print ("No Sheet files: ", nosheet_file_list)
print ("Locked files: ", locked_file_list)