Forum Discussion

Beeswing77's avatar
Beeswing77
New Contributor III
2 years ago

Excel Template upload - Data Source setup

Hi All,

I'm trying to set up some Excel templates for the uploading Budget and Forecast data.  I'm pretty happy with the Excel Template side, with the named range (XFD),  and specific header formats, etc.

However,  I'm a bit lost on the Data Source set up,  I get that you have to Allow Dynamic Excel Loads set to True, but what about the rest of the set up?  Do I choose Delimited or Fixed file?  It feels like this Data Source section is really for flat files, as it always wants to know the column number.

I've tried importing the Excel into the Data Source in the same way I would for a csv file,  but it just shows up as xml gibberish in the top box.  It definitely feels like I'm missing something.

Data Integration
Import
  • AlexD's avatar
    AlexD
    2 years ago

    Hi,

    I use delimited (but I'm not sure if it matters, tbh). It sounds like you know how to set up a data source for csv, so forget everything you know and do none of it...

    Make a blank data source, ignore what you'd normally do for mapping the columns (you just don't do it). Make sure to set this: Allow Dynamic Excel Loads = True (as you noted). That is it!

    Add this pathetic shadow of a data source to your wf import channel and pretend that you did lot's of hard work on the data source (the truth is that you did all the column mapping in excel already).

    Best,

    Alex

  • NickHymes's avatar
    NickHymes
    New Contributor III
    2 years ago

    Hi Beeswing77,

     

    If you are trying to have that excel template upload automatically to Onestream. You will need to write columns and rows that contain XFsetCell references utilizing the Onestream excel add in. Once that is complete. The data will write to each dimension within Onestream automatically.

     

    Alternatively, if you are uploading this excel sheet via a workflow step. You will need to set up the data source appropriately and any transformations rules that will apply. Is your file broken out by columns? Make it a delimited file to dynamically call upon each column. If not you can select a line of text and map that to each dimension and use bypasses to filter out data you dont need like dates, times, etc.

     

    Hope this helps!

     

    Nick Hymes

    Archetype Consulting

  • ckattookaran's avatar
    ckattookaran
    VIP
    2 years ago

    You don't do anything there. It is just there, all the mapping is done in your EXCEL file.

  • AlexD's avatar
    AlexD
    New Contributor II
    2 years ago

    Hi,

    I use delimited (but I'm not sure if it matters, tbh). It sounds like you know how to set up a data source for csv, so forget everything you know and do none of it...

    Make a blank data source, ignore what you'd normally do for mapping the columns (you just don't do it). Make sure to set this: Allow Dynamic Excel Loads = True (as you noted). That is it!

    Add this pathetic shadow of a data source to your wf import channel and pretend that you did lot's of hard work on the data source (the truth is that you did all the column mapping in excel already).

    Best,

    Alex