Timedimension while exporting a module in .csv file

gautamgurwara
gautamgurwara
in Integrations and extensibility

I am facing an issue when trying to export a module in .csv format.

 

I have setup a Time Range that has 2 Periods and have available aggregations set as Total of All.

 

In a Module I have this Time Range (which is in periods - Jan 20/Feb 20/etc...) in column and when I am exporting the module as a .csv file in the .csv file it reads as 20-Jan/20-Feb,etc...

If I click in the .csv file cell it reads as 1/20/2020,  2/20/2020 which is not correct. Even if I copy from .csv to notepad++  it still reads 20-Jan.

 

In the excel export as .xls it works perfectly fine it reads as Jan 20 in .xls format which is correct

 

Had anyone faced this issue before. How did you resolve it or is it a bug in the current Anaplan version?

Tagged:

Answers

  • anirudh
    anirudh
    Hi,

    Yes this is a big pain when reading any kind of data using excel and it is not dependent on Anaplan
    The issue is with Excel auto converting dates on file open. There is no way to avoid this behaviour. To keep the date consistent with the way Anaplan formats it, change your system date
    https://support.microsoft.com/en-gb/help/4026213/windows-how-to-set-your-time-and-time-zone
    This way when you open a CSV in an excel file, the auto converted date will look correct

    Let me know if you need more help

    Regards,
    Anirudh
  • gautamgurwara
    gautamgurwara

    Thank you for the reply.

     

    Have some further questions:

    • Please can you provide more information on the point when you say to change the system date - what to change it to? I am logged in via my company's laptop and it does not allow me to change the Date & Time settings.
    • Also, how do you see Anaplan date format?

    Thanks!

     

  • anirudh
    anirudh
    Would it be possible for you to share some screenshots of the export view as set up in Anaplan and the date column as it appears after you export it
    Then I can answer your questions with more confidence!
  • nathan.fowler
    nathan.fowler

    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. 

  • alexpavel
    alexpavel

    @gautamgurwara This is not an Anaplan issue, it is an Excel behavior (as @anirudh already mentioned)

    In order to see exactly what data has been exported in the CSV file, open the CSV file with Text editor ( like Notepad) in order to see exactly what data has been exported from Anaplan to CSV file. 

     

    You will notice that by opening the CSV file with Notepad (not Excel), the values is maintained the same was in Anaplan: Jan 20/Feb 20...etc.

    Only by opening the same CSV file in Excel, Excel will apply the automatic transformation of the values and data will be transformed. In order to preserve data also in Excel, you can use "Get Data" functionality and specifically define for Time columns to be TEXT type. This way the initial exported value from Anaplan will be maintained. 

     

    Excel will always apply some kind of automatic transformations for the values that Excel can recognize as Date or Number. 

     

    Hope it helps

    Alex

  • iWonder
    iWonder
    edited January 22

    Further to @gautamgurwara comment about the automatic transformation of data in excel, here is an example of how to change the data format on import to text in excel :

    • open excel -> new workbook
    • Go to the Data Tab and select the from CSV/Text button
    image.png
    • Find the file then click the Import button
    • Select Transform Data
    image.png
    • Select the time column -> change to: Data Type: Text (one of the buttons above)
    image.png
    • Select Replace Current in pop up window
    image.png
    • Close & Load
    image.png

    It might look a little different depending on the excel version you have but hopefully should be able to do similar flow! I was trying to find help on this so hopefully it will help others even though it isn't Anaplan specifically…

Categories