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!
Solved! Go to Solution.
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.
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
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.
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)