Alteryx Excel Sheet Input with Invalid Characters

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!

Input

Accepted answers

Yupei

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)

All comments

Thableaus

Hi @Yupei

Could you take a screenshot of your workflow?

Are you selecting the option to change the Entire File Path in the Dynamic Input?

Cheers,

Yupei

Screenshots attached.

Looking up this error on the Internet I found that this could also be due to runtime failure.

Maybe checking if the files are opened and restarting the application could solve the issue.

Cheers,

Copy of file attached.

Files are not open during runtime.

If I go in and manually change the sheet name, and have my workflow look for just

[FullPath] + '|||Prep Info Run Data$' I have no errors. The inclusion of the & symbol is throwing the errors.

16-303 - Copy.xls

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167