This section contains valuable tips regarding importing and exporting procedures. You will find these tips very useful before you begin a Sage 50 import/export process.
The tips are listed in alphabetical order by procedure.
Aging Information and Invoice Due Date
Blank Values and Boolean Values
Data That Does Not Get Imported
Exporting Journal Transactions
File Names for Import/Export Templates
Importing Time and Expense Tickets
Order in Which You Import Data
Quotation Marks in Memos, Notes, and Descriptions
Rebuilding an Existing Company
Using Spreadsheets for Imported/Exported Data
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 are stored in the following files:
|
Beginning Balance Items |
Journal File Stored |
|---|---|
|
General Journal |
|
|
Purchase Journal |
|
|
Sales Journal |
|
|
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. |
|
|
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.
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).
The following data cannot be imported.
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.
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:
Duplicate reference numbers are allowed in the following situations:
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
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 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.
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.
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)
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:
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:
Once you have imported all of your list files, you can import journals in the following order:
If you are using inventory items, you must import purchases before sales to produce correct costing.
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.
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.
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.
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).
If you are using a spreadsheet that is a .txt or .csv format, consider the following:
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:
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:
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.