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

Input tool bringing in extra null columns from Excel named range

mariahmeehan
7 - Meteor

I am using a named range to pull in data from an .xlsm file and am getting 29 extra null columns named F1 through F29) coming in before my actual columns defined in the named range. The excel file is macro enable but I tried saving it as and .xlsx file and am still have the same issue. Any ideas?

Nullcolumns.PNG

6 REPLIES 6
CharlieS
17 - Castor
17 - Castor

This is something that I see a lot and it's easy to resolve. From what I can tell, if there was every anything in those fields (even if they're blank now) they're flagged as modified and Alteryx will import them.

 

To prevent those empty columns from being pulled into Alteryx, open the sheet in Excel and Delete the columns (not just empty the contents). Select the columns, right click, and delete. The keyboard shortcut is Alt+E, D. 

 

ExcelDeleteColumns.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

mariahmeehan
7 - Meteor

Hi Charlie,

Thanks for the quick response. The reason I'm using a named range is because there is data in the columns to the left that I don't need for this workflow but also can't delete. The named range only refers to the columns I need so I'm not sure why the whole sheet is being pulled in.

CharlieS
17 - Castor
17 - Castor

How is your Name defined in Excel? I've found that Alteyrx doesn't support all of the same specifications that Excel does. For best results, it must be a contiguous group with cell-specific ranges.

 

For example: Sheet1$A:D will return lots of null rows use Sheet1$A1:D100 instead. 

mariahmeehan
7 - Meteor

It is defined like this:

 Capturerange.PNG

Also I noticed that even if the range works. It will not pull in the data that should be there but instead just will pull in the column name and replace the data with Null or 0.CaptureNull.PNG

CharlieS
17 - Castor
17 - Castor

The definition looks good. Try using the Excel Legacy driver instead of the regular Excel. The built-in driver can do some odd things to field types (and therefore values) when importing. 

 

ExcelLegacyDriver.png

 

 

 

 

 

 

 

 

 

 

 

 

 

mariahmeehan
7 - Meteor

That worked! Thank you!

Labels