Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
Andy Uttley, Alteryx ACE, makes music with Alteryx | Math + Music

How to check for encoding or formatting issues with Excel worksheets

Alteryx
Alteryx
Created

How to check for encoding or formatting issues with Excel worksheets

 

When using Excel spreadsheets in Alteryx, sometimes there may be difficulty in reading the data. Viewing the spreadsheets in XML format is an excellent way to check for encoding or formatting issues.

 

Prerequisites


Product - Alteryx Designer, Gallery, or Server and an Excel file used in Alteryx workflows or applications

 

Procedure

 

To view in XML, open the Excel worksheets in 7-zip or another application used for .zip files. Use the format C:\folder_path_of_the_workbook\Excel_file_name.xlsx\xl\worksheets. Next, click on the sheet name and the XML for the spreadsheet is shown.

 

EricWe_0-1582298933338.png

 

Notice the type of encoding is at the top. Followed by the formatting schemas, and then formatting for the worksheet. If needed, you can change the extension of the spreadsheet from .xml to .txt and edit the encoding or formatting of a spreadsheet to match a working example, and then change the extension back to .xml.

 

EricWe_1-1582298933386.png


Common Issues

 

If the Excel workbook is created automatically, such as being created by a script, the information needed for Designer to open the spreadsheets may be missing. Excel can sometimes add this information from its templates when opening the spreadsheet, but Designer is not able to add default encoding and formatting if it is missing.

If you come across an issue, it is a good idea to try adding example data directly in Excel and saving. Excel should automatically add the needed encoding and formatting. Afterward, you can compare with the broken file and update it to match the working example.

 

There are other possible reasons why Designer may not be able to open an Excel spreadsheet. However, checking for encoding and formatting issues in XML view will catch many of the problems with opening Excel data in Designer, so it is an excellent place to start.

 

Additional Resources

 

How To Input Excel Files That Have Encoding Issues
Character encoding
Microsoft Office XML formats
Structure of a spreadsheet

search keywords: Error opening connect string: Microsoft Access Database Engine: External table is not in the expected format.\3274 = -328602519

Comments
Alteryx
Alteryx

As a comment on a different thread @MarqueeCrew provides a macro that you might find useful.  It opens the target file in Excel and saves it again.  That may correct the encoding so that Alteryx can read it.