Import data with date

New Contributor

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

Message 1 of 10
9 REPLIES 9
Contributor

Re: Import data with date

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

 

 

Message 2 of 10
New Contributor

Re: Import data with date

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.

 

 

Message 3 of 10
Certified Master Anaplanner

Re: Import data with date

HI Tsugumin,

 

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

Try the below mapping.

Photo1.png.jpg

 

~Vignesh

Message 4 of 10
New Contributor

Re: Import data with date

@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

Message 5 of 10
Community Boss

Re: Import data with date

@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

Message 6 of 10
New Contributor

Re: Import data with date

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

Message 7 of 10
Community Boss

Re: Import data with date

@tsugumin ,

 

Can you please take a screenshot of the Mapping Tab?

 

Thanks,

 

Rob

Message 8 of 10
New Contributor

Re: Import data with date

Hi

 

This is mapping tab i set.

V006.PNG

 

Thanks

 

Tsugumi

Message 9 of 10
Community Boss

Re: Import data with date

@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

 

Message 10 of 10