FlexPro Forum – Discuss Your Topic!

Excel formulas

Home > Community > General > Excel formulas

Viewing 10 posts - 1 through 10 (of 10 total)
  • Author
    Posts
  • #12632

    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

    #8295

    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

    #9015
    Bernhard KantzBernhard Kantz
    Participant

    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]

  • Excel file (including references) you want to import

  • exact FlexPro version number (7.0.NN)

  • your version of Excel (including service pack information)

  • your Windows version and locale information

  • what do you expect to happen

  • an exact description of what you are doing
    [/list]
    Support
    support@weisang.com

#9016

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

#9017

Sorry, also my Windows version is:

XP SP2 and regional settings are United Kingdom.

I changed decimal point setting – no change.

#9018
Bernhard KantzBernhard Kantz
Participant

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

    #9019

    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.

    #9020

    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

    #9021
    Bernhard KantzBernhard Kantz
    Participant

    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

    #9022

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

    Viewing 10 posts - 1 through 10 (of 10 total)
    • You must be logged in to reply to this topic.