Skip to main content

How to Convert Excel to IIF to Import Journal Entries into QuickBooks

· 6 min read
Debits

Introduction​

Importing journal entries from Excel into QuickBooks can save time and reduce errors, especially when managing a large volume of financial data. However, QuickBooks Desktop requires the data to be in a specific IIF (Intuit Interchange Format) to import journal entries directly. This guide will walk you through the steps to convert your Excel file to an IIF file, making it compatible with QuickBooks.

Why Use IIF Files to Import Data into QuickBooks?​

  1. Streamlined Data Import: IIF files are formatted specifically for QuickBooks, allowing you to import journal entries without manual data entry.
  2. Time-Saving: Instead of entering each journal entry by hand, you can import multiple transactions at once.
  3. Error Reduction: Automated imports minimize the chance of data entry mistakes.
  4. Compatibility: IIF files are supported by QuickBooks Desktop, making them ideal for importing lists, journal entries, and other transactions.

Steps to Convert Excel to IIF for Journal Entry Import​

Step 1: Prepare Your Excel File​

The first step is to prepare your Excel file for conversion. Ensure that the file is formatted correctly, as this will help avoid errors during the import process.

1.1: Organize Data in Excel​

  1. Open your Excel file: Ensure that your journal entries are arranged with appropriate headers.
  2. Create the Necessary Columns: You’ll need columns such as Date, Account, Amount, Memo, Class, and Type. The exact headers may vary based on your QuickBooks requirements.
  3. Format Dates Correctly: Use the format MM/DD/YYYY for dates, as QuickBooks may not recognize other formats.
  4. Separate Debits and Credits: In QuickBooks, each line in a journal entry must specify whether it is a debit or a credit. Ensure that these are clearly labeled in your Excel sheet.

Sample Layout of Excel File​

DateAccountDebitCreditMemo
01/01/2023Sales Revenue500.00Sales entry
01/01/2023Bank500.00Deposit entry

Step 2: Save the Excel File as a CSV​

QuickBooks doesn’t allow direct imports from Excel (.xlsx) files for journal entries, but it can read IIF files. The conversion from Excel to IIF usually involves an intermediate CSV file.

  1. Go to File > Save As: In Excel, select File and choose Save As.
  2. Choose CSV Format: From the file format options, select CSV (Comma delimited) (*.csv).
  3. Save the File: Save your file in an easily accessible location, such as your Desktop.

Step 3: Convert CSV to IIF Format​

Once your data is in a CSV format, you need to convert it to an IIF file. There are two main methods to do this: using IIF conversion tools or manually formatting the file.

Method 1: Use an IIF Conversion Tool​

Several online tools and software programs can convert CSV files to IIF format. Some popular ones include:

  • IIF Transaction Creator: This tool helps convert CSV files to IIF and is compatible with QuickBooks Desktop.
  • CSV2IIF Converter: Another tool that helps in converting CSV files into QuickBooks-compatible IIF files.

Steps to Convert Using a Tool:

  1. Open the Tool: Access the IIF conversion tool you’ve chosen.
  2. Upload the CSV File: Import your saved CSV file into the tool.
  3. Map Fields: Follow the tool’s instructions to map your CSV columns to QuickBooks fields (e.g., Date, Account, Debit, Credit).
  4. Convert to IIF: Once mapped, click Convert or Export to save the file as an IIF format.

Method 2: Manually Format CSV to IIF​

If you prefer not to use a conversion tool, you can manually create an IIF file. This method requires some familiarity with IIF formatting.

  1. Open a Text Editor: Open Notepad or another plain text editor.

  2. Copy and Paste Data: Copy the data from your CSV file and paste it into the text editor.

  3. Add Headers: Add QuickBooks IIF headers, such as !TRNS for transaction headers and !SPL for split lines.

  4. Format According to IIF Standards: Ensure that each line follows QuickBooks' IIF format. A typical IIF file layout for journal entries may look like:

  5. Save the File as .IIF: After formatting, save the file with an .IIF extension (e.g., JournalEntries.IIF).

Step 4: Import the IIF File into QuickBooks​

Now that your file is in IIF format, you can import it into QuickBooks.

  1. Open QuickBooks Desktop: Make sure you’re logged in as an administrator.
  2. Go to File > Utilities > Import > IIF Files: This will open the IIF import dialog.
  3. Select Your IIF File: Browse to the location where you saved the IIF file, select it, and click Open.
  4. Review the Imported Data: Once imported, review the transactions in QuickBooks to ensure everything was imported correctly.

Step 5: Verify Imported Journal Entries​

After the import, verify that all journal entries were imported correctly.

  1. Go to Chart of Accounts: Access your accounts in QuickBooks.
  2. Open Relevant Accounts: Check the accounts involved in your journal entries to confirm that the debits and credits match.
  3. Run a General Ledger Report: Go to Reports > Accountant & Taxes > General Ledger to see a summary of all entries, including the imported ones.

Tips for Successful IIF Imports​

  • Backup Your QuickBooks File: Always create a backup before importing IIF files. If anything goes wrong, you can easily restore your data.
  • Check for Duplicate Entries: Importing the same IIF file multiple times can lead to duplicate entries, so make sure to import each file only once.
  • Use Proper Account Names: The account names in the IIF file must match those in QuickBooks exactly, or the import will fail.
  • Test with a Small Sample: For your first import, consider testing with a small batch of entries to ensure the process works as expected.

Troubleshooting Common Issues​

Import Errors​

  • Problem: QuickBooks shows an error message during the import.
  • Solution: Double-check that the IIF file is formatted correctly and that all required fields (like Date, Account, and Amount) are filled in.

Missing Transactions​

  • Problem: Not all transactions from the IIF file appear in QuickBooks.
  • Solution: Ensure there are no typos or mismatched account names in the IIF file. Verify that you’re using the correct IIF format.

Duplicate Entries​

  • Problem: Duplicate journal entries appear after importing.
  • Solution: Restore your QuickBooks backup and re-import the IIF file. Be cautious not to import the same file multiple times.

Conclusion​

Converting an Excel file to an IIF file allows you to import journal entries into QuickBooks efficiently. By following these steps—preparing your data, converting it to IIF, and importing it into QuickBooks—you can save time and reduce errors in your accounting records.

For businesses that regularly handle bulk data entries, understanding the IIF format and how to use conversion tools can be highly beneficial. Always remember to back up your QuickBooks data before any import, and verify entries after importing to ensure accuracy.