Timedimension while exporting a module in .csv file

[gautamgurwara]

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?

[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]

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]

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]

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]

@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