It is possible to import up to 7 years of history from SAGE.

• Option 1 - Send us your .cab file;
• Option 2 - Send us your xls files.

Check the integrity before sending (procedure at the bottom of this article)To import your data from Sage 50 to GEM-CAR, you have two options.

First option

Send your .cab file to your representative, specifying the sysadmin username and password, and we will do the import for you.

First of all, please send us the concordance between your accounts and our main accounts. Just replace the #### with your account chart number.

Note: Do not send your chart of accounts, as it may contain 3 bank accounts. If the first bank account is your US bank account and it is chosen at random, the transactions will be imported into this wrong account. The same thing for GST collected on sales, QST collected on sales, etc. Imagine the mess...

Your #### number (To be created in Sage 50 if none exists)

#### Default bank, usually 1020
#### Current earnings, usually 3150 (Net earnings)
#### Default primary income, usually 4200
#### Main purchase by default, usually 5200
#### Accounts Receivable Customers / Receivable, usually 1200
#### Accounts Payable / Payable, usually 2100
#### Customer discount Early payment and sales discount, usually 4240
#### Supplier discount Early payment and purchase discount, usually 5240
#### GST collected on sales (Payable), usually 2310
#### QST collected on sales (Payable), usually 2340
#### GST paid on purchases (receivable), usually 2315
#### QST paid on purchases (receivable), usually 2345
#### Adjustment account
#### Account for commissions (optional)
#### Accounts for customer acquisition costs (optional)
#### Accounts for variable costs (optional)

We will use this information to match your accounts with GEM-CAR when importing into our internal tool.

Second option

You can also send us the .xls files by email and we will do the import for you.

• Open the Sage 50 software.
• Log in to an administrator account (also called the sysadmin).
• In the navigation bar, click on Reports, then Report Center.
• Click on the Export button above each report and save them in xls format.

Exporting customers

In the left-hand column, click on Customers & Sales then click on Customer List.

In Edit this report, check Include inactive customers. Then, under Report columns, use Custom column settings and click the Add all button.

• Default file name: Clients.xls

Exporting suppliers

Click on Suppliers & Purchasing, then on List of suppliers

In Edit this report, check Include Inactive Suppliers. Then, under Report columns, use Custom column settings and click on the Add all button.

• Default file name: Supply.xls

Export the chart of accounts

Click on Accounts, then on Chart of Accounts.

• Default file name: Plan_Com.xls

Export transactions by year

Click on Financial > All transactions or All journal entries. In Modify this report, choose the current fiscal year (put the end date as far back as possible) and start again for the previous fiscal year and sometimes for the next fiscal year.

Note: due to the links between invoices and receipts in Sage 50, we suggest that you only import data for the current year, and keep your records from previous years in Sage 50.

• Default file name (add a year to differentiate between the two): AllJrnl.xls or AllTrans.xls

Export the trial balance (Balance)

Click on Financial > Trial Balance (Standard). Under Edit this report, select the earliest possible date.

• Default file name: Balance.xls.

Export customer transactions

Click on Customers & Sales > Customer Timeline Summary. In Edit this report, select the earliest possible date.

• Default file name: ClientSom.xls

Export supplier transactions

Click on Suppliers & Purchasing > Supplier Timeline Summary. Under Edit this report, select the earliest possible date.

• Default file name: FourSom.xls.

Export customer account ages

Click on Customers & Sales > Customer Timeline Details. Under Edit this report, select the earliest possible date.

• Default file name: ClientDT.xls or ClArrDtl.xls

Export the age of supplier accounts

Click on Suppliers & Purchasing > Supplier Timeline Detail or Supplier Timeline Detail Delay. Under Edit this report, select the earliest possible date.

• Default file name: FournDet.xls or FrArrDtl.xls.

Export stocks and services

Click on Stocks and services > List of stocks and services. Under Edit this report, check Include inactive items. Then under Report Columns, use Custom Column Settings and click the Add All button.

• Default file name: Stocks.xls

Check the integrity of the files

Follow the instructions below:

1. Does the check weighter live up to its name? (It must swing)

Open the Balance.xls file and make sure that the total of the debit and credit columns are identical to the penny.

2. The summary and detailed customer and the summary and detailed supplier have the same totals.
Compare the ClientSom.xls and ClientDt.xls files. The totals in the "Total unpaid:" columns should match. The same goes for FourSom.xls and FournDet.xls.

3. In the file Balance.xls, identify the accounts "Account Receivable Customers / Receivable, Usually 1200" and "Account Payable Suppliers / Payable, Usually 2100" as determined in step 1 "Please fill in the main accounts".
The Total Unpaid CustomerSom.xls and FourSom.xls columns must have the same amounts in the customer and vendor accounts as in the trial balance.
Note: If any of these criteria are not met, we cannot guarantee a smooth import into GEM-BOOKS. We recommend that you ask your external accountant (the one helping you close the year) to make the necessary adjusting entries. We will be happy to repeat the import of the new point CAB file that you will return to us.

Internal reference

• Clients.xlsProvide.xls
• Plan_com.xls
• AllTrans2021.xls
• Balance.xls
• ClientSom.xls
• FourSom.xls
• ClientDt.xls
• FournDet.xls
• Stocks.xls