How to Import a Customer List into Sage Accounting from Pastel

This article deals with the topic of how to import a customer list into Sage Accounting. Sage Cloud Accounting is rapidly replacing Pastel Desktop Accounting as the popular choice for small businesses owners to efficiently manage their customers’ details in a cloud environment.

Importing is the quick and easy shortcut of transitioning a business system to Sage Accounting from Pastel with minimum interruptions.

With importing a customer list you will be able to get all your customer’s details, into your Sage Accounting system without manually entering customer details. Whether it is tens, hundreds or thousands of customers, the import process remains the same, only the number of lines that you add increases but this will all be clear in my example in the video to follow. Once you have those customer details in the Sage Accounting system you are good to go and to do business like a Pro.

A customer list contains vital information about your customer and acts as a “master file” from where constant information is drawn from. For example when generating an invoice, you select a particular customer, and that customers’ details are automatically populated to be included in your sales invoice from the information stored in the masterfile. You only need all your customer information in one central place and from this central place all the information filters through the Sage Accounting system when you need it.

When will you need to perform customer data imports? 

• Customer data imports are mostly used when you are migrating from desktop accounting systems like Pastel or Quickbooks Desktop to Sage Online Accounting.
• I have also done customer imports as part of business buy-outs and takeovers where the new entity needed to get their hands on the customer masterfile and start working with it. 

Third party conversion services

The import process revealed in this article is based on a failed attempt of using the “Cloudconvert.co.za” online service to perform the import from Pastel to Sage Cloud Accounting.

The Cloudconvert’s service is good and in normal circumstances their free import option works fine, except for the fact that when Cloudconvert performs a conversion they:

• transfer the data in batch totals. 
• They also post all of the batch contra totals to a “conversion control account”  in your new set of books 

Why is transferring the transactions in batch totals via a control account a problem? 

1. There is no trace to a transaction that you can follow to prove its substance. 
2. It is impossible to identify the flow of a transaction through the accounting system, making it unable to find an error in your accounting records, because the details of the transactions are hidden in the batch totals. 

It is of the utmost importance that you perform certain accounting housekeeping steps first, before you embark on outsourcing your system conversion to a third party like “Cloudconvert.co.za“

These important steps before you import are:

1. Ensure that all your bank accounts balance to bank statements.  
2. Ensure that your customer accounts and supplier accounts are 100% correct.
3. Ensure that your VAT transactions are all correct. 
4. Check that your general ledger accounts (that’s your income and expense categories) are correct. 

If these steps are not attended to, it impossible to trace and find solutions for:

• transaction amounts or
• categorisation errors within summary balances,
• unreconcilable bank balances.   

The erroneous converted company in Sage Accounting has some benefit

In the newly converted company, although much of the financial figures brought over from Pastel Desktop Accounting to this company is doubtful, there is a great deal of information that is helpful. For example the entire customer list of Pastel was brought over to the Sage company and I will use this list. 

I will first have to export the existing customer list and then import that file into a brand new company in Sage Accounting.  

Preparing Your Customer List for Import

As you can imagine a customer file contains a huge amount of information. From the most basic details like names, addresses, contact details to advanced details such as customer tax numbers, credit limits and specific price lists linked to a particular customer.

The best way to perform an import is to use an existing draft, plan, model or a template. By building on a template you can ensure that the data you import meets the requirements of the system. This will prevent any nasty surprises right at the end after you have put in a lot of work. 

The Customer Import Template

Not all the customer lists are in the correct format for direct import and you may have two tweak it first to get it in perfect shape for according to the import template. The import template is the perfect guide for that.

What you need to do is to copy the information from your existing customer database into each column of the Customer Import Template spreadsheet. 

In Sage you can find all your Import templates including the customer details from the Top Navigation Menu under:

• > Company
• > Import Data 
• > Then click on the “Import Data Guide and Templates.” From here you can select the “Customer Import Template” which is an excel document.

Let’s take a closer look at the Customer Import Template Excel document.

You will have to ensure that the customer list that you will be exporting mirrors as close as possible the Customer Import Template. 

The columns in the customer import template.

• Column A: Provides an explanation of how the import spreadsheet works. 
• Column B to Column O are the customers’ names and address details.
• Column P to Column AA are the User Defined Fields and applicable if you use those.
• Column AB to AS are important customer management tools. 

The first row of the spreadsheet contains the headings and an explanation of what data is categorised in each column. 

Both Column A and Row 1 are deleted in the last preparatory step off the import file, after all your columns have been filled in. 

It is not compulsory that all the columns have to be filled-in and contain data. You can leave certain columns empty if you don’t have the data or it is not applicable for your business. The purpose of the first row (also called the Header Row) is to inform you which columns can be left empty.

The most tedious task of this whole process is to collate all the customer information in this format. The rest of the import process is simple. 

Prepare the ideal format of your customer export file with the Customise Grid.

You can access the List of Customers from the top heading menu under:

• > Customers,
• > List; and then,
• > List of Customers.  

At the top right is the Customise Grid and you will be using this grid to match your customers’ details to the Customer Import Template. 

Under the Customise Grid you have all the columns and the columns ticked are the ones selected to be displayed and which will be exported.  

I am going to select additional columns and thereby ensure that all the important information is available for importing. 

Column AB – the customer’s credit limit

Column AG – the customer’s email address 

Column AH – the customer’s web Address.

Column AI – the customer’s tax reference which is the VAT number

Column AK – whether the customer accepts electronic invoices 

Column AL – when Sage will automatically allocate a receipt to the customer’s oldest invoice. 

Column AM – to specify how each customer’s statement will be distributed.

Column AN –  to specify whether the customer is set up to view invoices on the Sage Accounting customer zone.

Column AO – whether the customer is a Cash Sale customer or not.

Column AP – the Sales Rep can be specified per customer back to

Column AR – the default discount percentage. 

Column AS – the customer’s default VAT type 

The last step before you can export your customer’s data is to save the grid.

The list of customers now have much more details, more information and many more columns displayed.

Exporting the customers list. 

There are 2 ways to export your customer data: 

Exporting Method 1.

1. To Company in the top navigation menu,
2. then down to select Export Data,
3. Select Customers To Export and
4. Export Data 

Your customer list is created and downloaded to your local drive.

Exporting Method 2.

The shorter method of exporting in Sage is from your “List of Customers” to select the CSV download icon. 

Comparing the exported list with the template.

When comparing the exported list with the customer import template. You can see in both the:

• Customer name.
• Customer category. 
• Opening balance.
• Opening balance Date. 
• Postal Address lines.
• Delivery Address lines.
• I normally delete the Additional Delivery address columns, because they are not in the template.
• The user defined fields we don’t really need but I tend to keep the text, numeric and data user fields. 
• The credit limit.
• Contact name.
• Telephone number. 
• And it appears that there are still customers with fax numbers.
• Cell number.
• Email Address.
• Web Address.
• Tax Reference numbers.

The next columns contain information about the management of the customer account but I am not going to name each column. You can go through them at your own convenience.   

The last column of spreadsheet is the: “Default VAT type”.  

I am happy with the export file and I can proceed to the next phase.

Accessing the Import Function in Sage Accounting.

The next phase is Importing the customer file to the new Company in Sage.

Sage can handle the import of an enormous quantity of data. To do that after you have logged into the New Sage Company. Go to List of customers which has no information yet. 

Click on Import Customers 

The 4 steps of the import process are displayed. 

1. The first step is Upload your CSV File 
2. The second step is Map Import File Fields
3. Perform the import by going to “Next”.
4. The fourth step is to note the CSV Import Results for any errors. 

Step 1: Upload the customer CSV file.

In Step 1: Click on the Choose File button. Find the customer CSV file and Upload the file. The “My CSV import file contains column headings” is ticked and then click on Next. 

Step 2: Mapping Fields and Data Validation.

In Step 2: You are going to match the Accounting field names with the import file field names thereby assigning each column’s data to the corresponding Accounting field.

There are a number of fields to review but we have already completed most of this work when we compared our export file to the import template and I am not going to check each field again. 

Step 3: Click on “Next” to perform the import.

Step 4: Import errors and post import tasks.

In Step 4: The CSV Import Results is revealed.

With 33 errors and 50 successful imports my import result was not great at all. This gives me the opportunity to show you how to resolve these errors, because it can happen to anyone and I don’t want you to get stuck with errors at this point. 

I want to warn you against something:

DO NOT CLICK ON THE FINISH BUTTON BEFORE YOU HAVE REVIEWED THE IMPORT LOG FILE!

because you cannot go back to view the log errors once you have gone forward by clicking finish. 

Troubleshooting Common Issues

Let’s view the import log file to troubleshoot and fix the import errors. 

There appear to be two types of errors in my example:

1. The default price list does not exist and
2. The sales rep does not exist.

I did not pay attention to these fields when I performed the import. The most common mistake is that you will not be aware of certain fields that were filled in in your export file and thereby not copy them over to the import template, especially if you have hundreds of customers to import.

In the original file exported there is a Price List 1 but in the new Sage company the Price List does not exist. 

Similarly a Sales rep has been added in certain customer rows. 

I can fix this issue by:

1. deleting these fields and redo the import of these customer rows.
   Or
2. I can amend the company’s settings by adding a Price List 1 and a Sales Rep.

You can find the affected customer rows by sorting the data by the Default Prices List and the Sales Rep Columns.

The Default Price List column has 33 entries and this range includes the Sales Rep entries as well.

I can keep these rows for the next import and delete the redundant rows because they have already been imported to Sager successfully. 

This leaves me with the 33 remaining rows that need to be fixed by deleting the entries in the Sales Rep and Default Price columns and saving the file. 

Perform another import in Sage to rectify the errors.

Back in Sage Accounting I can now Finish the previous Import and perform another import.

To recap the Import process in Sage. Start from the:

• Top Navigation Menu, under,
• Company and down the list to
• Import Data. 

Select the newly saved CSV file and upload the file to Sage.

Review the mapping fields. In my example I skipped this step and click on: “Next”. 

Importing to Sage success!

There you have it. The 33 rows were imported successfully and now you have achieved exactly what you wanted within a few minutes without having to manually enter customer details into your Sage Accounting system. 

Hopefully this article can start to alleviate your concerns of migrating to Sage Accounting, because of the amount of assets and data that are captured in your existing accounting system.

I start the importing process with the customer details, because it is a business’ most important asset and something business owners wants done as quickly and painless as possible.

Let’s Recap the key steps involved in importing a customer list into Sage Business Cloud Accounting.

The key steps in the import process are:

1. Export your customer list in Excel of CSV format.
2. Obtain the Import Template from Sage or The Fun Accountant’s website.
3. Copy each applicable column from your exported list into the Sage Import template and save it as a CSV file. Remember to remove Column A and Row 1 of the Import Template file.
4. Upload the file In Sage Accounting.
5. Mapping the Accounting Field names with the Import File’s fields.
6. Import the file.
7. Review the import log for errors and troubleshoot deviations.
8. Repeat Step 4 to Step 7 to fix and validate the remaining data.

The benefits of using Sage Accounting for efficient customer management.

Your business success depends on your ability to manage your customer lists in an efficient manner and to be albe to utilise the available customer data throughout your business system. Sage Business Cloud Accounting do just that by providing a dedicated, all-round, sophisticated, separate, customer function. Best of all it is also available 24/7, from anywhere in the world as well as a Mobile App that goes with it.

I have only scratched the surface of the customer function in Sage Business Cloud Accounting. Stay posted for much more information, tips and tricks to harness the power of your Sage Business System.

Video Recording.

Below is the YouTube link for this video if you want to follow it with the article:

Free Training. 

Visit our Free Training Video Page click on the image below: