Data Import from excel

How do I import an excel data grid with no unique ID and no unique combination of property?

Note: Customer is looking for keeping anaplan as system of records and wish to generate a unique ID within Anaplan, but key thing is it has to be a import/ not insert using dashboard.

Thanks

Arun

Accepted answers

All comments

Ankitjain

As far as i know, Importing data in Anaplan always need Unique code or a combination of Property.

In your case,

Just to load data in Anaplan populate dummy code(eg. 1..100) against every record in Excel and upload it.

Once data is present in anaplan,use #code generated in numbered list to create unique code in a lineitem and replace Dummy code with your derived code by Model to Model import.

You can also combine Model to model action with your data load action in Process.

ArunManickam

Hello @Ankit

Having a serial number / dummy code is not a solution for the customer. They are asking why Anaplan cannot create a unique id during the import when it can create an unique id when we insert using a dashboard.

I believe the issue is our Import process is designed to do Insert/Update at the same time. Many APIs in other applications clearly seperate the Insert action and Update action. Insert action does not require an Unique Id and Update requires a Unique Id.

Thanks

Arun

ben_speight

Imports are designed so they can be safely re-run without silently duplicating data, which could easily cause models to produce wildly erroneous results. It is rare for list items to only be added to, and without a code or property combination composite key that the source of the data recognizes, you have the equivalent of a denormalized "relational" database table. You can dump more items into it, but you cannot determine if those items are already loaded, and loading module data where the list is a dimension is impossible because you cannot uniquely identify items from the source data. And what if an import only partially succeeds?
Every item receives a unique ID, whatever method is used to create it, but that value will have meaning only in the context of that model; if you need to rebuild the model and re-import the data it will likely be different.

Amaya

Hi, @ArunManickam

Here is a quite simple solution.

・Create a numbered list. Numbers of items can be as many as you need. （It won't exceed 1,048,576 because of the excel limitation.）
・Build a model with the list. And prepare line items, same as excel. It would be better to make some list items to catch specific data.
・So let's copy and paste data from the excel.

You might think that I am joking, but why must we always use the import action? I believe copy and paste is the great invention of data integration.
Perhaps, most Anaplanners would not recommend this kind of implementation. Yes, this is a mere imitation of Excel. But many people love excel and its functionality like your customer.

And I assure that your customer can enjoy benefits from this Excel-ish implementation than using Excel. As below.
1) Easy check of the irregular inputs by using list item format
(End users sometimes destroy Excel formula or ignore the dropdown list)
2) Of course, the history functionality of Anaplan
3) Single source of truth

Besides, proper usage of DCA implements the additional functionality as follows.
1) Once data input through copy and paste, no one can change the data.
2) Some users can read limited items only.

It can be a system, especially when the data is critical for the customer.

Think about this implementation.

Regards,
Taichi

ArunManickam

Hello @Amaya

Nice thought. It does not work when the number of cells are more than 10,000.

Anaplan does not allow to paste more than 10,000 cells at a time.

Thanks

Arun

Amaya

Exactly, you are right.

Handling many rows of data, you should recommend your customer to design the list in the proper way with unique code.

Now unexpectedly your customer may like copy from the excel 🙂

It may vary on the use case. Try many.

Good luck,
Taichi

ArunManickam

Thanks Ben. That is indirectly saying that Anaplan cannot act as a source for keeping a system of records. It can only depend on external system for importing the system of records and we cannot and should not be generating a system of records with in Anaplan.

In supply chain planning applications, the planning engine spits out "Planned orders" with unique ID for each planned order. I see this as a limitation.

Thanks

Arun

MagaliP

I agree with the limitation on the number of cells when copying/pasting data. Why not try using the send and refresh functionality in the Excel add-in instead? The limit becomes 1m cells according to the considerations page.

Quick Links

Can you help?

Unanswered questions

Has anyone taken a stab at Master Anaplanner Recertification exam yet?
Just curious :)…
Solution for indent diabled for hierarchical list when the page has more than two axis
Hello fellow Anaplaners, I have a question about the UX for Anaplan for hierarchical list. If I have a list with multiple layers, say P1, P2, P3, and P4, and the workpage had 2 axis, list and line items, in its vertical axis and time in its horizontal axis (see figure below) The list is shown without indentations, and may…
Best way to Architect Financial Statements in Anaplan for Scalability
I have seen two methods used to build financial statements and I am wondering what others have experienced in scalability/maintenance between the two or if there is a better approach. Assume that we have a flat data source for our general ledger within Anaplan. Our line item we are focused on within the source data is the…