Import data with date

Hi Anaplan specialist

 

I would like to import the file with the date. However, the file exported describes the date as DD/MM/YYYY.

With the file i tried to import the data into Anaplan and the result was that the data is imported only into the first line.

 

Does anyone have the solution or any idea for this matter?

 

Kind Regards

Best Answer

  • You can create an export/import process within Anaplan. Ideally we always want to plan within Anaplan but I have created export/import process. The date format changes when we export to csv. 

     

    • Export
      • File Type: CSV
      • Layout: Grid
      • Omit Summary
      • Include Empty
      • Action Name: Export [What you want to export] template
      • Process Name: EXPORT: [What you want to export] template
    • Import
      • Action Name: Import [What you want to import]
      • Process Name: IMPORT: [What you want to import] 
      • Time: Periods Y-M

    FYI, you'll probably get a date error when you set up the action. However, once you run the process end to end the data loads with no errors. This is pretty finicky and you may experience some errors. 

Answers

  • Hi,

     

    Anaplan features mapping/ETL support for imported data.  Without knowing the specifics of your file format I've mocked up a simple example. 

     

    Using a basic txt file for import with the date format in question.  In this case row 1 is the header row.  Data starts at row 2.

    2019-03-20_09-51-15.jpg

     

    Now with the module open to the data grid view import the file.

    Import mapping windows will open.  Make sure Header row and first data row are correct as well as separater.  Anaplan is very good about pre-determining the incoming format but its always good to verify.

    2019-03-20_09-52-14.jpg

    1. Hit next to map the data.
    2. Lets focus on time and assuming that all other columns map correctly.
    3. Anaplan has the ability to map various date formats to the anaplan format.
    4. Now map the Source to Target on the Mapping tab and then go to the Time tab.
    5. See illustration below.

    2019-03-20_09-52-38.jpg

     

    2019-03-20_09-54-19.jpg

     

    Go ahead and map the rest of the columns and load the data. You may need to try this a few different ways demanding on the source data format.

     

    Hopefully this provides a few idea on loading data files that contain dates.

     

     

    Regards,

     

    Dave

     

     

  • @tsugumin ,

     

    Was the module you were entering the data dimensionalized  by time?  And if so, what was the granularity of Time (day, month, etc)?  If you did have the module my month, but the data in the file was at the day level, Anaplan will automatically sum the data up to the granularity of the target (again, I am assuming at the month level).

     

    Hope this helps,

     

    Rob

  • Hi Dave

     

    Thanks for your detailed explanation.

    The date format in my file is different from yours.

    So, could you kindly check my circumstance described below?

     

     The module to import the data is as below.Time scale set is "month". Apr 18 means 04/2018.

    V1.PNG

     

    The csv file i want to import is formatted as below. This is the format i just exported from the module above. 

    V2.PNG

     

    In the process of import, i mapped date with format of 'Y-M' according to the month format in the csv file.

    V3.PNG

     

    Then i faced with this issue as below.

    V4.PNG

     

    Would you have any solution to this matter?

    Thanks in advance.

     

     

  • Hi Rob

     

    Thanks for your reply.

    The data i have tried to import is dimensionalised by Time,Month.

    Could you kindly check my explanation with pics above?

     

    KInd Regards

  • @tsugumin ,

     

    Can you please take a screenshot of the Mapping Tab?

     

    Thanks,

     

    Rob

  • HI Tsugumin,

     

    In your CSV Template file, the time format is YY-MMM.

    Try the below mapping.

    Photo1.png.jpg

     

    ~Vignesh

  • Hi

     

    This is mapping tab i set.

    V006.PNG

     

    Thanks

     

    Tsugumi

  • @VIGNESH.M

     

    Thanks for your reply.

    I tried the way you adviced but it did not work in my case.

    This might be due to the date format in the excel file.

     

     V007.PNGV008.PNG

     

     

    V009.PNG

     

    Kind Regards

    Tsugumi

  • @tsugumin ,

     

    Ok, let's try this...I have attempted to recreate exactly what you have, but if not, please correct me.

     

    I have uploaded a csv file which looks likes:2019-03-21_07-50-53.png

     

    Notice, the date is rendered as YY-MMM, but you have to remember this is only a renderning and now exactly how the date is being stored in the file.  If you click within the cell fo a a date, you will see date is actually a true mm/dd/yyyy date.  This might be where your issue is, but I am not sure.2019-03-21_07-55-53.png

     

    When I upload that file to Anaplan, please make sure your headers data rows are defined correctly.  Also, for the time being, please make the Defualt file permissions to Everyone.  This can be changed later, but for testing purposes, set it to Everyone.  Also, on the left side, make sure your Column Separators are correct.

    2019-03-21_07-49-44.png

     

    In the Mapping, please make sure you map the correct columns to the data.  For Time, notice Anaplan is "seeing" the data as YYYY-MM even though the representation is different (YY-MMM).2019-03-21_07-50-25.png

     

    On the Time tab, make sure mapping is similiar to the below:

    2019-03-21_08-01-45.png

     

    The Currency Tab should look like:2019-03-21_08-03-21.png

     

    And the Line Item tab should look like:2019-03-21_08-03-30.png

     

    When your run the action, you should get all green checks2019-03-21_08-05-16.png

     

    Hope this helps,

     

    Rob