How to input data in XLSB file with more than 255 fields / columns?

[tonyzhu]

The XLSB file I want to use has 360+ columns in but Alteryx can only input 255 columns. 

As we would like to get the full workflow automated via Alteryx, manually converting XLSB to XLSX here is not an option.

Much appreciated if any hints or tips here. Thanks!

[T_Willins · Accepted solution]

Hi @tonyzhu,

This is an issue with .xlsb files, but you should be able to import the worksheet in segments, 255 columns at a time, then combine at the beginning of the workflow.  Attached is a workflow that brings in data from a test .xlsb in three Input Data tools:  columns A:IU (first 255), columns IV-SR (second 255), and then the remainder.  A Record ID tool after each gives the Join Multiple tool a common element to join on.  The Record ID fields can be manually deselected in the Join Multiple tool, but I added a Dynamic Select tool with a formula so I didn't have to look through 500+ fields to find the ones to deselect.  If the workflow errors due to the file being opened when the subsequent Input Data tool starts then a batch macro could be used to import each column group, but additional tools, such as Cross Tab, would be necessary to format the data.

I included the full data set from the .xlsb in the workflow as a Text Input tool for you to test the workflow.

[tonyzhu]

Hi @T_Willins ,

Thanks a lot for your kind help!

A quick question, which version of Alteryx Designer you are using? I tried to update the "3 Table or Query" configuration in the Input Data Tool, i can only get `Sheet1$` in by selection, and cannot edit the value to add the ranges in like `Sheet1$SQ:TA` ... Wondering how you did achieve that? Thanks!

Regards,

Tony 

[T_Willins · Accepted solution]

Hi @tonyzhu,

I am using Alteryx Designer 2020.2.  If you can upgrade, I would recommend it.  However, even in older versions there is a "hack" to specify not only a sheet, but also a range.  Once you have selected the file in the File Import tool, after the .xlsb use the following syntax:  |||SheetName$Range.  So in the example below using Alteryx Designer 2018.4, the Connect to File or Database would be C:\Users\twil24\Desktop\test.xlsb|||Sheet1$A:M    This will bring in Sheet1, Columns A to M.  After you click off of the Connect to File or Database field it will reset the Table or Query to as below.