Importing Data From Another Program (Sage, QuickBooks, etc)
- Background Information
- How to Import
- Import Options
- Automatch vs. Match All
- Limitations of Importing
- How to Read Error Messages
- Why Not Import Everything?
Switching from one Accounting Software package to another can be a daunting task. This article is written specifically to help with the transition between software. We have another, more general, importing article here. We also have a PDF guide that was designed to help people switching from QuickBooks convert their account information, contact list, and item list. This guide is available here.
The reason that the PDF guide only covers account information, contact list, and item list is that it is the most time efficient way to import your information and the cleanest way to import your information. While it may be possible to import years worth of transactions, it can cause various problems that are described in this section.
We suggest, instead of importing everything, that you use your old software as a "filing cabinet". You can reference old information in your old software and use AccountEdge for your new information. The best practice is to pick a Quarter End or Year End, import the three lists, and set Opening Balances for your accounts based on that Quarter End or Year End time frame. Most customers find that this is a happy medium between starting over entirely from scratch and trying to bring over everything.
How to Import
No matter what it is you are trying to import, you'll use the same basic steps to make your way through the import process. Before you can begin this process you will need to have already created your company file. If you haven't yet done this - please see this article.
Once you are logged into your Company File you follow these steps to import:
- Choose File from the menu bar at the top of the screen
- Choose Import Data from the drop down menu (it's very close to the bottom)
- Choose what you are trying to import
As a note, when comparing AccountEdge to other software, you might find that we use different terms. We use the term "Card" to denote a contact like a customer or a vendor. We use the term "Job" to identify a project or what is sometimes called a Class. You'll find the same types of slight differences in terminology generally occurs between all accounting software.
When importing, AccountEdge has the ability to accept data from Comma Seperated or Tab Delimited Text Files. When importing, no matter what kind of data it is that you are importing, you have three options. Depending on the data type there may be more options - but these apply to all imports. These three options are Import File Format, First Record Is, and Duplicate Records. You will find that for contacts and items you will have the "Identify Records by" option.
Import File Format
As noted above, AccountEdge can accept both Comma Separated and Tab Delimited Text Files. Most Accounting Software can export to these file types. You can also save into these two file types in Excel - so if your program can only export to Excel - you can always open the file there first and then save to one of these formats. It's important to choose the correct format - otherwise you will end up jumbled data during the import.
Comma Separated Text Files
Comma separated text files are just that - text files where each individual field is separated by a comma. For example, if you export information about your accounts into a text file you would see something like this:
Account Number,Account Name,Account Type,Account Opening Balance 1-1110,General Checking Account,Asset,"$30,000"
You'll notice that all of the fields are just separated by a comma, but the $30,000 has quotation marks surrounding it. This is because the quotation marks are necessary since the field has a comma inside of it. Additionally, AccountEdge only sees files with the extension .txt - if your file ends in .csv just rename it to be .txt instead.
Tab Delimited Text Files
Tab delimited text files are text files where each field is separated by a tab instead of a comma. Using the previous example, a tab-delimited text file of this information would look like this:
Account Number Account Name Account Type Account Opening Balance 1-1110 General Checking Account Asset $30,000
This is almost exactly the same as the Comma-Separated file - it just has tabs denoting the fields.
First Record Is
This option allows you to switch between the first record of your file being a Header Record or the first record of your file being a Data Record.
Our examples from the previous section both included what are known as "Header Records". This information (from our comma separated and tab delimited examples) includes the fields Account Number, Account Name, Account Type and Account Opening Balance. These records are used to identify the information that is in the rest of the file. So, for example, under the Account Number header you will see a specific account number in the rows underneath. Header Records make it easier to identify the information you are importing.
If the first line of your import doesn't have Header Information but instead goes right into the information itself you would choose Data Record. Again, using our example above, instead of starting with "Account Number" we would start with "1-1110".
This option allows you to choose how to handle Duplicate Records. Depending on the type of import you will see different options. Each option has upsides and downsides - depending on what you are trying to accomplish.
Any duplicate records will not be imported. This means that if you are importing account information and there is already an account number 1-1110 your information will not be imported for that account. The upside and the downside to this are really the same. It prevents an import from modifying changes you've made in the software.
Update Existing Record
Any duplicate records will be updated over what is in AccountEdge. This means that if you are importing account information and there is already an account number 1-1110 your information will be updated over what is in AccountEdge (for instance - changing the name of the account). The upside to this is that it allows you to make changes quickly outside of AccountEdge. The downside is that it's possible to import mistakes.
This option is available for cards and other import types. If this option is chosen, it will add any duplicate records as separate cards. The upside to this, is that if you have multiple cards with the same name (ie: you have two customers named Walmart because you sell to two separate Walmart locations) it will bring these cards in. The downside of this is that if you do multiple imports - you won't see information being updated - but just added again and again. So if you have to do a second or third import you could end up with 4 or 5 cards some of which are identical.
This is one of the reasons that we suggest backing up before each import - it's much easier to restore a backup then to try to deal with duplicate information. If you didn't backup beforehand you still have options - it's just more work. For example, if you imported duplicate cards, but haven't yet entered any transactions you can delete the cards. If you do have transactions attached to the cards you have the option of combining multiple cards into one card.
Identify Records By
This option is available for cards and other import types. We will cover the options for cards specifically in this section.
When importing we are going to look at the Last Name for cards that are individuals or Company Name for cards that are companies. This means that you would need to have a field that included this information in your import - and it would be the field that you use to identify your cards. This is best explain in relation to Card ID.
Card ID is a field that only some companies use. The best example of this would be a Gym. Rather then identifying customers by name, they might instead identify them by a gym membership number. If you have multiple customers with the same name - the Card ID can also be a differentiating factor so that you don't end up with multiple identical cards.
This is a record that is internal to AccountEdge. Generally, there's not a good reason (especially as a new customer) to attempt to import based on Record ID. We would suggest using one of the other options.
Automatch vs. Match All
Once you've chosen your different import options and selected the file you are going to import you are taken to a screen with two columns and several options.
Automatch can only be used if you are importing a text file with a Header Record. AccountEdge is going to attempt to match the Header Record (which you have on the left) with AccountEdge Fields (which you have on the right). It is looking to match these names exactly.
For example, if I'm importing accounts, and my text file has a field named Account Numbers - automatch will not work because AccountEdge is looking for a field named Account Number.
Match All does not do the same sort of intelligent field name matching that Automatch does. Match all matches everything in the left hand column to everything in the right hand column on a 1 to 1 basis. The easiest way to illustrate this is by looking at an example:
You'll notice that the first three rows on the left match the first three rows on the right. The fourth row on the left is different (and for that matter so is every row after) - but if I choose to Match All it is going to continue - matching account type to balance, last check number to account type, and so on.
The other option you have is actually matching the data manually. You can do this by clicking on a field on the left (it will highlight in blue) and then selecting the field you want to match it to on the right. This is extremely useful if the Headers in your file do not match the AccountEdge Field Names exactly OR if your first record is a data record.
Limitations of Importing
Later in this article we will cover why it doesn't make sense to import everything, but for this section we will talk about the hard limitations of the program.Inventory
AccountEdge can import your items list. This means it can bring in the actual names and numbers of the products you sell, the accounts that should be associated with these products, etc. What AccountEdge can't do is bring in your actual inventory quantity along with the item list. You can import an inventory adjustment to reflect these figures properly - or you can enter these values into AccountEdge once you import your items. This support article has instructions on entering Opening Inventory Balances to manage this.
AccountEdge can't import Payroll Checks. Payroll information can be imported as journal entries so that you have the correct accounting information - but as a brand new user you generally wouldn't want to do that as this information should be included in your opening balances.
How to Read Error Messages
When importing information into AccountEdge an import log is generated. This log is overwritten each time you import. The log will display any errors or warnings that may occur during the import process.
This support article has far more detailed information, but as a basic introduction, the error or warning information is at the bottom of the import log. You want to scroll to the bottom and find the message.
As a general rule of thumb, warnings mean that most of the information was still imported and it's not a huge worry. Errors mean that the information was skipped over and not actually imported. The error messages are generally fairly self explanatory. One thing to note - is that certain error messages can mean multiple things - for instance, if you are importing a journal entry, you may see the error message that an account is not postable. This could mean that the account doesn't exist or it could mean that the account is a Header account (which can't be posted against) and not a detail account (which can be posted against).
Why Not Import Everything?
This is a question we often get from new users who have years of data stored in another file and there are several reasons that you should not. Again, this doesn't mean it's not possible to accomplish, it's just generally not recommended because it can make a mess of your books.
You can only enter (or import) transactions into your current Fiscal Year or later. That means, if it was 2016 and you wanted to import transactions starting from 2005, you would have to create your file with a Fiscal Year of 2005. This would allow you to import transactions with dates as early as 2005. The drawback would be that until you finished your import and any manual entries that needed to be made in these years, your main reporting (Profit and Loss and Balance Sheet) would be for 2005 and wouldn't provide accurate current data. You would also need to close each Fiscal Year successively starting with 2005 and going through the process a year at a time.
When you import your account information and enter your account opening balances - you are including the value of all transactions that has led you to this point. If you were to try to import all information you would instead have to start with $0.00 opening balances and allow the transactions to create the balances in your different accounts.
If data was ever purged from the older software (which it almost certainly was) you are going to be missing transactions. We can only import whatever is in the text files. If you are missing ANY information - your account balances will be incorrect and will cause more problems then it is generally worth. This can especially be problematic when importing closed invoices from older software as AccountEdge brings in payment with closed sales.
As we talked about earlier, AccountEdge is looking for specific field names when attempting to import data. If these field names don't match AccountEdge then they need to be manually matched. Beyond this, the information in these fields may not match from program to program. For instance, AccountEdge is looking for a 5 digit account number where Sage may export a 4 digit account number. This is easy enough to modify when dealing with the Chart of Accounts - but becomes time consuming when having to edit thousands of transactions.