 |
Import spreadsheets: Boost Accuracy and Avoid Double Data Entry
Importing your Microsoft® Excel- or .csv-based spreadsheet into QuickBooks can increase the accuracy of your QuickBooks company file, and saves you time by avoiding redundant data entry.1 QuickBooks lets you import spreadsheet data in four areas:
- Customers
- Vendors
- Accounts
- Items
Note: While importing items creates items in your Items list, it does not create a corresponding increase in your inventory value.
Fortunately, with QuickBooks importing is a snap. By following the steps outlined below QuickBooks will help you:
- Prepare your spreadsheet file for import into QuickBooks
- Map the data from your file to QuickBooks
- Preview and correct imported data
- Import the file
Note: QuickBooks Help includes additional instructions on importing data into QuickBooks. If you have additional questions we suggest you consult those files.
Importing from an Excel or other .csv spreadsheet into QuickBooks follows four main steps:
Step 1: Prepare your data for import into QuickBooks.
Before importing, spend a few minutes formatting your Excel or .csv spreadsheet data for QuickBooks. Preparation reduces errors and makes the importation process cleaner and faster.
Quick Tip: To quickly understand how QuickBooks imports spreadsheet data, try exporting some of your QuickBooks lists. Then review the resulting file in a spreadsheet. If you have not yet set up your company file, export lists from the sample data files (see "Transferring reports, lists, and registers to another program" in Help for more on exporting).
To prepare your spreadsheet for importing, follow these steps:
- Open the spreadsheet containing the data.
- Move the cell contents down one row so that the first row is blank. In that blank row, above each column of data, create a header that follows the field recommendations for each import type:
- Check the spreadsheet's structure. Each list type (account, customer, vendor, item) should reside in a separate spreadsheet or in a separate sheet inside a master spreadsheet.
For example, if you have a list of customers and a list of vendors, all the customer data should be in one worksheet and all the vendor information should be in another worksheet.
Step 2: Map the data from your file to QuickBooks.
After preparing your spreadsheet data for importation, you next save a mapping of the data from your file to QuickBooks. Mappings defines how QuickBooks will import the Excel or .csv data into your company data file.
Note: You need to map only required fields to save a mapping. Mapping other fields is optional.
Follow these steps to map your data:
- From the File menu, choose Import, then choose Excel Files.
- Select the Excel or .csv file you want to import. You can enter the path to the file or browse to it.
- Select a sheet. If your worksheet contains only one page, select Sheet 1.
- From the "Choose a mapping" drop-down list, select Add New.
- Give your new mapping a name.
- Select an Import Type (account, customer, vendor, or items).
Table 1. An Import type template lets you map QuickBooks fields to your import data. The table shows the four QuickBooks Import types and some of the fields available in each.
| Account |
Customer |
Vendor |
Items |
| Number |
Job or Customer Name |
Name |
Type |
| Name |
Opening Balance |
Account Type |
Name |
| Description |
Company Name |
Account Number |
Description |
| Bank Acct. No./Card No. |
Salutation |
Credit Limit |
Tax Code |
| Opening Balance |
Contact |
Print on check as |
Income Account |
| As of |
Phone |
Tax ID |
Expense Account |
| Income Account |
Fax |
Vendor eligible for 1099 |
On Hand |
| Account is Inactive |
Email |
In Inactive |
Cost |
| Remind me to order checks... |
Billing Address 1 |
Terms |
Preferred Vendor |
- In the "QuickBooks" column, select the row of information you want to map.
- Now, in the "Import data" column, using the drop-down button, select which column in the import file should be mapped to the QuickBooks column.
- Repeat this process until the mapping is complete.
- Click Save.
Quick Tip: By saving your mapping, you will save time the next time you import your spreadsheet data.
Example Mapping:
In the example above the QuickBooks column displays the fields available in QuickBooks. The import data column displays the information contained in the import file that's being mapped. Using the mapping feature, you define the way your data will be imported into QuickBooks.
Step 3: Preview and correct imported data.
To smooth out the importation, try previewing your spreadsheet data before actually importing into QuickBooks. Previewing also lets you correct problems directly in the Preview window.
Preview the import
- Map your import file (see above).
- Click Save.
- Click Preview.
- From the "In preview data 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.
Correct errors in the Preview window
- Map your import file (see above).
- In the Data Preview section, highlight the record containing the error. The Details For section displays the details about the record.
- In the Details For section, find the line containing an error message. In the screenshot example below, 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, an error is detected.
- In the Data column, highlight the data that needs to be corrected, then enter the new data. In this example, a correct entry would be "Income."
- If the new data is OK, the "Error" status changes to "OK."
- Continue correcting errors as necessary.
- Click Import (see below for more)
If there are any problems with the import, an error log will be generated.
Step 4: Import the file.
The last step: import your spreadsheet data into QuickBooks:
- From the File menu, choose Import, then choose Excel Files.
- At the Set up Import tab, choose the file you want to import.
- If there are multiple sheets, select the sheet. If there's only one sheet, select "Sheet 1."
- Specify whether the file you're importing has header rows (the first row containing the column headings).
- From the drop-down list, choose the mapping you created above.
- At the Preferences tab, set your preferences.
- Click Import.
Congratulations, you're done!
Step 5: Appendix: Field recommendations.
The following tables show the fields and the kind of data allowed in each field for each of the four import types:
Customer
Use the column headings below when you prepare your file for importing Customer data into QuickBooks. For the data contained in the rows below the column headers, follow the guidelines in the table when preparing or entering the data.
Table 2. Column headings for Customer data.
| JOB OR CUSTOMER NAME (Required) |
Enter the name of the Customer (and Job, if needed) as it appears in QuickBooks. Example: Kristy Abercrombie: Bathroom Remodel. Note: If you are importing a child (or sub) entry for a parent (or main) entry, the parent entry must already exist in order for the child entry to be imported correctly. |
| OPENING BALANCE |
No "$". Enter the opening balance. You cannot enter an opening balance for an existing customer, only for a new one. |
| OPENING BALANCE AS OF |
Enter a date. The date must be entered as MMDDYYYY. You cannot set an opening balance "as of" date for an existing customer, only for a new one. |
| COMPANY NAME |
Enter a company name (maximum 41 characters long). |
| SALUTATION |
Enter a salutation such as "Mr.," "Mrs.," or "Dr." |
| FIRST NAME |
Enter the customer's first name. |
| MIDDLE INITIAL |
Enter the customer's middle initial. |
| LAST NAME |
Enter the customer's last name. |
| CONTACT |
Enter the contact name for the customer. |
| PHONE |
Enter the customer's phone number. |
| FAX |
Enter the customer's FAX number. |
| ALTERNATE PHONE |
Enter the customer's alternate phone number. |
| ALTERNATE CONTACT |
Enter the alternate contact name for the customer. |
| EMAIL |
Enter the customer's e-mail address. |
| BILLING ADDRESS 1 through BILLING ADDRESS 5 |
Enter the customer's billing address (maximum of 41 characters). |
| SHIPPING ADDRESS 1 through SHIPPING ADDRESS 5 |
Enter the customer's billing address (maximum of 41 characters). |
| CUSTOMER TYPE |
Enter a QuickBooks customer type. |
| SALES REP |
Enter a QuickBooks sales rep. |
| PREFERRED SEND METHOD |
Enter a QuickBooks preferred send method. Example: E-mail. |
| TAX CODE |
Enter a three-character tax code. To view your tax codes, from the Lists menu choose Sales Tax Code List. |
| TAX ITEM |
Enter a QuickBooks tax item. |
| RESALE NUMBER |
Enter a resale number. |
| PRICE LEVEL |
Enter a QuickBooks price level. |
| ACCOUNT NUMBER |
|
| CREDIT LIMIT |
Enter a credit limit. |
| PREFERRED PAYMENT METHOD |
Enter a preferred payment method. To view your choices, from the Lists menu, choose Customer & Vendor Profile Lists and then Payment Method List. |
| CREDIT CARD NUMBER |
Enter the customer's credit card number, appended with a single quotation mark ('). |
| CREDIT CARD EXPIRATION MONTH |
Enter the month as two digits, i.e., May = "05." |
| CREDIT CARD EXPIRATION YEAR |
Enter the year as four digits. |
| NAME ON CARD |
Enter a name. |
| CREDIT CARD ADDRESS |
Enter an address. |
| CREDIT CARD ZIP CODE |
Enter a zip code. |
| JOB STATUS |
Enter a job status. To view your choices, from the Edit menu, choose Preferences. Select Jobs & Estimates in the Scroll box and click the Company Preferences tab. |
| JOB START DATE |
Enter the start date. |
| JOB PROJECTED END |
Enter the projected completion date. |
| JOB END DATE |
Enter the actual end date. |
| JOB DESCRIPTION |
Enter information about the job. |
| JOB TYPE |
Enter a job type. To view your choices, from the Lists menu, choose Customer & Vendor Profile Lists and then Job Type List. |
| IS INACTIVE |
Enter "Yes," or "No," "Active," or "Not-Active." |
| NOTE |
Enter a note about the customer. |
Vendor
Use the column headings below when you prepare your file for importing Vendor data into QuickBooks. For the data contained in the rows below the column headers, follow the guidelines in the table when preparing or entering the data.
Table 3. Column headings for Vendor data.
| NAME (REQUIRED) |
The name of the vendor. |
| OPENING BALANCE |
No "$". Opening balances can be set for new vendors only, not existing vendors. |
| OPENING BALANCE AS OF |
Enter a date. The date must be entered as MMDDYYYY. You cannot set an opening balance "as of" date for an existing vendor, only for a new vendor. |
| COMPANY NAME |
Enter a company name (maximum of 41 characters). |
| SALUTATION |
Enter a salutation. |
| FIRST NAME |
Enter the vendor's first name. |
| MIDDLE INITIAL |
Enter the vendor's middle initial. |
| LAST NAME |
Enter the vendor's last name. |
| ADDRESS 1 through ADDRESS 5 |
Enter address information (maximum of 41 characters). |
| CONTACT |
Enter the contact name for the vendor. |
| PHONE |
Enter the vendor's phone number. |
| FAX |
The vendor's FAX number. |
| ALTERNATE PHONE |
Enter the vendor's alternate phone number. |
| ALTERNATE CONTACT |
Enter the alternate contact name for the vendor. |
| EMAIL |
Enter the vendor's e-mail address. |
| PRINT ON CHECK AS |
Enter the vendor's name as you'd like it to print on a check (maximum of 41 characters). |
| ACCOUNT NUMBER |
Enter the vendor's account number. |
| VENDOR TYPE |
Enter one of the QuickBooks vendor types. |
| TERMS |
Enter one of the QuickBooks terms. |
| CREDIT LIMIT |
Enter the vendor's credit limit. |
| TAX ID |
Enter the vendor's Tax ID number (maximum of 9 characters). |
| VENDOR ELIGIBLE FOR 1099 |
"Yes" or "No." |
| IS INACTIVE |
"Yes" or "No" "Active" or "Not-Active." |
| NOTE |
Enter a note or "to do" for your customer. |
Account
Use the column headings below when you prepare your file for importing Account data into QuickBooks. For the data contained in the rows below the column headers, follow the guidelines in the table when preparing or entering the data.
Table 4. Column headings for Account data.
| ACCOUNT TYPE (REQUIRED) |
The type of account: |
| |
Accounts payable |
|
| |
Accounts receivable |
|
| |
Bank |
|
| |
Credit card |
|
| |
Cost of goods sold |
|
| |
Equity |
|
| |
Other expense |
|
| |
Other income |
|
| |
Expense |
|
| |
Fixed asset |
|
| |
Income |
|
| |
Long term liability |
|
| |
Other asset |
|
| |
Other current asset |
|
| |
Other current liability |
|
| ACCOUNT NUMBER |
The account number. |
| ACCOUNT NAME (REQUIRED) |
The name of an account in your chart of accounts. Note: If you're importing a child (or sub) entry for a parent (or main) entry, the parent entry must already exist in order for the child entry to be imported correctly. |
| DESCRIPTION |
A brief description of the account. |
| BANK ACCT. NO/CARD NO./NOTE |
The account number. |
| OPENING BALANCE |
The opening balance of the account. |
| AS OF (DATE) |
A date. |
| REMIND ME TO ORDER CHECKS |
When you use the check number you enter here, you'll be reminded to order checks. |
| TRACK REIMBURSED EXPENSES |
Enter "Yes" or "No." |
| INCOME ACCOUNT FOR REIMB. EXPENSES |
Enter the name of the income account you use to track the above reimbursed expenses. |
| ACCOUNT IS INACTIVE |
Enter "Yes", "No", "Active", or "Not-Active." |
Item
Use the column headings below when you prepare your file for importing Item data into QuickBooks. For the data contained in the rows below the column headers, follow the guidelines in the table when preparing or entering the data. The NAME, TYPE, and ACCOUNT columns are required.
Table 5. Column headings for Item data.
| TYPE (REQUIRED) |
Enter a QuickBooks item type. |
| NAME (REQUIRED) |
Enter the item's name. Note: If you're importing a child (or sub) entry for a parent (or main) entry, the parent entry must already exist in order for the child entry to be imported correctly. |
| REIMBURSABLE CHARGE |
Enter "Yes" or "No." Note: if the charge is for services performed by someone else, the item type should be "Service Item." If the charge is for a reimbursable expense, the item type should be "Other charge." |
| DESCRIPTION |
Enter the item's description. |
| TAX CODE |
Enter a three-character tax code. To view your tax codes, from the Lists menu, choose Sales Tax Code List. |
| ACCOUNT (REQUIRED) |
Enter an Account name. |
| EXPENSE/COGS Account |
Enter an expense account name from your chart of accounts. To view the Chart of Accounts, from the Lists menu, choose Chart of Accounts. |
| ASSET ACCOUNT |
Enter an asset account name from your chart of accounts. To view the Chart of Accounts, from the Lists menu, choose Chart of Accounts. |
| DEPOSIT TO (ACCOUNT) |
Enter a bank account name from your chart of accounts. To view the Chart of Accounts, from the Lists menu, choose Chart of Accounts. |
| DESCRIPTION ON PURCHASE TRANSACTIONS |
Enter a text description. |
| ON HAND |
Enter an amount. |
| COST |
Enter an amount. |
| PREFERRED VENDOR |
Enter a vendor's name. |
| TAX AGENCY |
Enter the name of a tax agency from the Vendor list. To view it, from the Lists menu, choose Vendor List. |
| PRICE/AMOUNT or %/RATE |
Enter a price or rate. Keep in mind that you can't use percentages when setting a price for inventory items or items whose expense will be passed on to the customer. In those cases, use a dollar amount. |
| ITEM IS INACTIVE |
Enter "Yes," or "No," "Active," or "Not-Active." |
| REORDER POINT |
When your inventory item reaches this number, QuickBooks will remind you to reorder the item. |
| TOTAL VALUE |
Note: This is for inventory items only. QuickBooks calculates this amount for you by multiplying the number of items on hand times the cost of each item. If you want, you can manually enter a different number. |
| AS OF (DATE) |
Enter a date. |
| PAYMENT METHOD |
Enter a QuickBooks payment method. |
|
|
|
|
|
|
1 Excel integration requires the following Microsoft Excel 2000, 2002 or 2003, applications sold separately.
Back to Top
|
|
|
