Background

AccountEdge can import from and export to either comma-separated or tab-delimited text files. It is generally recommended to use the comma-separated text file format. The rest of this article will provide information on which information can be imported and export, the order in which you should import information, troubleshooting tips and more.

What Can Be Imported

The following information can be imported from the File > Import Data drop-down menu:

• Tax Code List
  • Non-Consolidated Codes
  • Consolidated Codes
• Accounts
  • Account Information
  • Budgets
• Cards
  • Leads
  • Customers
  • Vendors
  • Employees
  • Personals
  • Lead Sources
  • Lead Statuses
  • Reminders
• Items
  • Item Information
  • Auto-Build Information
  • Brands
  • Tags
• Jobs
  • Job Information
  • Job Statuses
• Activities
  • Activity Information
  • Activity Slips
  • Timesheets
• Departments
• Currencies
• Tax Code Lists
• Custom Lists
• Mileage Logs
• Transactions
  • Disbursements
    • Pay Bills
    • Spend Money
  • Receipts
    • Receive Payments
    • Receive Money
  • Inventory
    • Inventory Adjustments
    • Auto-Build Transactions
  • Transaction Journals
  • Sales
    • Service Sales
    • Item Sales
    • Professional Sales
    • Time Billing Sales
    • Miscellaneous Sales
  • Purchases
    • Service Purchases
    • Item Purchases
    • Professional Purchases
    • Miscellaneous Purchases
• Full Service Payroll
• TAL General Journal Entries
• Quicken® QIF File

Notes

• AccountEdge does not import employee payroll checks, inventory quantities, or inventory values, but you can import inventory adjustments.
• When importing data from a QIF file into AccountEdge, you will need to change the file name extension of the QIF file from .qif to .txt.

Format for Importing Text Files

While AccountEdge can import either comma-separated or tab-delimited text files, there is certain formatting that it requires for the different types of information that can be imported.

Some imports require 1 line per record or transaction. Some imports, require 2 or more lines per record or transaction; minimally 2 lines are required: one for the debit side of the transaction, and one for the credit side of the transaction. If more than one account is being debited, or more than one account is being credited, then more than 2 lines would be required. Each line would contain either a debit or a credit. The total of debits must equal the total of credits.

Note: For transaction imports (journal entries, sales, etc) different transactions are separated by a blank line.

The following imports require one line per record

• Account Budgets
• Accounts List
• Activities
• Activity Slips
• Brands
• Cards (customer, vendor, employee, personal)
• Custom Lists (1 or more lines)
• Departments
• Inventory Adjustments (1 or more lines)
• Items List
• Jobs
• Mileage Log
• Pay Bills
• Receive Payments
• Reminder LogsSales (service, item
• Tags
• Tax Code List (consolidated) (1 or more lines)
• Tax Code List (non-consolidated)
• Timesheets

The following imports require 1 or more lines

• Sales
• Purchases

The following imports require 2 or more lines per record (transaction)

• General Journal Entry
• Receive Money
• Spend Money

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.

Import Order

For importing data into a new file, the list below shows the proper import order.

Some records items cannot be imported. It is not possible to import Activities, Categories, Currencies and Locations. Payroll records are not exportable nor importable.

Note: 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.

The import order of record items, when exporting records from one file and importing into a new file, is:

1. Account Information
2. Tax Code List
3. Custom Lists
4. Employee Cards
5. Vendor Cards
6. Customer Cards
7. Jobs
8. Activities
9. Activity Slips
10. Timesheets
11. Tags
12. Brands
13. Items
14. Purchases
15. Sales
16. Disbursements
17. Receipts
18. Inventory Adjustments
19. General Journal Entries
20. Reminder Logs

Troubleshooting the Import

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. Know the type of import before proceeding.
2. Determine Origin of Import Data - Did input file come from web; maybe extension is not correct for content.
3. Determine input file format - Look at data; verify its integrity.
4. Can't open input file - Is the file extension prohibiting AccountEdge from selecting the file? Change the extension to .txt.
5. 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.
6. Determine Problem or Error User is Experiencing - What is the problem: can't find or open input file; can't match fields; can't determine error, etc.
7. Open Import Log File; Check Error Number(s) - What is error? Positive error numbers are warnings, negative are fatal errors.
8. Can't find Match, xxx Not Found - The RecordID doesn't match. Do a Match All, then uncheck the RecordID field.
9. Date Errors - Is date format correct? Is FY correct for import dates? Are Lock Periods in use? File Read-only?

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

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 Mac AccountEdge Pro 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.
• Error -190 (190) To resolve error -190 (190), Customer/Vendor not found, remove the 'Record ID' (not the Card ID) when importing. Once the 'Record ID' is removed, those sales will import (unless there are other errors).
• Error 84 (Error -84) when Importing Service Sales and POs When importing data, the error message at the bottom of the import log reads, "Foreign Currency Linked Accounts Missing." (There is a linked account missing.) This error is caused by some linked account being invalid or unselected. Check the linked accounts by going into Setup > Linked Accounts, and verify a link exists for each of the linked accounts.

What Can Be Exported

AccountEdge can export to comma-separated or tab-delimited text files. These files can then be opened with Excel or many other programs. We do not have the ability to export EDI (Electronic Data Interchange) files.

The following information can be exported from AccountEdge:

• Tax Code List
  • Non-Consolidated Codes
  • Consolidated Codes
• Accounts
  • Account Information
  • Budgets
• Cards
  • Leads
  • Customers
  • Vendors
  • Employees 
  • Personals
  • Lead Sources
  • Lead Statuses
  • Reminders
• Items
  • Item Information
  • Auto-Build Items
  • Brands
  • Tags
• Jobs
  • Job Information
  • Job Status
• Activities
  • Activity Information
  • Activity Slips
  • Timesheets
• Departments
• Currencies
• Custom Lists
• Mileage Logs
• Transactions
  • Spend Money
  • Receive Money
  • Inventory
    • Inventory Adjustments
    • Auto-Build Transactions 
  • Transaction Journals
  • Sales
    • Service Sales
    • Item Sales
    • Professional Sales
    • Time Billing Sales
    • Miscellaneous Sales
  • Purchases
    • Service Purchases
    • Item Purchases
    • Professional Purchases
    • Miscellaneous Purchases
• TAL Accounts List

Note: The TAL Accounts List export is for a software called Time Slips Accounting Link.

Importing

You can import into AccountEdge by:

• Go to File from the menu bar at the top of the screen
• Choose Import near the bottom of the drop-down menu
• Select what you want to import (i.e.: 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

Exporting

You can export from AccountEdge by:

1. Go to File from the menu bar at the top of the screen
2. Choose Export near the bottom of the drop-down menu
3. Select what you want to export (i.e.: Items)
4. 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)
5. 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 - I can choose to just export the item number and item name) - if you want everything click Match All and then click export
6. You'll be given a save dialogue box and can choose where to save the text file

Click here for a PDF guide on importing accounts, cards, items, & activities into AccountEdge.