Excel formulas

[Tathagata Ghose]

Hello,

When I import an Excel file, Flexpro doesn’t correctly read the columns which are formulae (linked to other sheets for example). After import, Flexpro shows the numerical values as “strings” and I can’t plot them. How can I make Flexpro import the values in the cells as numbers?

Thanks in advance
Tatha

[Tathagata Ghose]

Hello,

When I import an Excel file, Flexpro doesn’t correctly read the columns which are formulae (linked to other sheets for example). After import, Flexpro shows the numerical values as “strings” and I can’t plot them. How can I make Flexpro import the values in the cells as numbers?

Thanks in advance
Tatha

[Bernhard Kantz]

Please let us know how you import the Excel file. Do you import it using ODBC or do you use the ‘Import Excel’ command?
What do you understand under “correct”? Do you get formula texts or wrong values?

If you are using the ‘Import Excel’ command (available only if Excel is present on your system) Excel itself is started embedded in FlexPro. It should be able to resolve all links to other sheets as in stand-alone mode.

Normally FlexPro should be able – depending on the type of import you are using (see above) – to figure out the datatype in a correct manner. We could imagine that the decimal point in FlexPro and Excel are different which could lead to the described behaviour. Please check the decimal point settings under Tools/Options/Country Settings to match those of Excel (or your system).

If you think FlexPro behaves not correct please send us a test case to support@weisang.com. Include the following information:
[list]

[Tathagata Ghose]

Thank you. OK, I’ve attached the problem file. You will find that the sheet “Data” contains formulae which are simple links to other sheets.

I use the Right-Click+import command from within the object list and choose this Excel file. It opens and shows correctly. I select say, first 3-4 columns and do “Ctrl-C” and then Right-Click+paste within object list. This is how I import the data. It works on all other spreadsheets I have tried.

This spreadsheet doesn’t work. In fact, I converted all the formulae to values to test and Flexpro still imports these as strings.

My Excel version is Excel 2003 v11.8134 SP2
Flexpro v 7.0.15

What I expect: I expect Flexpro to import the “values” in the Excel cells as numerical data series, not strings.

Pl help.
Tatha

[Tathagata Ghose]

Sorry, also my Windows version is:

XP SP2 and regional settings are United Kingdom.

I changed decimal point setting – no change.

[Bernhard Kantz]

FlexPro needs to determine one data type for all values. This is normally done from the first row/first column of the selected area. Please check if your selection contains strings in the first selection row. Unless you do declare them as titles for the dataset(s) they will cause the target data set to be of type string.

In general you should select the exact area of your data (empty cells are skipped) to get the best results.

Please see also the following topics in the online help:
[list]

Tutorials/For Newcomers/Getting to know FlexPro in just 15 minutes/Importing Excel Data

Managing Data/Excel Workbook and sub-topics
[/list]
Support
support@weisang.com

[Tathagata Ghose]

Hello,

I am specifying 1st row as label row. I hope you were able to open my spreadsheet. If you open my spreadsheet, you will see that the 1st column (after date column) is plain numbers (no formula). Flexpro is importing this column correctly as numerical values. The problem starts from the next column which is a formula – it is getting imported as strings – so are all other formula columns. So, Flexpro itself is deciding to change the data type mid-way into the spreadsheet.

[Tathagata Ghose]

Hello,

I’ve now solved my problem. The error was caused by rows at the bottom of my spreadsheet which contained a formula in anticipation of future data. This formula returns a ‘null string: “” ‘ until there is valid data in the referenced cells for the formula to compute a number.

So, this problem boils down to the fact that Excel shows zero when a forumla references missing values. There doesn’t seem to be a way to suppress this zero output until a valid data is generated. For this reason, we have to use a “” string to suppress the display.

Flexpro reads this “” as a string and the entire column is read as a string. I can’t find a solution within Excel. My suggestion is that, in a future version, Flexpro should allow for some error codes within Excel, e.g. #VALUE, #NAME, “”, #NA etc. and ignore these while reading data.

Thanks
Tatha

[Bernhard Kantz]

In FlexPro there is also the possibility to convert a text dataset (or Excel data link) into a numeric dataset.
Create a FPScript formula which converts the data set:

FloatingPoint64 'data set name'

support@weisang.com

[Tathagata Ghose]

Thanks. This works perfectly. It might still be useful to allow for error codes as in my post above.

[Author]

Posts