Import/Export Tips

Aging Information and Invoice Due Date

If you import a sales or purchase transaction and do not include the Date Due field, Sage 50 will not "calculate" the due date for the invoice. This will affect your accounts receivable and accounts payable aging reports.

Beginning Balances

Beginning balances are stored in the following files:

Beginning Balance Items	Journal File Stored
Chart of Accounts	General Journal
Vendors	Purchase Journal
Customers	Sales Journal
Jobs	Cash Receipts Journal and Cash Disbursements Journal Note: Job beginning balances are stored in these journals as they are entered, but they cannot be exported.
Employees	Payroll Journal

Note: Beginning balances are not exported for inventory items or jobs. Therefore, you must enter these values manually if you are rebuilding or creating a new company.

In addition, Quantity on-Hand is not exported for inventory items because it is a calculated field in Sage 50 . The quantity on-hand is not stored in the inventory item file, and is calculated as you enter transactions.

Blank Values and Boolean Values

For the most part, an imported blank field is considered to be zero or the Boolean value of "FALSE" for numeric and Boolean fields respectively.

Valid values for imported Boolean fields are blank, "TRUE", "True", "FALSE", or "False" (excluding the quotation marks).

Data That Does Not Get Imported

The following data cannot be imported.

Default Information:Sage 50 cannot export or import default information such as G/L account links, custom field labels, shipping methods, payroll fields, etc. These must be copied over during New Company Setup Step 2 of 3.
Inventory Beginning Balances: You will have to manually enter these items after you complete the import process.
Job Beginning Balances: You will have to manually enter these items after you complete the import process.
Sales Tax IDs and Agencies: Sage 50 cannot export or import sales tax information. Therefore, you should copy the taxcode.dat and taxauth.dat files to the new company. If you are creating a company from scratch using import, be sure that you create any sales tax information the imported transactions include, before importing. For example, if you use a tax code in transactions (ST-TAX), you must enter this code in the Sage 50 company first.
Payroll Tax Formulas:Sage 50 cannot import or export payroll tax formulas. Therefore, you should copy the taxtable.dat file to the new company. If you do not copy this information, your payroll data will be incorrect.
Task Window Templates:Sage 50 cannot import or export task window templates. Therefore, you should copy the template.dat file to the new company.

Default Information

All default information, such as labels for custom fields, payment methods, ship-via methods, payroll fields, and price level names is not exported. Therefore, if you plan to import data into a new company, you must copy the default information during New Company Setup or enter the information manually before importing data.

Duplicate Reference Numbers

When importing journal transactions, you may encounter duplicate reference numbers/check numbers between existing records and the new records you are importing. In many cases, you can choose to skip the record with the duplicate reference or cancel the process. If you skip the record, it will not be imported. If you cancel the process, only records up until that point will be imported.

There are certain situations in which Sage 50 does not allow duplicate reference numbers. In this case, you must manually enter these records or values.

Sage 50 does not allow duplicate references in the following situations:

Sales: You cannot have a duplicate invoice number for the same customer ID.
Purchases: You cannot have a duplicate invoice number for the same vendor ID.

Duplicate reference numbers are allowed in the following situations:

Payments (Cash Disbursements): you can have a duplicate check number. You will be given a warning message allowing you to reuse the same number or cancel.
Receipts: you can have a duplicate reference number. If you selected the Warn if Duplicate Reference check box in the template and you have a duplicate reference number when importing, you will receive a warning message that a duplicate reference number exists for the same customer ID. You can click OK to import the record with the duplicate reference number, No to skip the record, or Cancel to halt the process. If you cancel, all records up until that point will be imported.
Payroll: You can have a duplicate check number. You will be given a warning message allowing you to reuse the same number or cancel.

Exporting Journal Transactions

When exporting journal transactions from one Sage 50 company to another, it is critical that you specify a date range that will include all the information you want to export. Specify a date range that goes several years back to ensure that you have included all of the information you want. See also

Field Definitions

There are some fields that have specific values, such as whether they can be imported, number of characters, etc. To examine these field definition list, see Import/Export field definition lists.

File Names for Import/Export Templates

File names for templates you are using will always default to the same name and location if you do not change this (in the Options tab) before exporting or importing. For example, when exporting the customer list, Sage 50 defaults the name and location to be as follows: C:\CUSTOMER.CSV. Thus, if you export a customer list a second time without changing the location, the first export file will be overwritten by the second export. You will receive a warning message if this is about to happen.

For a complete list of template names and their corresponding default file names for import and export, see Import/Export Field Definition Lists.

Note: It is especially important to copy or set up employee defaults if you intend to import payroll transactions. If the payroll field names do not exist in your new company, you will receive import errors.

Importing Data

Maintain and Tasks Screen Items: In Sage 50, the Maintain windows allow you to perform tasks regarding lists, such as the Customer List, Employee List, Chart of Accounts, etc. and the Tasks windows allow you to perform tasks that consist of transactions. When you are importing data, it is important to note that Sage 50 allows you to import only new transactions, that is, tasks performed in the Tasks windows. You cannot edit transactions through the import process. You can, however, import new Maintenance items or update existing ones, such as the Customer List, Employee List, etc.

Template Fields: Not all of the fields listed in a template can be imported. If you view the original template, all the fields that are checked Show (by default) can be imported. All the fields that are unchecked are export-only. There is one exception to this rule--the Customer List. The "Ship To" addresses (2-9) are not checked by default, but they can be imported. If you have several "Ship To" addresses that you want to import, make sure you check this option in the template before importing.

Importing Inventory Items

You cannot change the item classes of existing inventory items and then import the items back into your company. Sage 50 will prompt you with an importing error. For example, let's say you already have inventory item XYZ (non-stock item) in your company. If you try to import item XYZ as a stock item into the same company, you will receive an error. The reason for this error is to maintain accurate costing information and avoid duplicating or overwriting existing items.

If you are using Time & Billing, activity items and charge items cannot be a component of an assembly. You will receive an invalid item class error if this occurs. (Sage 50 Premium Accounting and higher) )

When importing activity or charge items, you must include a valid G/L sales account. When you enter new activity or charge items in the Maintain Inventory Items window, you are required to have a valid G/L sales account before you can save the new inventory item. Import has the same requirement. (Sage 50 Premium Accounting and higher)

Importing Time and Expense Tickets (Sage 50 Premium Accounting and higher)

If you are using Time & Billing, then Time and Expense tickets must be imported after the Sales Journal. If a ticket is used in Sales/Invoicing, Sage 50 must validate that the invoice exists before accepting the ticket during the import. If you are importing a customer ticket that is used in Sales/Invoicing, Sage 50 will confirm that the sales invoice referenced on the imported ticket matches the customer selected for the invoice. If you are importing a job ticket that is used in Sales/Invoicing, Sage 50 will confirm that the customer associated with this job matches the customer selected for the invoice. If the invoice does not exist or the customer does not match, then Sage 50 will display an error message and not accept the ticket.

When importing time tickets, you may want to consider using alphanumeric characters for the ticket number field. In Sage 50, ticket numbers are numeric and sequential based on when you open and save a ticket (time and expense tickets share the same numbering sequence). This way you can distinguish what was imported versus what already exists in Sage 50. (For example, assign imported ticket items A1000, A1001, and so on.). Do not use this technique when rebuilding company data.

Ticket numbers must be unique, or you will receive an error during the import. If you import a ticket number out of sequence from what already exists in Sage 50, Sage 50 will assign the first available unused number to the next ticket entered in the Time Ticket or Expense Ticket window. This avoids duplication of ticket numbering. Also, note that importing tickets may cause ticket numbering to be out of sequence (especially in network situations).

When importing tickets, employee IDs that are set up as sales reps will cause an error to occur in the Pay Level field. Sage 50 does not list sales reps in the employee ID lookup list in the Time Tickets or Expense Tickets window. Because of this, you can only import employee IDs in the Record By field that are set up as "employee" and "both" (employee and sales rep).

If you import an invalid ticket date you will receive the following error: "You cannot import transactions dated beyond the end of NEXT year or dated before the beginning of LAST year." This simply means that ticket dates must be in either the last closed fiscal year or the two currently open fiscal years of the company you importing to.

When importing tickets that "have been used in Sales/Invoicing," Sage 50 will accept the ticket if the invoice number used matches an existing invoice number in the company you are importing. However, you need to be aware of the following conditions:

If Customer/Job/Admin=2 (Job) and the customer associated with the job is not referenced on the sales invoice, you will receive the message, "Customer ID associated with a job for this ticket is not the same as the customer ID assigned to the sales invoice."
If Customer/Job/Admin=3 (Administrative), the "Has been used in Sales/Invoicing" and "Invoice Number Used" fields will be ignored.
If "Has been used in Sales/Invoicing"=FALSE, then the "Invoice Number Used" values will be ignored.

Order in Which You Import Data

The order in which you import data is very important, because files contain data that must exist in the system before certain other data can be imported.

When creating a new company, you should import list files first and in the following order:

Chart of Accounts
Employee List
Vendor List
Customer List
Inventory Item List
Phase List (Sage 50 Premium Accounting and higher)
Cost Code List (Sage 50 Premium Accounting and higher)
Job List

Once you have imported all of your list files, you can import journals in the following order:

General Journal
Purchase Order Journal
Purchases Journal
Inventory Assemblies Journal
Inventory Adjustments Journal
Sales Order Journal
Sales Journal
Time Ticket Register - if using Time & Billing (Sage 50 Premium Accounting and higher)
Expense Ticket Register - if using Time & Billing (Sage 50 Premium Accounting and higher)
Payments Journal (Cash Disbursements)
Cash Receipts Journal
Payroll Journal

If you are using inventory items, you must import purchases before sales to produce correct costing.

Quotation Marks in Memos, Notes, and Descriptions

Avoid using double quotation marks in names, notes, memos, or description text. For example, double quotation marks are used as inch marks or in quoted phrases. If the Sage 50 export/import process detects that a field value contains a comma, it automatically adds double quotation marks at the beginning and end of that field. It does this to allow commas and other punctuation within the field text. If you also include double quotation marks in the text, the import will not understand where the end of the text is. These quotation marks could change your intended field order and produce import errors.

Rebuilding an Existing Company

If you are rebuilding an existing company, you should create the new company first, making sure that you use the exact same date for your accounting periods in the new company. If the first company has custom fields (in customers, vendors, inventory, or employees), then you must set the same number of custom fields up in the receiving company. If you do not, you will receive an error message referencing the field after the custom field. For detailed instructions on rebuilding an existing Sage 50 company, see Rebuild an Existing Sage 50 Company (Overview).

If you are rebuilding an existing company, you can export the beginning balances for general ledger, customers, vendors, and employees. To export these beginning balances, you must change the date range filter (on the Export tab) to ALL and then set the beginning date several years back.

For example:

Date: ALL From: Dec 1, 1992 To: (last transaction date)

You cannot import journal entries dated past the end of the second open fiscal year based on the import company setup. You will have to close the first fiscal year, prior to importing these future transactions. However, you can import previous year journal entries and ticket transactions for beginning balance purposes.

If you do not go back far enough (the “From” date), your beginning balances will not be included and the information in your reports will be incorrect. After the import is complete, print the reports from the old company and the new company to see if they match. If the amounts are not the same, you probably did not specify an early enough date.

Required Fields

The same fields that are required by the program when entering a new record are also required when importing a new record. For example, a G/L Sales Account is required before you can save a new customer entry and a Vendor ID is required before you save a purchase entry.

Transaction Job ID Fields

Most transactions in Sage 50 can be associated with a job ID. If phase and cost code data are used in transactions (Sage 50 Premium Accounting and higher), they must be included in the imported Job ID field as a single unit. When importing any combination of jobs, phases, or cost codes, you must separate the items with commas and enclose the entire string within quotation marks. For example, your transaction job ID field would be imported as "myjobid,myphase,mycostcode" (notice no spaces between the units).

Using Spreadsheets for Imported/Exported Data

If you are using a spreadsheet that is a .txt or .csv format, consider the following:

Microsoft Excel (version 4 or 5) or Quattro Pro will drop the leading zero (for example, "01" will be truncated to "1"). In Sage 50 leading zeros are meaningful and this will lead to inaccurate data.
Text numbers such as 0.00 will be turned into a single zero ("0"), which is a whole number. When you attempt to import this "0" back into Sage 50, the import will fail.
If there is a field with a hyphen in it (such as 10-25, which might have been used as a reference number or invoice date in Sage 50), a spreadsheet will assume you meant it to be a date. As a result, when you save the spreadsheet as a .csv format, this hyphenated reference will be changed to a date, such as "25-Oct". This will lead to inaccurate data reporting in Sage 50.
Files in .csv format imported or exported from Lotus 1-2-3 are not fully compatible with the Sage 50 format. You should not use Lotus 1-2-3 .csv files in Sage 50.

Working with Journals

Journals are made up of transactions. For example, the Sales Journal contains sales invoices. The journals you can work with when importing and exporting information include:

General Journal
Purchase Order Journal
Purchases Journal
Inventory Assemblies Journal
Inventory Adjustments Journal
Sales Order Journal
Sales Journal
Time Ticket Register - if using Time & Billing (Sage 50 Premium Accounting and higher)
Expense Ticket Register - if using Time & Billing (Sage 50 Premium Accounting and higher)
Payments Journal (Cash Disbursements)
Cash Receipts Journal
Payroll Journal

Each transaction within these journals has a general structure in which they get imported and exported. This structure is based on how the transactions are entered through one of the Tasks windows.

The following is true when you enter a transaction through one of the Tasks windows:

The top half of the screen contains fields in which you enter general information for the entire transaction, also known as header information. For example, the Date and Customer ID.
The bottom half of the screen contains fields in which you enter distributions, such as the line items and quantities that are being sold on an invoice.
Each distribution is composed of a set number of fields and each transaction could contain up to 160 distributions (except for Accounts Receivable journals which contain up to 154 distributions).

When you export a journal to a spreadsheet, for example, two additional fields appear: Transaction Period and Transaction Number. These are internal system fields (they do not appear on the window in Sage 50) that Sage 50 uses to group information. When you import transactions that were created outside of Sage 50 , the system uses the date to determine which accounting period in which to post the transaction. This is because Sage 50 is a date-sensitive system.

If you create transactions in another program to import into Sage 50, the Transaction Number is a required field. This is because one transaction can have multiple distribution lines and the transaction number is used to group all of the distributions for the same transaction.

The transaction number is a whole number, and all the distributions for one transaction should have the same number. The numbers must be unique within a period.

The Transaction Period field is used for export purposes only. It allows you to determine the system period number in which the transaction appears. It has no functional purpose for the import process. (If you are importing from another system, you do not need to add this field to your data file. In addition, you should modify the journal template so it does not include the Transaction Period field.)

Chart of Accounts Beginning Balances: Beginning balances for your general ledger accounts are imported and exported as general journal transactions. That means if you try to export these beginning balances from one company to another, the balances will be correct, but you won’t be able to view these beginning balances using the Beginning Balances button on the Maintain Chart of Accounts window. Instead, you must open the General Journal Entry window and select the List button to find the beginning balance entry.

Reversing Entries: Any posted reversing entries get imported as regular general journal entries, but any associated, future reversal entries do not get imported or exported.

Recurring Entries: Recurring entries export and/or import as regular entries. However, they lose their connection as recurring entries; they are just separate entries in different periods.

Amount Paid on Invoice: There is an Amount Paid field at the bottom of the Sales/Invoicing and Purchases windows. You can enter an amount there when creating or editing a customer or vendor invoice, and a receipt or disbursement is automatically created when you post, to pay this invoice. This amount paid on the invoice or payment is not exported. However, the associated receipt or payment does get exported, so your aging reports and account balances would be correct. Just be aware that if you edit these invoices or purchases, you will no longer see any amount in the Amount Paid field.

Cost of Sales: If you import sales transactions that use inventory, the cost of sales amounts get regenerated during the import process. If you are importing from another Sage 50 company, the cost of sales in the first company may be different than in the second company. That's because the order in which you import may not match the order in which you entered the transactions.

Future Transactions: You should not attempt to import transactions into a company with dates beyond the end of the second open fiscal year. When you set up your company during New Company Setup, the fiscal year structure is established (when your first two fiscal years will begin and end). Sage 50 allows you to enter and edit transactions to the two open fiscal years. This includes importing. However, if you attempt import a journal entry dated past the end of the second fiscal year, you will get an error. For example, if you are rebuilding a company to change the fiscal year starting date from January 2012 back to July 2011. You can only import transactions into the new company up to June 2013. You will have to close the first fiscal year in the new company, and then import the remaining future entries.