TCM-392: Designer 2021.4.1 Excel XLSX Data Input Tool skips/drops initial columns with no data in first row (Row 1)

[EdP]

Designer 2021.4.1 Excel XLSX Data Input Tool skips initial columns with no data in first row (Row 1)

 

Environment Details

• Alteryx Designer, Server
  • 2021.4.1

Cause

Defect TCM-392

The Data Input Tool skips initial columns when the columns do not have data in the first row.  For example: If there is no data in cell A1 then column A will not be loaded.  If cell B1 has data then it will load and all following columns will load even if they are missing data from the first row.

Resolution

Defect TCM-392 is Fixed in 2021.4.2

Solution A

1. Add data to cell A1.

Solution B

1. Set the File Format option to Microsoft Excel Legacy as described here: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-to-Enable-Microsoft-Excel-Binar...

[Kenda]

The issue seems to be a bit broader than what is initially described above.

Even if you don't have data in cell A1, column A may still load if other rows in this column are populated, but the rows above may not be read in. For example, if cell A3 is the upper leftmost cell populated and you try to bring in the entire sheet, cell A3 will look as though it was cell A1 in Alteryx.

Additionally, if populating cell A1 did help, unfortunately, superfluously opening every workbook and typing in cell A1 is not always an option. 

I also tried changing all of my inputs to Excel Legacy and the issue still persisted. They weren't always the same cells brought in compared to the normal Excel format, but they were still not the exact specified range, either. 

It seems as though the only way to account for this is to look for a cell you know will be populated in the workbook, check where that was read in, and adjust your workflow to rename columns/rows accordingly.