Batch Macro grabbing too many rows and columns

[jpatel3868]

 The Batch Macro is grabbing tons of blank fields from the Excel Document. Can someone help?

 

[Qiu]

@jpatel3868 
maybe you can check the Output mode in the Interface Designer.

[apathetichell]

add a data cleanse tool in the batch macro? - I assume this batch macro is just reading in multiple excel sheets - if not perhaps you can give us some idea of what this batch macro is doing?

[afv2688]

Hello @jpatel3868 ,

I would recommend first checking the configuration of the batch macro like @Qiu suggested. If your files have the same number of columns and come with the same structure would recommend to set it to "Configure by Position".

If you keep having the error it might be that the users are recycling an excel file. That means that the file was used on those columns and fields to do some tests maybe or some calculations and the metadata of being used to call it somehow is still there. Try one thing, copy and paste the data to another excel file and see if it still gives you the same problem.

Regards

[danilang · Accepted solution]

hi @jpatel3868 

As @afv2688 stated, the Alteryx Input Data tool reads the active area of the excel sheet.  The active area is defined as the area the has ever had data or formatting applied to it.  If you put a single value in cell Z256 and then press the delete key to remove the value, the active area still extends all the way to cell z256.  Alteryx will return a recordset with 26 columns and 256 rows, even if there is only a value in cell A1.  

To fix this you can 

1. reset the active area in the source excel workbook, by selecting the unused rows, right clicking on the row header and selecting "delete".  Repeat with the unused columns.

2. Add a data cleanse tool in the macro or the main workflow and removing null rows and columns as suggested by @apathetichell  

Dan