- Have you ever had issues getting Spotfire to load data or update data from a spreadsheet?
- Have you ever seen strange behavior in updating a spreadsheet after you have inserted rows?
I ran across an interesting problem this week helping someone troubleshoot a DXP file that wouldn’t update when records were added to a spreadsheet. Now, the spreadsheet that was loaded looked something like the screenshot below. It was a drilling and completions sheet that had records, several blank rows, and then summary values (calculations) at the bottom.
When the spreadsheet was loaded, the user ignored the summary values and loaded only the raw data (as shown below). At first, they just updated the blank records. When those ran out, they inserted rows direct above the summary lines. When they started inserting rows, Spotfire no longer updated the DXP.
After a few tests, I figured out the behavior. At first, I thought it was a defect in the software, but now think this is by design.
So how does it work — If you load a spreadsheet and select ignore rows, Spotfire is remembering the row numbers to be ignored. It is NOT uniquely identifying the rows and remembering the identifier, thus tracking a row as it moves around the spreadsheet. Let’s say you ignore records 9 and 10. If you add two rows above the records to be ignored, it’s still ignoring rows 9 and 10, but rows 9 and 10 are now 11 and 13, which aren’t being ignored. Rather, the records just inserted are being ignored. This is why it looked like the spreadsheet wasn’t updating.
The solution is not to have summary rows as shown in this example.
I think the software is designed this way because they just don’t have a way to track each row throughout a spreadsheet, but that’s just my guess.