Data Import from excel

ArunManickam

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

 

 

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.