QB Issue Resolution:

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

To do this task

  1. If you’ve already created an IIF file, go directly to step 7 below. If the original transaction data is already in a file, open that file. Otherwise, create a new file in either a word processor or a spreadsheet.
  2. Important: If you are using a spreadsheet, it must be capable of saving the file as a tab-delimited text file (most spreadsheets can). If you are using a word processor, it must be capable of saving the file as a text file without embedded formatting codes. In either case, your end result is a file in which tab characters separate blocks of transaction data.
  3. Set up the first column. This column is reserved for special keywords that identify the beginning and end of each transaction. Follow the guidelines that apply to the type of program you are using:
    • Spreadsheets: Use the first (leftmost) column in the spreadsheet. The first three cells in the column must read as follows:!SPLFor now, leave the remaining cells in the column blank.
    • !ENDTRNS
    • !TRNS
    • Word processors: The first three lines of the file must begin as follows:!SPLNote: indicates that you press the Tab key after entering the text. Do not type “”.
    • !ENDTRNS
    • !TRNS
  4. Add the headings for the other columns. (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. In a spreadsheet, put the keywords in the first two rows, like this:
      !TRNS TRNSTYPE DATE ACCNT… etc.
      !SPL TRNSTYPE DATE ACCNT… etc.

      In a text file, put the keywords in the first two lines. Press Tab after each keyword:

      !TRNS TRNSTYPE ACCNT … etc.
      !SPL TRNSTYPE ACCNT … 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). 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). 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. 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. 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.
  5. Enter the specific details of each transaction. (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. 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. 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. 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. 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. If you are using a text file, press Tab after ENDTRNS.
    • Fill in the transaction details. General information about the transaction goes into the row or line that begins with the keyword TRNS. Information about the distribution lines goes in the rows or lines that begin with SPL. 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. For example, transaction dates go into the DATE column.

    • Transaction amounts require special attention. 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. If they are not, you can set up the accounts in the import file. The account information must precede the first transaction in the import file.
    • You cannot create a link between two transactions. 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. To apply the payment, you must wait until you have imported the transactions into QuickBooks. Then, go to the Receive Payments window and apply the payment from there.
    • Repeat this process for each additional transaction.
  6. In the Amount column, enter the transaction amounts. 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. (Example: an invoice.)

      Negative—if it decreases your assets. (Example: a credit memo.)
      A liability account
      (like accounts payable)      		 Negative—if it increases your liabilities. (Example: a bill from a vendor.)

      Positive—if it decreases your liabilities. (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. For each transaction, the amounts should total zero.
  7. When you’ve entered all the data, save the import file. 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.
  8. In QuickBooks, go to the File menu, choose Utilities, choose Import, and then click IIF Files.
  9. Enter the location and name of the import file.
  10. Click OK to import the transactions.

Check example

The following example shows a check in QuickBooks import file format. To see the entire example, you may have to widen this Help window. 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. Here are the first seven columns:

!TRNS TRNSTYPE DATE ACCNT NAME AMOUNT DOCNUM
!SPL TRNSTYPE DATE ACCNT NAME AMOUNT DOCNUM
!ENDTRNS            
TRNS ✓ 3/31/94 Checking Koepplinger Landowners -550.00 1985
SPL ✓ 3/31/94 Rent   500.00  
SPL ✓ 3/31/94 Utilities   50.00  
ENDTRNS            

Here are the remaining columns. If we had not split up this example, these columns would appear to the right of the first seven columns:

MEMO CLEAR TOPRINT ADDR1 ADDR2 ADDR3
MEMO CLEAR QNTY INVITEM    
           
April rent N N Koepplinger Landowners 876 West Paul Avenue Sycamore, CA
  N        
  N        

Notes

  • This example could have been created in either a spreadsheet or a text file. In a spreadsheet, each item of information would have a cell of its own. 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. The import file shows the amount as a negative number (-550.00) because a check decreases the value of a company’s assets. This is purely for QuickBooks internal accounting purposes. 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). The first distribution line assigns 500.00 of the check to an expense account named Rent. The second distribution line assigns 50.00 of the check to an expense account named Utilities. Here, the amounts are positive because they represent increases to the company’s expenses. Note that the detail amounts and the check amount add up to zero.
  • The import file also contains other information about the check. The check number; (1985) is in the DOCNUM column. A memo to the payee (“April rent”) appears in the MEMO column.
  • The TRNSTYPE column identifies the type of transaction—check, invoice, bill, etc. The keyword to use for checks is ✓. 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. When neither is the case, QuickBooks creates the accounts when you import the transactions, making each account a bank account. 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. To see the entire example, you may have to widen this Help window. 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. Here are the first nine columns:

!TRNS TRNSTYPE DATE ACCNT NAME AMOUNT DOCNUM MEMO CLEAR
!SPL TRNSTYPE DATE ACCNT NAME AMOUNT DOCNUM MEMO CLEAR
!ENDTRNS               N
TRNS INVOICE 3/31/94 Accounts Receivable Lisa Yee 669.62 679 Light fixtures N
SPL INVOICE 3/31/94 Sales Income   -456.92   Light Fixtures N
SPL INVOICE 3/31/94 Labour Income   -175   Labor N
SPL INVOICE 3/31/94 Sales Tax Payable Tax Board

&Fedsalestaxagency;

 -37.70   Sales tax N
ENDTRNS                

Here are the remaining columns. If we had not split up this example, these columns would appear to the right of the first nine columns:

TOPRINT ADDR1 ADDR2 ADDR3 DUEDATE TERMS PAID SHIPDATE INVTITLE INVMEMO EXTRA
  INVITEM                  
                     
Y Lisa Yee 541 Surf Road Sycamore, CA 4/30/94 1%10 Net 30 Y 3/31/94 Invoice Thank you! AUTOSTAX
  Resale                  
128 Labor                  
  Auto Tax                  
                     

Notes

  • This example could have been created in either a spreadsheet or a text file. In a spreadsheet, each item of information has a cell of its own. In a text file, tab characters separate the data columns.
  • The invoice is made out to Lisa Yee at 541 Surf Road, Sycamore, CA. 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). 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). 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). The negative amounts are necessary for QuickBooks internal accounting purposes. If you viewed this invoice in QuickBooks, the line item amounts would all be positive. 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. The payment terms for the invoice (1%10 Net 30) appear under TERMS. A message to the customer (“Thank you!”) appears under INVMEMO (QuickBooks will print this message at the bottom of the invoice). A notation that the invoice is yet to be printed (Y) appears under TOPRINT. All of this is information that QuickBooks stores with the invoice.
  • The TRNSTYPE column identifies the type of transaction—invoice, check, bill, etc. The keyword to use for invoices is INVOICE. 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. When neither is the case, QuickBooks creates the accounts when you import the transactions making each account a bank account. 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. You must enter AUTOSTAX even if you do not charge sales tax.