Importing and Exporting AccountEdge Data

Article ID: 2025221          Date Modified: 07/02/2017

Background

AccountEdge can import from and export to either comma-separated or tab-delimited text (.txt) files. The import file formats are identical to the Export file formats, so when trying to import data, we usually recommend you create your templates by Exporting existing data to get the right formats your data needs to be in and you can edit the values in Excel or a similar spreadsheet application

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 the import process, create a backup of your data
  • 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

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 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 the Import

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). 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 FY correct for import dates? Are Lock Periods in use? File 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 or AccountEdge Plus

AccountEdge Import Log

/Documents/AccountEdge 20XX GB/

AccountEdge Plus NE 2011 and Previous

AccountEdge Import Log

/Applications/AccountEdge NE 20XX/

AccountEdge Plus NE 2012 and Later

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.