File format to import transactions into QuickBooks

You can import data into QuickBooks from files that are in a number of different formats using Zed Axis.  This article is a detailed description of the different formats and best layout for importing transactions.  Included with the Zed Axis software is a folder of sample file for all the transactions types that Zed Axis can import into QuickBooks, these files are included in the Zed Axis program files located at C:\ProgramData\Zed\Axis 15.0\Sample files

File format

The file needs to be in a Microsoft Excel spreadsheet or a flat text file that separates the data fields with a comma, tab, or pipe.  Text files generally have a file extension of .txt, .csv (comma separated value), or .tab.

Data in rows

The data needs to be in rows so each row will import into QuickBooks as a line item.  For example in the file shown below this is shows two invoices reference numbers 1005 and 1006.  Invoice 1005 has only one line whilst invoice 1006 contains two lines.  When importing transaction data into QuickBooks, Zed Axis will group lines together on the same transaction as long as they have the same reference number, this allows you to import multiple transactions in one file.  If the transaction reference number is not provided then each line will import as a separate transaction and QuickBooks will automatically assign a reference number.
 

Headers and columns

The columns represent the data that you want to import into QuickBooks, we recommend that the first row of the file is a header row that contains a description of what is contained in that column.  If the file contains a header row it makes the mapping process much easier, if there is no header row then default field headers will be shown such as Field1, Field2, Field3,. The order that the columns in does not matter since the mapping process you can map in any particular order.  For more about mapping read this article.

Data issues to avoid  

• If your data contains rows that are just notes or text with no item code, QuickBooks will be unable to import these as it needs a item or account to successfully import, please remove these prior to import 
• If you data contains many blank rows this may cause issues.  Zed Axis will not import blank rows and will ignore them however if there are many blank rows Axis may assume that the data is finished and the import will exit. 
• If you data contains unsupported characters that QuickBooks does not recognise then the import will fail, as a general rule stick to commonly used characters. 
• If you data is longer than the maximum field lengths