Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Alteryx Excel Sheet Input with Invalid Characters

Yupei
7 - Meteor

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!

13 REPLIES 13
Yupei
7 - Meteor

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
19 - Altair
19 - Altair

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
17 - Castor
17 - Castor

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
7 - Meteor

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)

Labels