Importing transactions to QuickBooks

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 print this Help topic, press imageP.

To import transactions to QuickBooks:

  1. If the transaction data is already in a file, open that file. 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). If you are using a word processor, it must be capable of saving the file as a straight text file without embedded formatting codes. In either case, your end result will be a file in which tab characters separate blocks of transaction data.

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

      !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. Do not type “<tab>”.

  3. 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 <tab> TRNSTYPE <tab> ACCNT <tab>... etc.
      !SPL <tab> TRNSTYPE <tab> ACCNT <tab>...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 etc.). 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, etc.). 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 have to have a Terms column in the import file.

      Only three keyword columns are absolutely 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.
  4. Enter the specific details of each transaction. (Again, use the two examples as a model.)

    In general, follow these steps:

    • Start at the beginning of the first row or line after !ENDTRANS. This would be 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 in 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 in 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 in a text file, press Tab after ENDTRANS.
    • 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 in 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 would wait until you have imported the transactions into QuickBooks. Then, you would 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.

    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 posted to... 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.
  6. 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.
  7. In QuickBooks, choose File > Import > From IIF Files.
  8. Enter the location and name of the import file.
  9. Click OK to import the transactions.

Sample IIF files you can download for individual transactions

To download sample IIF files for individual transactions:

  1. Go to www.quickbooks.com/support/faqs/qbw2003/117503.html.
  2. Click the link of the sample file you want to download.
  3. Follow the instructions in your browser for downloading the file.
  4. In Finder, drag the downloaded file to your StuffIt Expander application.

    The compressed file expands to an IIF file and a readme file.

    You can download a free version of StuffIt Expander from www.stuffit.com.

  5. In QuickBooks, choose File > Import > From IIF Files.
  6. Enter the location and name of the IIF file.
  7. Click OK to import the transactions.