Intuit Intuit HomeIntuit Products
nav_top_golden_seal QuickBooks
Order Status My Account Shopping Cart
 
Import (and Export) Your QuickBooks Data

If you have files that contain important business information about your customers, vendors, or sales, there are ways to get that data into QuickBooks without retyping it. Similarly, you can also export your QuickBooks data into another program.

The applications that work best for importing QuickBooks data are spreadsheets, including Microsoft Excel files (.xls), comma-separated value files (.csv), and any other tab-delimited text file that conforms to QuickBooks' import file format.

IF YOU USE: YOU CAN IMPORT DATA:
QuickBooks 2004 or 2005
(and Microsoft Excel '97 or later)
  • Directly from Microsoft Excel (.xls), comma-
        separated value (.csv), or other tab-delimited
        text file
  • QuickBooks 2003 or earlier
  • Directly from comma-separated value files
        (.csv)
  • Indirectly from Microsoft Excel files, by
        converting them first to .csv files

  • Before you click the Import button, there's a little bit of learning and setup to do. After you've imported once, it becomes faster and easier. Follow the five steps below to ensure that you import your file correctly, transfer the data properly, and avoid errors.

    1. Step 1: Export a list to understand file formatting.
      QuickBooks imports and exports text files. The best way to understand how to format your files for importing them into QuickBooks is to (1) export some of your QuickBooks lists, and (2) view the resulting file in a spreadsheet, such as Microsoft Excel. That way, you'll see the data laid out into rows and columns. If you don't have a spreadsheet program, you can also view an export file in a word processing program such as Microsoft Word or Corel WordPerfect. If you don't already have a QuickBooks company, you can export lists from the sample data file (sample_product-based business.qbw or sample_service-based business.qbw).

      Here's how to export a QuickBooks list:

      1. From the File menu, choose Export, and then Lists to IIF Files.
      2. In the window that appears, select a list for QuickBooks to export, for example, the Vendor list.
      3. Click OK.
      4. Enter a filename for the export file, and select a location to store it.
      5. Click Save. QuickBooks will tell you if your data was exported successfully.
      6. Click OK.
      7. Open the export file in a spreadsheet program, such as Microsoft Excel, or a word-processing program such as Microsoft Word.

      When you view the file, you'll see capital-letter keywords that serve as row and column headings. The keywords at the beginning of each row identify the type of information in that row. For example, the keyword CUST indicates that the row contains data about a customer on your Customer:Job list.

      Rows that begin with the same keyword are grouped together. For example, all of the CUST entries from your Customer:Job list appear as a single block of rows in the file. Each block, in turn, has its own column headings that appear in capital letters in the cells of the first row in the block. The column headings identify the type of information that each column in the block contains.


    2. Step 2: Preparing your data for import into QuickBooks.
      It's important that you format your Excel or .csv file data properly before you import it into QuickBooks. Advance preparation reduces the likelihood of errors and makes the import process cleaner and faster.

      Here's how to format your spreadsheet file data:

      1. Open the spreadsheet that contains the data.
      2. Move the contents of all the cells down by one row so that the first row is blank.
      3. In that blank row, above each column of data, type in the appropriate header for the type of information in the column. For example:
        • Customer = CUST
        • Vendor = VEND
        • Account = ACCNT
        • Item = INVITEM
        For a complete listing of all the headers you'll need to use in order to import your data properly, go to your QuickBooks Help and type "Reference Guide to Import Files." From there, follow the links in the table to find the headers you're looking for. Or, refer to the list you exported in Step 1, above.
      4. Check the structure of the spreadsheet. Each type of list (account, customer, vendor, and item) should be in a separate spreadsheet or in a separate sheet inside a master workbook. For example, if you have a list of customers and a list of vendors, all the customer data should be in one sheet and all the vendor information should be in another sheet.


    3. Step 3: Map the data from your file to QuickBooks.
      Mappings tell QuickBooks how to import the Excel or .csv data into your company data file. Once you create a mapping, you can save it to use any number of times later.

      You only need to map required fields in order to save a mapping. Mapping other fields is optional.

      1. From the File menu, choose Import, then Excel Files.
      2. Select the Excel or .csv file you want to import. You can enter the path to the file or browse to it.
      3. Select a sheet (at the bottom of the window). If your worksheet contains only one page, select Sheet 1.
      4. From the "Choose a mapping" drop-down list, select Add New.
      5. Give your new mapping a name.
      6. Select an import type.
      7. In the "QuickBooks" column, select the row of information you want to map.
      8. In the "Import data" column, click the drop-down button, and then select the column in the import file that should be mapped to the QuickBooks column.

        Here's an example:

        Mapping


        In this example, the QuickBooks column displays the fields available to use in QuickBooks. The Import data column displays the information contained in the import file that's being mapped. Using the mapping, you define the way your data will be imported into QuickBooks.
      9. Repeat this process until you've mapped all the columns in the file you're importing to QuickBooks fields.
      10. Click Save.


    4. Step 4: Preview (and correct) the imported data.
      You can preview your import and make corrections to it before you actually bring the data into QuickBooks.

      Here's how:
      1. Map your import file. (Step 2, above.)
      2. Click Save.
      3. Click Preview. The Preview window will appear.
      4. From the "In data preview show" drop-down list, choose whether you want to see all the data or only errors.
        • If you select "all data," records that will be imported successfully are marked as "OK." Records that will not be imported successfully are marked as "Error."
        • If you select "only errors," records that will not be imported successfully are marked as "Error" and no other records are displayed.

        Data Preview

      5. In the "Data Preview" section, click on the record that contains the error to highlight it. The "Details for" section displays the details about the highlighted record.
      6. In the Details for section, find the line that contains an error message. In the example above, the error is in Column C of the import file, and the error is "Invalid Account Type."
        • In this example, the QuickBooks "Type" cannot be numeric and must be an account type. Because the Type is numeric, QuickBooks detected an error.
      7. In the Data column, highlight the data that needs to be corrected and enter the new data. In this example, a correct entry would be "Income."
      8. If the new data is okay, the error status changes to "OK."
      9. Continue correcting errors as necessary.
      10. Click Import.

      If there are any problems with the import, QuickBooks will generate an error log.


    5. Step 5: Import the data.
      Now that you've completed steps 1, 2, 3, and 4, you're ready to import your data. Here's how:

      1. From the File menu, choose Import, then From Excel Files or From IIF Files.
      2. At the Set up Import tab, choose the file you want to import.
      3. If there are multiple sheets in your import file, select the sheet you want to import. If there's only one sheet, select Sheet 1.
      4. Specify whether the file you're importing has header rows.
      5. From the drop-down list, choose a mapping. You can choose an existing mapping or create a new mapping.
      6. At the Preferences tab, set your preferences.
      7. (Optional) Click Preview to see how the data will be imported into QuickBooks.
      8. Click Import.


      QuickBooks Solutions Marketplace
      If you need to regularly import and export data, one of the QuickBooks-integrated business software applications in our QuickBooks Solutions Marketplace may meet your needs. If you're running QuickBooks Pro or Premier 2002-2005, or QuickBooks Enterprise Solutions, your QuickBooks can share data with over 350 popular business software applications. Learn more at the QuickBooks Solutions Marketplace.

      ProAdvisor Import/Export Tips and Tricks
      We had some QuickBooks ProAdvisors tell us what they've learned over the years about importing and exporting data to and from QuickBooks. Read the resulting article, ProAdvisor Importing and Exporting Tips and Tricks, in this issue of QuickBooks E-News.


      Back to Top

    Terms and conditions, features, support, pricing and service options subject to change without notice.
    Privacy Statement