This discussion is archived
3 Replies Latest reply: Nov 2, 2010 4:25 PM by JTF85 RSS

How to create a data load file from Excel !!!

806339 Newbie
Currently Being Moderated
Hi All,

I'm new to HFM and would like to load data into an HFM application. As I have an Excel file with all the data. When I'm directly loading the data it throws an error saying "No section has been specified to determine if this is data, description or line item detail". How can I convert this excel file into proper format (.dat) file understandable by HFM ?
  • 1. Re: How to create a data load file from Excel !!!
    Erich Ranz Pro
    Currently Being Moderated
    There are several ways to get this data into HFM.

    1) FDM - best option if you have it
    2) Webforms/Data Grids
    3) HsSetValue formulas in Excel
    4) DAT file loads
    5) JVs, etc

    If you wish to use DAT files created via Excel, you will likely want to use Excel VBA macros to create your DAT file to load. We do this on occasion for special projects and it works quite well. What you can do is set up an Excel file with your data inputs to look however you want, then link your POV members and amounts to another tab (we commonly call this the Export tab and it is set up in an HFM-friendly format).

    Create a macro to write a DAT file to a specified location using data from the Export tab. The DAT file will need to be formatted as below. For a specific sample, you can extract data from your HFM app and see the format.

    !Data
    Scenario;Year;Period;View;Entity;Value;Account;ICP;Custom1;Custom2;Custom3;Custom4;Amount
    Scenario;Year;Period;View;Entity;Value;Account;ICP;Custom1;Custom2;Custom3;Custom4;Amount
    Scenario;Year;Period;View;Entity;Value;Account;ICP;Custom1;Custom2;Custom3;Custom4;Amount
    Scenario;Year;Period;View;Entity;Value;Account;ICP;Custom1;Custom2;Custom3;Custom4;Amount
    Scenario;Year;Period;View;Entity;Value;Account;ICP;Custom1;Custom2;Custom3;Custom4;Amount

    Brush up on Replace, Merge, or Accumulate load options in the HFM Admin and User Guides, then upload your new DAT file.
  • 2. Re: How to create a data load file from Excel !!!
    806339 Newbie
    Currently Being Moderated
    Perfect I have loaded an excel file by specifying in the above format. Thank you very much.
  • 3. Re: How to create a data load file from Excel !!!
    JTF85 Journeyer
    Currently Being Moderated
    When designing these sheets, you may also want to consider the POV of the .dat file, members can be put into the POV and you can also specify multiple periods so that you only load one line covering multiple items in your period. The guide received during Oracle's 9.3 Create and Manage Apps class outlines how to set this up quiite well. In our application this created a huge benefit in diagnosing any errors as the ammount of rows in the data file were significantly reduced.

    --Example

    !Entity=Nevada
    !Scenario=Budget
    !Value=<Entity Currency>
    !View=YTD
    !Period=JAN...MAY
    !Year=2010
    !Column_Order=Account,Custom1,Custom2,Custom3,Custom4,ICP

    !Data
    Acct;C1;C2;C3;C4;ICP;JANAmmount;FebAmmount;MarAmmount;AprAmmount;MayAmmount;

    -Hope you find this helpful!
    JTF

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points