Data Source Attachment Import "empty and ignored" Inconsistencies

[S A Martin]

We have an XLSX file with multiple tabs that needs to be imported and transformed, but I'm seeing some inconsistencies with how the different tabs are being loaded.

The tabs all use a similar format with columns populated by formulas being colored a light grey.  When we load the data sets, most of them show the empty rows as "empty and ignored" but one lists them all as "inserted".

I made a new version of the "oddity" tab with no formatting or formulas; It only processes and inserts the rows with data.  As soon as the background format is added to the column, it processes and inserts all 999 rows again.

Any insights as to what could be causing this behavior?

[Slava Savitsky]

Based on my experience with data imports, Excel formatting can indeed cause all sorts of issues. So my recommendation is to get rid of as much formatting as possible. In your case, the system seems to be treating formatted cells as non-empty regardless of their actual value.

[S A Martin]

The only formatting applied in my testing was the grey background on the columns that would normally contain formulas.

To ensure the XLSX wasn't corrupted, I built an entirely new one without formatting or formulas on any tabs.  I didn't paste any data into the spreadsheet but manually entered values into each tab as sample data.  I loaded each tab to their respective import tables and each only Processed the number of actual records.

Example: 

Processed: 3, inserts 3, etc...

I added a background color to the columns on each tab where formulas normally exist and loaded them again.  This caused all the rows to be Processed like before, but only the "oddity" inserted the empty records into the import table.

• Oddity: Processed: 999, inserted 999, updates 0, errors 0, empty and ignored 0, ignored errors 0
• All Others: Processed 999, inserted {actual records}, updates 0, errors 0, empty and ignored {999-actual records}, ignored errors 0

[Slava Savitsky]

Does it make any difference if you change the order of the tabs in your Excel file?

[S A Martin]

It did not.  I did try importing into a new table and it worked like the others.  I'm not sure what is going on with the original import table, but it seems to be something in that one.