CSV Upload - Help

CommunityMember113484
CommunityMember113484
in Integrations and extensibility

Hi,

I have a CSV file with Time Period, however, this fails to upload into Anaplan.  How do I fix this?

Thanks

 

CommunityMember113484_1-1603466272648.png

 

 

CommunityMember113484_0-1603466257807.png

 

Tagged:

Best Answer

Answers

  • anirudh
    anirudh

    If @KirillKuznetsov 's solution does not work for you, can you open the file in notepad instead and post a screenshot of what the date looks like. Excel sometimes auto formats dates into friendly mm-yy but that may not reflect the underlying date format

  • CommunityMember113484
    CommunityMember113484
    Hi, when I import though, there is no time option. The module is not dimensioned by time, but the line item is time period and month formatted.
  • Alexandru.Rosca
    Alexandru.Rosca
    Try to change your cell format in column C in csv. Right click -> Format Cells -> Custom -> Type: mmm yy -> save your file and load again.
  • AWhitworth
    AWhitworth

    Try importing into a test formatted line item and then converting to a time format within another line item in Anaplan. You can do this for any data type and is good practice for dates and lists, I it particularly useful for dates/ time as Excel can often mess about with the formatting. 

  • ChrisAHeathcote
    ChrisAHeathcote

    @CommunityMember113484 

    If the data is not time formatted then Anaplan will not be able to map it to a time period during the import process. In this situation you need to import the data into a text formatted line item and convert it into a time period using Anaplan functions.

     

    Firstly, you will need to create a flat list containing all the variations of the the month names and allocate a number formatted property to each which indicates the month number ( Jan = 1, Feb = 2 and so on ). You will use this to map the month part of the text to a month number which will feed into the time and period functions.

     

    In you target module create four line items; month list (month list formatted as created above), month number ( number), year number (number) and time period (time period month).

     

    Month list = FINDITEM(LEFT(3),Month List) This will return the list item which corresponds to the first three letters of the line item. This is assuming that all months in your data following this three letter format.

    Month Number = Month list.Month Number[lookup:Month List] This line item allocates a number to the list item as per the property, month number.

    Year Number = VALUE(RIGHT(2)) + 2000 This converts the last two characters to a number and adds 2000 to generate a year value.

    Time Period = PERIOD(DATE(Year Number, Month Number, 1)) This takes all the number formatted line items and converts them to an Anaplan time period.

     

    The sequence above will allow you to import a non time/date formatted line item and convert it to a time formatted line item so that you can use aggregation functions such as SUM. 

     

     

  • jameson4170
    jameson4170

    Thank you for Sharing CSV. Best MIG Welder

  • mhemphill
    mhemphill

    This whole subject is a frustration.

    1. We set up screens to export a template for a module bearing time periods for months (Jan 23) to csv
    2. excel then converts to Jan-23
    3. User populates template
    4. Import Action to Re-import fails as excels (date driven) time period no longer matches Anaplan Time Periods
    5. Jan-23 <> Jan 23

    MMM?YY does not work

    Fake Time on interim modules and loads of frustration

Categories