Importing and Exporting Data To and From AccountEdge

Article ID: 2025221            Last Updated: 5 January 2023

 

Background

AccountEdge can import from and export to either comma-separated or tab-delimited text (.txt) files. Import file formats are identical to export file formats, so when importing data, we recommend you create your templates by first exporting some existing data.

You can export data samples from the Widgets sample company if you do not yet have your own AccountEdge company file. This will provide you with an example file in the format your own date will need to be formatted for import.

How to Import and Export

Exporting

How to export data from AccountEdge:

• Go to the File - Export Data drop down menu option.
• Select what you want to export (e.g.: Items).
• You'll have a selection for the format you want to save in (comma separated or tab delimited text file) and whether the first record is a Header record (we would recommend yes for using this with most applications).
• After you hit Continue you'll be given the option of what fields you want to export that are associated with the export (for instance - using the items example - you can choose to just export the item number and item name) - if you want everything click Match All and then click export. If you intend to use the export as a template for importing into another AccountEdge company file, we recommend you use Match All to prevent any matching errors when importing it later.
• You'll be given a save dialogue box and can choose where to save the text file.

Importing

How to import data into AccountEdge:

• Before commencing any/every import process, create a backup of your data. Imports cannot be 'undone' and you will need to use a pre-import version of your file if the import does not complete as desired.
• Go to the File - Import Data drop down menu option.
• Select what you want to import (e.g.: Items)
• You'll have a need to select what kind of text file you are importing (tab delimited or comma separated text file), whether the first record in the file is a Header Record or Data Record (if your file contains Headers it makes things easier), whether or not to import duplicate records and how you want to identify the import. Record IDs are background records the software uses so this is generally not the correct choice.
• After you hit Continue you'll need to find and select your text file.
• From there you can choose to Automatch (which will match Header Name to Header Name), Match All which will match field 1, 2, 3 on the text file to field 1, 2, 3 (and so on) in AccountEdge, or - you can just click the option on the left and select the option on the right.
• Click Ok to import.
• Review the Import Log for any errors or warnings and take appropriate actions to correct the errors or omissions.
• If you intend to import more records, then backup your data before begininning the next import.

 

Typical Order of Importing Data

For importing various types of data into a new AccountEdge file, the list below shows the typical import order.

1. Accounts Information
2. Accounts Budgets 
3. VAT Codes 
4. Departments 
5. Currencies 
6. Custom Lists 
7. Lead Sources 
8. Lead Statuses 
9. Brands 
10. Tags 
11. Items 
12. Cards - Leads 
13. Cards - Customer Cards 
14. Cards - Supplier Cards 
15. Cards - Employee Cards
16. Cards - Personal Cards 
17. Jobs 
18. Mileage Logs 
19. Activities 
20. Activity Slips 
21. Timesheets 
22. Purchases 
23. Sales 
24. Stock Adjustments 
25. Disbursements - Pay Suppliers 
26. Disbursements - Spend Money 
27. Receipts - Receive Payments 
28. Receipts - Receive Money 
29. Transaction Journals 
30. Reminder Logs

Notes:

• For transaction imports (journal entries, sales, etc) different transactions are separated by a blank line.
• Field Information: There is a separate document that details the different fields that each type of information requires and the limitations of those fields. This is available by clicking here.
• Some records such as stock Locations cannot be imported so you will need to create this manually in your file before importing your items.
• Payroll records are not exportable nor importable, and will need to be reprocessed manually in your new file or you could import the journal values for your financial records up to a certain point before you begin processing paycheques manually, however you must ensure correct pay history values are manually recorded on each employee's card if the first paycheque being processed is part way through the payroll tax year.
• When importing, the Record ID should be imported if you're sure it's the correct Record ID in the new file. If it isn't, or you are not sure, this field should not be imported. It's usually not necessary to import the Record ID into a new file.
• Some elements could easily be duplicated so it's not a must to import all kinds of records e.g. Sales or Purchases imported with a paid today value will create the respective receipt or payment record with the date matching the sale or purchase date, and Transaction Journals are created when other transactions are recorded (Sales, Purchases, Spend Money, Receive Money, Nominal Journals), so the user must decide which ones to import and which ones to leave out to avoid duplicating data. 　After importing, its best to run various reports to ensure that your financial reports match your previous file or system.

Troubleshooting Imports

Trouble Shooting Steps:

1. Determine type of import  Does the import require more than one line per transaction? List imports are usually one line. Most transactions are two or more lines with an empty line between each new transaction. Know the type of import before proceeding.
2. Can't open input file  Is the file extension prohibiting AccountEdge from selecting the file? Change the extension to .txt.
3. No Import, but no errors  File may have NL line endings; will need to convert to CR or CRLF. Usually you can determine this when looking at the field match window. If all fields are on one line on the left-hand panel, you know the import will not be correct.
4. Open Import Log File; Check Error Number(s)  Look at the bottom of the log, what is error? Positive error numbers are warnings, negative are fatal errors.
5. Can't find Match, xxx Not Found  The RecordID doesn't match. Do a Match All, then uncheck the RecordID field.
6. Date Errors  Is date format correct? Is the financial year correct for import dates? Are Lock Periods in use? Has the company file been set to read-only?
7. Amount Errors  Often either the fields being matched into the amount column are not amount fields hence we recommend using header records for importing or the amounts may have currency symbols that are not being accepted by the import and are better of being removed and left as number values rather than currency values

The Import Log

The import log is invaluable for troubleshooting import issues. Any errors or warnings that occurred during the import process can be found in the import log that is created after the import is complete. The errors or warnings are listed at the bottom of the report and correspond to the number in front of each record. Each time you import, the previous log will be overwritten.

Import Log Locations:

AE Version	Log File Name	Log File Location
AccountEdge Pro	Import Log	/Documents/AccountEdge Pro GB/
AccountEdge NE	Import Log	/Documents/AccountEdge NE GB/
AccountEdge or AccountEdge Plus 2010 - 2021	AccountEdge Import Log	/Documents/AccountEdge 20xx GB/
AccountEdge Plus NE 2011 and earlier	AccountEdge Import Log	/Applications/AccountEdge NE 20xx/
AccountEdge Plus NE 2012 - 2021	AccountEdge Import Log	/Documents/AccountEdge NE 20xx GB/

 

Reading the Import Log

The import log shows errors and warnings. Warnings are represented by a positive number. Errors are represented by a negative number. An error prevents importing, a warning is a comparatively minor issue. If you scroll to the very bottom of the import log it will give you a listing of all errors and warnings with their different number codes (as well as a description of what the problem is).

The error will also display next to the piece of information that is problematic. If it is a multi-line transaction it will display next to the first line of that transaction - this may not be the line that actually is the problem.

Troubleshooting Tips:

• Excel Export Note  When saving an Excel worksheet as either a tab-delimited or comma-separated file, Excel encloses any field that contains a comma in the field with double-quotes; this means Excel will add double-quotes to comma-separated data and to tab-delimited data if there is a comma in the field; AccountEdge does not strip off the double-quotes of a tab-delimited file; so, a comma-separated file is the preferred format.

 

• Import into Excel  When importing into Excel, account numbers can sometimes be interpreted as dates. When this happens, try again, but set the column to a text field rather than a General field.

 

• Blank Lines Separate Transactions  Blank lines separate records in an import file. For example, an invoice can have several lines items. The import lines would not be separated by a blank line (all the lines would be imported into one invoice transaction). But a blank line is necessary to indicate a new transaction record.

 

• Multi-line Import Failure  Sometimes users attempt to import data such as Inventory Adjustments, as one (huge) record. This isn't a problem. But if one of the lines has incorrect data, the entire import will fail with one error code, and you won't know which line has the actual error. If this happens, check the Transaction Journal (with All tab selected). Verify no import was done, or if partial import was completed. Make a copy of the input file, then delete lines that have been imported. Insert blank lines to separate the records, to isolate the failing line.

 

• All Data on one Line in Import Window  When the input fields on the left side of the Import Window are displayed on one line, chances are the file format was incorrectly indicated. That is, the file may be in CSV format, but the user selected Tab-delimited, or visa-versa. If this isn't the case, check the next item.

 

• Lines that end in LF (NL)  Mac AccountEdge doesn't always like data lines ending with a line feed (NL). The result will be an Import Window with all data being displayed on one line. Sometimes, there may be several lines displayed, but the import will not work. If you use Text Wrangler (free from Bare Bones Software), you can see and change the line termination encoding. It's shown at the bottom of the Text Wrangler editor. Change NL to CR/LF or CR.

 

• .CSV Extension  AccountEdge doesn't always like file names with a .csv extension; you won't be able to select the file. Change the extension to .txt

 

• Saving Files from a Web Browser  Some browsers append file name extensions unnecessarily. So, the data inside a file may not be what's expected when looking at the file name extension. Sometimes, you'll have to verify the contents are in the format that is expected.

 

• RecordID  The message will be: record not found. The RecordID of some records refers to the record in an AccountEdge database. If records are exported from one file, and imported into another, the RecordID may not be correct for the destination file. Is these cases, the RecordID does not have to be imported. (You can Match All, then click the RecordID to exclude it from import or export).

 

• Debits and Credits  The sum of debits must equal the sum of credits.

 

• Single- to Multi-Line Import  From some eCommerce systems, complete transactions are listed on one line (e.g., PayPal). Depending on the transaction type, the data from the one line may need to be massaged into 2 or more lines, and in some cases, 2 or more transactions. PayPal records may need to be separate transactions. One for the sale and one representing the PayPal fee.

 

• Account Numbers  Account numbers used in imports must reference AccountEdge Detail Accounts. You cannot post to Header Accounts. Accounts have the format: X-XXXX or XXXXX (with or without the '-').

 

• Currency  Currency amounts can be imported with or without the currency prefix configured in the AccountEdge company file for the currency code. When you import foreign currency transactions, you must indicate the Currency Code in the Currency Code field.

Additional Information

Further information from the AccountEdge help pages can be found by clicking here.

 

 

 

Return to AccountEdge Support