Import Data from Sage50

To import your data from Sage50 into GEM-BOOKS, you have two options.

Send your .cab file—also known as the “Accountant’s Copy”—to your representative, along with the username sysadmin and password, and we will handle the import for you.

First, please send us the reconciliation between your accounts and our main accounts. Simply replace the #### with your chart of accounts number.

Note: Do not send your chart of accounts, as it may contain three bank accounts. If the first bank account is your U.S. bank account and it is selected at random, transactions will be imported into the wrong account. The same applies to GST collected on sales, QST collected on sales, etc.

Your #### (To be created in Sage50 if it does not exist)

#### Default Bank, usually 1020

#### Retained Earnings, usually 3150 (Net Income)

#### Default main revenue account, usually 4200

#### Default main purchase account, usually 5200

#### Accounts Receivable, usually 1200

#### Accounts Payable, usually 2100

#### Customer discount, advance payment, and sales rebates, usually 4240

#### Supplier discount, advance payment, and purchase rebates, 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

#### Commission account (optional)

#### Accounts for customer acquisition costs (optional)

#### Accounts for variable costs (optional)

We will use this information to match your accounts with those in GEM-BOOKS during the import into our internal tool.

You can also send us the .xls files via email, and we will handle the import for you.

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

Export Clients

In the left column, click Customers & Sales, then click 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: Customers.xls

Exporting Suppliers

Click “Suppliers & Purchases,” then click Supplier List

In “Edit this report,” check Include Inactive Suppliers. Then, under Report Columns, use “Custom Column Settings” and click the “Add All” button.

• Default file name: Suppliers.xls

Export the Chart of Accounts

Click on Accounts, then on Chart of Accounts

• Default file name: Chart_of_Accounts.xls

Export Transactions by Year

Click on “Financial”, then “All Transactions” or “All Journal Entries”. In “Edit this report,” select the current fiscal year (set the end date as far in the future as possible) and repeat the process for the previous fiscal year and, if necessary, for the following fiscal year.

Note: Due to the links between invoices and payments in Sage 50, we recommend importing only data from the current fiscal year and keeping your archives from previous fiscal years in Sage 50.

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

Export the Trial Balance (Balance)

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

• Default file name: Balance.xls

Export Client Transactions

Click on Clients & Sales, then Customer Timeline Summary. Under Modify this report, select the earliest possible date.

• Default file name: CustomerSummary.xls

Export Vendor Transactions

Vendors & Purchases, then Vendor Timeline Summary. Under Modify this report, select the earliest possible date.

• Default file name: VendorSum.xls

Export Client Account Aging

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

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

Export Supplier Account Aging

Click Suppliers & Purchases, then Supplier Timeline Details or Supplier Delinquency Timeline Details. Under Modify this report, select the earliest possible date.

Export Inventory and Services

Click Inventory and Services, then Inventory and Services List.

Under Modify this report, check Include inactive items. Then under Report columns, use Custom column settings and click the Add all >> button

• Default file name: Inventories.xls

Verify the Integrity of the Files

Follow these instructions:

1. Does the trial balance live up to its name? (It must balance)

Open the Balance.xls file and ensure that the totals for the debit and credit columns match exactly to the penny.

2. The summary and detailed customer and supplier totals are the same.

Compare the ClientSom.xls and ClientDt.xls files. The totals in the “Total Unpaid:” columns must match. The same applies to SupplierSummary.xls and SupplierDetail.xls.

3. In the Balance.xls file, identify the accounts “Accounts Receivable / Receivable, Usually 1200” and “Accounts Payable / Payable, Usually 2100” as determined in Step 1 “Please enter the main accounts.”

The “Total Unpaid” column in ClientSom.xls and FourSom.xls must have the same amounts in the accounts receivable and accounts payable as in the trial balance.

If you are using a Mac, “Save a copy” of each Excel document in xls format.

The import tool has a time limit for the import task. If the import completes within this time limit, a green panel will tell you what to check after a successful import. If the time limit is exceeded, go to the transactions and “refresh” every minute until your transaction total no longer changes.

Note: If any of these criteria are not met, we cannot guarantee a smooth import into GEM-BOOKS. We recommend asking your external accountant (the one who helps you close the fiscal year) to make the necessary adjusting entries. We will be happy to re-import the new CAB file that you send back to us.

Internal Reference

• Clients.xls
• Suppliers.xls
• SalesPlan.xls
• AllTrans2021.xls
• Balance.xls
• ClientSum.xls
• SupplierSum.xls
• ClientDebt.xls
• SupplierDebt.xls
• Inventory.xls