Jump to content

Importing Excel files with formulas that return ="" (blanks) in some cells?

Qing Wong

Recommended Posts

I am trying to import Excel files, which have formulas for some calculated columns that return blanks (="") if manually entered data for that row is missing. This is mainly for aesthetics for the users populating the Excel:

image.png.7254140d16194c7edefd2bc771706cc1.png(Note that because rows for those manually populated columns (left 2) are empty, the rightmost calculated column is also left blank.

However, when I try to import this sheet into Spotfire, I find that Spotfire automatically imputes all these blank values with 0. This causes my graphs to be full of data points that are artificially zeros! I don't want to replace 0 with blanks in Spotfire as this would take data that might actually be 0.00 (which is realistically feasible) and remove it.

Is there a way for Spotfire to recognize blank ("") Excel values as NA and not as 0.00 during the import step? It seems like this is baked in during the import.

Link to comment
Share on other sites


I did a quick test and I see the same behavior. That looks like a defect so please open a case with our support team in the Support Portal ( https://support.tibco.com) about it.

For now, the only workaround I can think of is to either simply copy & paste the data into Spotfire or export the data from Excel to e.g. a csv file first, and then import that into Spotfire.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...