Importing transactions to QuickBooks

Note: These instructions are rather long. import files^IIF, transactions, importing to QuickBooks for IIF, . You may want to print them instead of reading them onscreen. import files^IIF, transactions, importing to QuickBooks for IIF, . To print them, click the print icon in the upper right of this Help window.

You can import transactions to QuickBooks, provided that the transaction data is in a text file that conforms to QuickBooks import file format. import files^IIF, transactions, importing to QuickBooks for IIF, . This is a tab- or comma-delimited text file in which the transaction data appears in columns. import files^IIF, transactions, importing to QuickBooks for IIF, . Special keywords identify the beginning and end of each transaction and provide headings that indicate the type of information in each column.

IIF import resources

There are additional resources available online to help you create and use IIF files:

• IIF Import Kit

  When you download the import kit, you get an import manual, IIF header help, and sample IIF files.

• QuickBooks Community Forum

  • List of FAQs about common issues users face using IIF files. import files^IIF, transactions, importing to QuickBooks for IIF, .

  • Search the forum to see if your question has been answered. import files^IIF, transactions, importing to QuickBooks for IIF, .

  • Browse through thousands of discussions between QuickBooks users who have imported IIF files.

  • Can't find the answer you need? Post a question in the Import/Export Forum.

To do this task

1. If you've already created an IIF file, go directly to step 7 below. import files^IIF, transactions, importing to QuickBooks for IIF, . If the original transaction data is already in a file, open that file. import files^IIF, transactions, importing to QuickBooks for IIF, . Otherwise, create a new file in either a word processor or a spreadsheet.

   Important: If you are using a spreadsheet, it must be capable of saving the file as a tab-delimited text file (most spreadsheets can). import files^IIF, transactions, importing to QuickBooks for IIF, . If you are using a word processor, it must be capable of saving the file as a text file without embedded formatting codes. import files^IIF, transactions, importing to QuickBooks for IIF, . In either case, your end result is a file in which tab characters separate blocks of transaction data.

2. Set up the first column. import files^IIF, transactions, importing to QuickBooks for IIF, . This column is reserved for special keywords that identify the beginning and end of each transaction. import files^IIF, transactions, importing to QuickBooks for IIF, . Follow the guidelines that apply to the type of program you are using:

   • Spreadsheets: Use the first (leftmost) column in the spreadsheet. import files^IIF, transactions, importing to QuickBooks for IIF, . The first three cells in the column must read as follows:

     !TRNS

     !SPL

     !ENDTRNS

     For now, leave the remaining cells in the column blank.

   • Word processors: The first three lines of the file must begin as follows:

     !TRNS <tab>

     !SPL <tab>

     !ENDTRNS <tab>

     Note: <tab> indicates that you press the Tab key after entering the text. import files^IIF, transactions, importing to QuickBooks for IIF, . Do not type "<tab>".

3. Add the headings for the other columns. import files^IIF, transactions, importing to QuickBooks for IIF, . (For a model, use the two examples below.) In general, follow these guidelines:

   • For a list of the keywords you can use for column headings, see the keyword reference.

   • The keywords must appear at the beginning of the file, before any of the transaction data. import files^IIF, transactions, importing to QuickBooks for IIF, . In a spreadsheet, put the keywords in the first two rows, like this:

     !TRNS	TRNSTYPE	DATE	ACCNT... import files^IIF, transactions, importing to QuickBooks for IIF, . etc.
     !SPL	TRNSTYPE	DATE	ACCNT... import files^IIF, transactions, importing to QuickBooks for IIF, . etc.

     In a text file, put the keywords in the first two lines. import files^IIF, transactions, importing to QuickBooks for IIF, . Press Tab after each keyword:

     !TRNS <tab>	TRNSTYPE <tab>	ACCNT <tab>... import files^IIF, transactions, importing to QuickBooks for IIF, . etc.
     !SPL <tab>	TRNSTYPE <tab>	ACCNT <tab>... import files^IIF, transactions, importing to QuickBooks for IIF, . etc.

   • The first row of keywords covers general information that appears in each transaction (the date of the transaction, the customer's name, the vendor's name, the transaction total, and so on). import files^IIF, transactions, importing to QuickBooks for IIF, . The second row of keywords applies specifically to the distribution lines of the transaction (the amount of each distribution, the income or expense account to which you assigned the distribution, and so on). import files^IIF, transactions, importing to QuickBooks for IIF, . In some cases, the same keyword can apply to both the transaction as a whole and to a distribution line.

   • Use only the keywords that apply to your transactions—you do not have to use all the keywords listed in the keyword reference. import files^IIF, transactions, importing to QuickBooks for IIF, . For example, if you do not include payment terms on your invoices, you do not need a Terms column in the import file.

   Only three keyword columns are required for transactions—all others are optional. import files^IIF, transactions, importing to QuickBooks for IIF, . The required keywords are:

   TRNSTYPE	Indicates the type of transaction.
   ACCNT	For the transaction as a whole: the name of the balance sheet account to which you assigned the transaction. For a distribution line: the name of the income or expense account to which you assigned the distribution line.
   AMOUNT	For the transaction as a whole: the transaction total. For a distribution line: the amount of the distribution.

4. Enter the specific details of each transaction. import files^IIF, transactions, importing to QuickBooks for IIF, . (Again, use the two examples in Step 3 as a model.) In general, follow these steps:

   • Start at the beginning of the first row or line after !ENDTRNS. import files^IIF, transactions, importing to QuickBooks for IIF, . This is row 4 in a spreadsheet and line 4 in a text file.

   • Type the keyword TRNS to indicate the beginning of the first transaction. import files^IIF, transactions, importing to QuickBooks for IIF, . If you are using a text file, press Tab after TRNS.

   • Go to the beginning of the next row or line and enter the keyword SPL to indicate the first distribution line of the transaction. import files^IIF, transactions, importing to QuickBooks for IIF, . If you are using a text file, press Tab after SPL.

   • If the transaction has more than one distribution line, repeat the last step for each distribution line. import files^IIF, transactions, importing to QuickBooks for IIF, . Use a separate row or line for each distribution line.

   • Type the keyword ENDTRNS in the first row or line after the last distribution line. import files^IIF, transactions, importing to QuickBooks for IIF, . If you are using a text file, press Tab after ENDTRNS.

   • Fill in the transaction details. import files^IIF, transactions, importing to QuickBooks for IIF, . General information about the transaction goes into the row or line that begins with the keyword TRNS. import files^IIF, transactions, importing to QuickBooks for IIF, . Information about the distribution lines goes in the rows or lines that begin with SPL. import files^IIF, transactions, importing to QuickBooks for IIF, . If you are using a text file, press Tab after each item of information.

   Note: The column headings you added in Step 3 indicate where to put each item of information. import files^IIF, transactions, importing to QuickBooks for IIF, . For example, transaction dates go into the DATE column.

   • Transaction amounts require special attention. import files^IIF, transactions, importing to QuickBooks for IIF, . See Step 5 below.

   • The names of accounts that you enter must be the names of accounts in the chart of accounts of your QuickBooks company. import files^IIF, transactions, importing to QuickBooks for IIF, . If they are not, you can set up the accounts in the import file. import files^IIF, transactions, importing to QuickBooks for IIF, . The account information must precede the first transaction in the import file.

   • You cannot create a link between two transactions. import files^IIF, transactions, importing to QuickBooks for IIF, . For example, if one transaction is an invoice and another transaction is a payment for the invoice, you cannot indicate in the import file that the payment is to be applied to the invoice. import files^IIF, transactions, importing to QuickBooks for IIF, . To apply the payment, you must wait until you have imported the transactions into QuickBooks. import files^IIF, transactions, importing to QuickBooks for IIF, . Then, go to the Receive Payments window and apply the payment from there.

   • Repeat this process for each additional transaction.

5. In the Amount column, enter the transaction amounts. import files^IIF, transactions, importing to QuickBooks for IIF, . For each transaction, do the following:

   • Enter the total amount of the transaction in the Trns row of the Amount column.

   • Enter the distribution line amounts in the SPL rows of the Amount column.

   • Important: Follow these guidelines for making an amount positive or negative:

     If the amount is in...	Make the amount
     An asset account (like accounts receivable)	Positive—if it increases your assets. import files^IIF, transactions, importing to QuickBooks for IIF, . (Example: an invoice.) Negative—if it decreases your assets. import files^IIF, transactions, importing to QuickBooks for IIF, . (Example: a credit memo.)
     A liability account (like accounts payable)	Negative—if it increases your liabilities. import files^IIF, transactions, importing to QuickBooks for IIF, . (Example: a bill from a vendor.) Positive—if it decreases your liabilities. import files^IIF, transactions, importing to QuickBooks for IIF, . (Example: a credit from a vendor.)
     A capital or equity account	Negative—if it increases your equity or capital. Positive—if it decreases your equity or capital.
     An income account	Negative—if it increases your income. Positive—if it decreases your income.
     An expense account	Positive—if it increases your expenses. Negative—if it decreases your expenses.

   • As a final check, add up all the amounts. import files^IIF, transactions, importing to QuickBooks for IIF, . For each transaction, the amounts should total zero.

6. When you've entered all the data, save the import file. import files^IIF, transactions, importing to QuickBooks for IIF, . Follow the guidelines that apply to the type of program you are using:

   • Spreadsheets: Save the file as a tab-delimited text file.

   • Word processors: Save the file as a text file.

7. In QuickBooks, go to the File menu, choose Utilities, choose Import, and then click IIF Files.

8. Enter the location and name of the import file.

9. Click OK to import the transactions.

Check example

The following example shows a check in QuickBooks import file format. import files^IIF, transactions, importing to QuickBooks for IIF, . To see the entire example, you may have to widen this Help window. import files^IIF, transactions, importing to QuickBooks for IIF, . You can also print the example by right-clicking in this window and choosing Print Topic.

Because there are too many columns of information to show, we have split the check into two parts. import files^IIF, transactions, importing to QuickBooks for IIF, . Here are the first seven columns:

Here are the remaining columns. import files^IIF, transactions, importing to QuickBooks for IIF, . If we had not split up this example, these columns would appear to the right of the first seven columns:

Notes

• This example could have been created in either a spreadsheet or a text file. import files^IIF, transactions, importing to QuickBooks for IIF, . In a spreadsheet, each item of information would have a cell of its own. import files^IIF, transactions, importing to QuickBooks for IIF, . In a text file, tab characters would separate the data columns.

• The check is made out to Koepplinger Landowners for the amount of 550.00. import files^IIF, transactions, importing to QuickBooks for IIF, . The import file shows the amount as a negative number (-550.00) because a check decreases the value of a company's assets. import files^IIF, transactions, importing to QuickBooks for IIF, . This is purely for QuickBooks internal accounting purposes. import files^IIF, transactions, importing to QuickBooks for IIF, . If you viewed this check in QuickBooks, the check amount would be 550.00, not -550.00.

  The check also has two distribution lines (designated SPL in the import file). import files^IIF, transactions, importing to QuickBooks for IIF, . The first distribution line assigns 500.00 of the check to an expense account named Rent. import files^IIF, transactions, importing to QuickBooks for IIF, . The second distribution line assigns 50.00 of the check to an expense account named Utilities. import files^IIF, transactions, importing to QuickBooks for IIF, . Here, the amounts are positive because they represent increases to the company's expenses. import files^IIF, transactions, importing to QuickBooks for IIF, . Note that the detail amounts and the check amount add up to zero.

• The import file also contains other information about the check. import files^IIF, transactions, importing to QuickBooks for IIF, . The check number (1985) is in the DOCNUM column. import files^IIF, transactions, importing to QuickBooks for IIF, . A memo to the payee ("April rent") appears in the MEMO column.

• The TRNSTYPE column identifies the type of transaction—check, invoice, bill, etc. import files^IIF, transactions, importing to QuickBooks for IIF, . The keyword to use for checks is CHECK. import files^IIF, transactions, importing to QuickBooks for IIF, . It must appear in each line of data—the SPL lines as well as the TRNS line.

• The accounts (Checking, Rent, Utilities) must be either in the chart of accounts of your QuickBooks company or defined in the import file. import files^IIF, transactions, importing to QuickBooks for IIF, . When neither is the case, QuickBooks creates the accounts when you import the transactions, making each account a bank account. import files^IIF, transactions, importing to QuickBooks for IIF, . If you are using the import file to set up the accounts, the account information must precede the first transaction in the file.

Invoice example

The following example shows an invoice in QuickBooks import file format. import files^IIF, transactions, importing to QuickBooks for IIF, . To see the entire example, you may have to widen this Help window. import files^IIF, transactions, importing to QuickBooks for IIF, . You can also print the example by right-clicking in this window and choosing Print Topic.

Because there are too many columns of information to show, we have split the invoice into two parts. import files^IIF, transactions, importing to QuickBooks for IIF, . Here are the first nine columns:

Here are the remaining columns. import files^IIF, transactions, importing to QuickBooks for IIF, . If we had not split up this example, these columns would appear to the right of the first nine columns:

Notes

• This example could have been created in either a spreadsheet or a text file. import files^IIF, transactions, importing to QuickBooks for IIF, . In a spreadsheet, each item of information has a cell of its own. import files^IIF, transactions, importing to QuickBooks for IIF, . In a text file, tab characters separate the data columns.

• The invoice is made out to Lisa Yee at 541 Surf Road, Sycamore, CA. import files^IIF, transactions, importing to QuickBooks for IIF, . The invoice total (669.62) is a positive number because an invoice increases the value of a company's assets.

  The invoice contains three line items (designated SPL in the import file). import files^IIF, transactions, importing to QuickBooks for IIF, . The first line item is for a light fixture (-456.92), the second is for labor hours (-175.00), and the third is for sales tax on the light fixture (-37.70). import files^IIF, transactions, importing to QuickBooks for IIF, . The amounts are negative because they are increases, either to the company's income (as in the case of the light fixture and the labor hours), or to the company's liabilities (as in the case of the sales tax). import files^IIF, transactions, importing to QuickBooks for IIF, . The negative amounts are necessary for QuickBooks internal accounting purposes. import files^IIF, transactions, importing to QuickBooks for IIF, . If you viewed this invoice in QuickBooks, the line item amounts would all be positive. import files^IIF, transactions, importing to QuickBooks for IIF, . Note that the invoice total and the line item amounts all add up to zero.

• The import file also contains other information about the invoice. import files^IIF, transactions, importing to QuickBooks for IIF, . The payment terms for the invoice (1%10 Net 30) appear under TERMS. import files^IIF, transactions, importing to QuickBooks for IIF, . A message to the customer ("Thank you!") appears under INVMEMO (QuickBooks will print this message at the bottom of the invoice). import files^IIF, transactions, importing to QuickBooks for IIF, . A notation that the invoice is yet to be printed (Y) appears under TOPRINT. import files^IIF, transactions, importing to QuickBooks for IIF, . All of this is information that QuickBooks stores with the invoice.

• The TRNSTYPE column identifies the type of transaction—invoice, check, bill, etc. import files^IIF, transactions, importing to QuickBooks for IIF, . The keyword to use for invoices is INVOICE. import files^IIF, transactions, importing to QuickBooks for IIF, . It must appear in each line of data—the SPL lines as well as the TRNS line.

• The accounts (Accounts Receivable, Sales Income, Labor Income, Sales Tax Payable) must be either in the chart of accounts of your QuickBooks company, or defined in the import file. import files^IIF, transactions, importing to QuickBooks for IIF, . When neither is the case, QuickBooks creates the accounts when you import the transactions making each account a bank account. import files^IIF, transactions, importing to QuickBooks for IIF, . If you are using the import file to set up the accounts, the account information must precede the first transaction in the file.

• The EXTRA column contains the keyword AUTOSTAX, which creates a sales tax line on the invoice. import files^IIF, transactions, importing to QuickBooks for IIF, . You must enter AUTOSTAX even if you do not charge sales tax.

See also

• Reference Guide to Import Files

• Creating an import file