How to Import Customer with multiple address & contact

[tlie]

When we import Customer to ERPNext, we have to do three steps:

1. Import Customer
2. Import Address
3. Import Contact

When we have customer list with a lot of sames names like below (for example, John are two different Johns, whereas Jack is the same person)
Name Address Contact
John Street 1 622 123
John Street 2 0812 345 678
Jack Street 3 722 0437
Jack Street 4 0877 123 445

in ERPNext, when we import with same Name,
then the ID will be like this
John
John-1
Jack (there is no Jack-1 because it’s the same person, i only import 1 record)

Then go to Step 2 & Step 3,
If I don’t know what’s the primary key of the names, how can I make street 1 to go to John and street 2 to go to John-1? and both street 3 & street 4 go to Jack?

The same also with Step 3.

If I only have 4 records, we can do it manually. But if we have 2,500+ records, we need to find automated way to do this from the Data Import Tool or maybe MySQL tool like phpmyadmin?
We have some ideas, but stuck how to link it & also how to use phpmyadmin to generate the auto numbers generated by the address title, something also like John, John-1, etc.

Can someone advise with this batch processing?

Thank you in advance.

[Pawan]

You will see below fields in Address and Contact.

Use these to map addresses and contacts to Customer.

[tlie]

Thanks for your reply, Pawan.

I’m aware this can be done when going to each Address & Contact record and link manually.

However, I’d like to do it in batch because we have thousands of records with multiple address / branches.

Can we do it from batch like Data Import Tool or maybe SQL scripts?

[Pawan]

This is available in Data Import Tool as columns. You can do it from there. I have done it many times previously.

[tlie]

I’m wondering how you do the matching.

We can successfully do import, but we’d like to match thousand of address / contact records to the correct customer IDs automatically, not by inputting one by one

[yasserelbana]

Did you find any way to import customer with multiple address and contact
if you found a solution please help men
thanks in advanced

[mwrsa]

So here I am, four years later, and still cannot seem to find the right solution to the issue. We have around 9k addresses to import (customers, suppliers, employees), most companies don’t have a multitude of data, but we have a few that have literally dozens of contacts and addresses linked to them. Are there any recent news in this field, mostly for v.12, but I will probably switch over to v.13 as soon as it’s out, ERPNext won’t go into production use before autumn here, if needs be even in spring 2022!

[Jorge_Devia]

It is the same problem of all ERP, a distinctive code is handled only at a low level. What must be done is to implement a Rest API in a loop, through the contacts and clients are created, they are assigned.

[ebsjbulcher]

@Pawan

This is available in Data Import Tool as columns. You can do it from there. I have done it many times previously.

Are you able to give us any pointers on how you match up imported addresses with contacts?

[EugeneP]

I’ve imported multiple addresses like this :

What order to setup a business on ERPNext

Yip, I’ve experienced the same segregation with Customer/Supplier and separate Address records in v12. Although Supplier/Customer can have their primary Address as part of the record I’ve opted not to do so and rather link it to Address records. The same goes for the primary phone numbers and email address. Conventionally you’d assign these to a Contact at the Supplier/Customer but sometimes the business has a switchboard and something such as info@company.com which is not ordinarily related …

What order to setup a business on ERPNext

Hi @EugeneP I’m afraid didnt catch what does WUI means? Additionally i wld much appreciate if you kindly elaborate on importing ADDRESSES and link them with supplier/customer. Eager for any links that could help me pass this this stage. Thank you in advance.

Hope it helps

[Pawan]

Link DocType and Link Name can be used to map the addresses to the customers using data import tool. Hope this resolves your issue.

[ebsjbulcher]

That was very helpful - thank you @EugeneP! That’s the most help I’ve found on the import process to date! I still have questions though.

In step three, you say:

@EugeneP

3 Once successful, open the spreadsheet again and copy the Supplier (Supplier Name) / Customer (Full Name) name to the Address template’s Link Name (Links) column and make sure you specify either Supplier or Customer in Link Document Type (Links) column. By doing this you’re sure of linking the Supplier/Customer on their name to the Address records. Fill in or copy and paste the rest of the address detail.

I believe you are refering to this section of the Map Columns document:

My confusion stems from two things:

1. I don’t know the Link Name at the time of import, because it is generated by ERPNext during the Contact/Customer/etc. import process. That makes it very time consuming to use when mapping addresses.
2. The Link Title can be inaccurate, because different people can have the same name and hence, have two different entries in ERPNext. Assuming that the Link Title is unique would lead to associating some addresses with the wrong Contact/Customer/etc, and leave other Contact/Customer/etc without an address at all.

Am I missing something?

[bkm]

@ebsjbulcher

My confusion stems from two things:

1. I don’t know the Link Name at the time of import, because it is generated by ERPNext during the Contact/Customer/etc. import process. That makes it very time consuming to use when mapping addresses.
2. The Link Title can be inaccurate, because different people can have the same name and hence, have two different entries in ERPNext. Assuming that the Link Title is unique would lead to associating some addresses with the wrong Contact/Customer/etc, and leave other Contact/Customer/etc without an address at all.

Am I missing something?

Maybe I can help a bit…

The “Link Name” can be set manually. In the instructions given in the @EugeneP post you quoted, he is instructing you to build the “Contacts” and the “Supplier” import tables first. Then use the magic of something like MS Excel or “LibreOffice Calc” to build the “Address” import table using the template you get from the import tool. In the Address template there will be a column called something like “Link Name”

Again, using the magic of one of the spreadsheet programs, populate the “Link Name” column in the Address template with the actual full names of the “Supplier” or “Customer” and save the template.

Then import first the Suppliers, then import the Customers, and finally import the Addresses. The Link Name column should then correlate the addresses to the correct suppliers and contacts.

Doing it this way also makes it very easy to locate the addresses properly in the GUI when your users are attempting to locate and modify any particular address because they can search by exact name.

However, even though all of this works, I do not know if it actually addresses the problem posed by the original posted several years ago when they run into multiple contacts with the exact same name.

Most likely the way to deal with that is to have the import tool report errors and continue with adding records. At the end use the list of errors to manually fix the ones that have the exact same name. I cannot imagine there would be any great number of those. Regardless, this would likely be the best fix.

Hope this helps…

BKM

[ebsjbulcher]

It did help. I was able to run a (mostly) successful test import, and I was even able to import contacts in the same manner. I did run into two problems, however:

1. Some of my contacts have multiple phone numbers and/or email addresses, but there was only field I could use to import the data. Again, not an issue for short lists, but impractical for lists of thousands.
2. Before importing the addresses or contacts, I can’t select a primary address or contact on the customer. Of course, importing the addresses and contacts doesn’t alter the customer record. It sounds like I need a second import after the addresses and contacts are in place to select the primary records on the customer. Is that correct?

Your thoughts?

[EugeneP]

Hello @ebsjbulcher

@ebsjbulcher

Before importing the addresses or contacts, I can’t select a primary address or contact on the customer.

Am I correct in assuming you want to import the customer first, before the address or contact, but at such time also specify which of the succeeding addresses or contacts, although not imported (ie non-existent) as yet, will be the primary address or contact?

If this is what you anticipate, then I suppose you’re right in the sense that you’d have to do a subsequent import after the initial import of both customer and address/contact. However this final import is not for new records but and update. In which case export at least the ID column of the customer and the Customer Primary Address / Primary Address columns. I have never tried this but I presume you’d have to supply the ID of the address in these columns. Furthermore I have no idea as to how these 2 columns differ.

[ebsjbulcher]

Yes, that’s correct. Thanks for the reply!

[MartinF]

Thanks, worked like a charm.

I may add, just in case, to not put the ID for the address as this messes up and gives an, logical, error for duplicate id key or already existing id key.

[meichthys]

For others struggling, here’s some of my notes on this. They need to be cleaned up, but hopefully they help give a starting point for those struggling with this - I know I sure did!

---

In ERPNext, emails, addresses, and phone numbers are separate DocTypes and are not just fields on the Contact document. Because of this, we need to import them separately into ERPNext and link them to the proper contact, customer, etc.

The process is the same for emails, phone numbers, addresses, etc., but let’s take for example the importing of Addresses:

This process assumes that the contact information has just been imported (without the address fields). If you are running this process at a later date, your fields may not line up when copy and pasting.

Process Files

This process will involve 3 files:

• Original Contact Import (Let’s call it OCI)
• Contact Template Export (Let’s call it CTE)
• Address Template (Let’s call it AT)
• Contact Link Import (Let’s call it CLI)

Prepare Data for Import

If you can, it’s best to take a snapshot of your system before doing imports - this makes it easy to roll-back in case of big issues.

1. Download the AT from the ERPNext Import tool.

• To download the template you will need to create an address import using the frappe ‘Data Import’ tool

2. Fill out AT file:
3. For each address field in OCI, copy and paste into the corresponding field in AT.
   1. Tip: hide rows 3 to the second to last row in the OCI file, this way you don’t need to drag down every column to select all the records.
   2. Note: you will need to import multiple times if your OCI has multiple address fields like ‘Address1_street’, ‘Address2_street’, ‘Address3_street’, etc.
   1. If some fields don’t exist, you may need to concatenate certain fields.
4. Check the doctype that you are importing to - and make sure you have populated all required fields.
   1. If ERPNext doesn’t have a field that you need, you can create a new field by customizing the built-in doctype.
   2. If ERPNext requires fields that you don’t have, you can update the field to be an empty string. This isn’t ideal, but does get past the check.
   1. An easy way to do this is to insert a column next to the one containing missing data, add a formula like: =if(LEN(AL2)=0," " , AL2), then fill down, then copy and paste over the original column (as values). Note: The Address doctype requires the “Title” field even though it isn’t indicated as ‘mandatory’. By default, the ‘name’ of the document will be ‘title’+‘address type’
5. Upload AT to a new Address import:
6. All the fields should map automatically since we didn’t modify the AT template headings
7. You will probably get a few errors/warnings after uploading the file. Address the issues until all errors/warnings are gone:
   1. “The following values do not exist for ” - You will either need to add the value or fix your import file to not contain that value
   2. To make it easier to link addresses to contacts, it is best if all your records import successfully, otherwise, mapping addresses to contacts may be a bit trickier.
   1. I found it easier to just roll-back and re-import corrected addresses until i got all address records to import (even if many were blank) (I used - when the field required a value. Then later, i can just filter and delete blank address records. Keeping the ‘blank’ addresses makes the linking easier.
8. Click ‘Start Import’ and hope all records are processed.
9. Now we need to update our ‘Contact’ records to link the addresses to the contacts:
10. Create a new Data Import for ‘Contact’ of type “Update Existing Records”
11. Download the Contact template with all records (This will be CLI)
    1. Select the “ID” and “Link Name (Links)” fields and ‘Export’
    2. If there were some contacts in the system before you imported, ,just remove them from the bottom of the export file.
    3. Duplicate the IDs for as many types of addresses you have to import (I.e. if you have 3 types of addresses, your ids should be duplicated 3 times)
12. Export all the Addresses (all you need is the ID and Address Type fields)
13. Filter by a specific Address Type and then copy the “ID” field from AT (this is the name field in erpnext) and paste into the Link Name (Links) of CLI (Make sure the addresses are aligning with the correct contacts.)
    1. Repeat for each address type. (You will have all the IDs duplicated multiple times - that’s ok)
    2. You can then filter by blank addresses and remove blanks (I filtered where text starts with --) then removed rows, unfiltered and save
14. Sort by contact ID and then add a new column to the beginning of the table and add the following formula to cell A3: =IF(B3=B2,"",B3), then drag down, and also copy cell B2 to A2. Then copy column A and paste as values into column B. What this did is cleared out the ID field where the IDs were duplicates - this allows child records to be imported into the parent record during the same import. If you try to do separate imports for each type of address, you will overwrite the child table each time and end up with only one linked address.
    1. Remove column A after pasting values into column B.
15. Create a new Contact Import, upload CLI, and click Submit - hope it all goes through.

Good luck! - You’ll need it!
Take lots of backups, Try, fail, and repeat - you’ll get it eventually!