Importing Excel file with Subtotals

#1

Hi Team

I am importing an excel file generated by another system. It contains subtotals (“Rolled up”).
Sometimes the files import perfectly, and then every now and then the file skips the subtotals and show zero’s in the subtotal field. I am at a loss - now sure why this occurs.

A CSV will solve that problem, but will then the totals at the end will be missing.
It is financial information we are working with and the team need the totals.

Any suggestions or tests that I can do?
I have no idea why this is happening.

I opened the file, copy and past it into a new file and saved it.
Original file 700kb (File 1)
New file - 810kb (File 2)

If I import file 1 - no subtotals
If I import file 2 - then subtotals show

If I open both file in Excel, the subtotals show

R

#2

If you only need a row with totals as the last row of the CSV table, you can calculate aggregates in a separate table using the “Aggregate” action and then append the aggregates to the main table using the “Append table” action.

If you still want to deal with the finicky Excel file, you can try using the “Excel command” action, the “Re-save workbook” command. It forces Excel to re-save the file. Sometimes, it fixes shortcomings of spreadsheets produced programmatically by 3rd party libraries. “Re-calculate” might also work.

#3

Thanks, Dmtry.

We need to keep the “exported” file as the source of the truth without making any changes to it.
Auditors requirement.

On the source of truth, we then do a few transformations.
It has to be the way Spinifex exports data.

#4

Can you clone the file first? In this case, you will make changes to the clone, not to the original file.

#5

If I open and save the file before importing it, the file size increase and the file import correctly. I am working with one drive. Not sure if that impacts anything. Will use a macro to open and save file and then continue with the process. This is bizarre.

#6

Thanks, Dmitry