How to Export Customer Information from Quickbooks 2011

QuickBooks lets you do lots of cool things with customer info, but say you have a mail merge already set up in FileMaker Pro, or you want to transfer all of your customer records to Access to track product support. You have to export your customer data out of QuickBooks into a file that the other program can read and import.

To extract customer info from QuickBooks, you have three choices:

• Export your customer information directly to Excel if you’re not sure what info you need and you’d rather delete and rearrange columns in a spreadsheet program. QuickBooks exports every customer field. Then, you can edit the spreadsheet all you want and transfer the data to yet another program when you’re done. (The one downside to this approach is that the spreadsheet includes blank columns between each data-filled column.)
  
  
• Create a report when you want control over exactly which fields QuickBooks exports. By creating a customized version of the Customer Contact List report, for example, you can export the same set of records repeatedly, creating delimited files, spreadsheets, and so on.
  
  
• Export a text file of your customer data if you need a delimited text file to load into another program. The delimited file lists each customer in its own row with each field separated by tabs.

The following sections explain all your options.

Exporting to Excel

In QuickBooks, exporting the Customer List to Excel is a snap. To export all the customer data stored in QuickBooks to an Excel file, in the Customer Center toolbar, click Excel, and then choose Export Customer List to open the Export dialog box. The Excel menu also contains commands for exporting transactions and for importing and pasting spreadsheet data into QuickBooks.

The Export dialog box that appears is already set up to create a new spreadsheet. Click the Export button and you’ll be looking at the Customer List in Excel in mere seconds. If you’d rather give QuickBooks more guidance on creating the spreadsheet, click the Advanced tab and then adjust options like Autofit (which sets the column width so you can see all your data) before clicking Export.



Customized exports using the Contact List report

By modifying the Contact List report’s settings, you can export exactly the fields you want for specific customers. For example, storing email addresses in QuickBooks is perfect when you email invoices to customers, but you probably also want them in your email program so you can communicate with customers about the work you’re doing for them. Exporting the entire Customer List is overkill when all you want is the contact name and email address; that’s where exporting a report shines.

Out of the box, QuickBooks’ Customer Contact List report includes the Customer Name, Bill To address, Contact, Phone, FAX, and Balance fields. Here’s how you transform this report into an export tool:

• Choose Reports→Customers & Receivables→Customer Contact List. The Customer Contact List report window opens.
  
  
• In the report window’s toolbar, click Modify Report. The “Modify Report: Customer Contact List” dialog box that appears lets you customize the report to filter the data that you export.
  
  
• Click the Display tab (if you’re not already on it) and, in the Columns section, choose the fields you want to export. The Customer, Contact, Phone, and Fax fields might be good ones to export. Then again, they might not. You can add or remove whichever fields you want by clicking a field’s in the Columns list to toggle that field on or off. If there’s a checkmark in front of the field’s name, the report will include a column for that field; if there’s no checkmark, it won’t.
  
  
• To produce a report for only the customers you want, click the Filters tab. In the Filter list, choose Customer. In the Customer drop-down list that appears, choose “Multiple customers/jobs” to select the customers you want to export.
  
  QuickBooks displays the Select Customer:Job dialog box, with the Manual option selected; that’s what you want. In the list of customer names on the right side of the Select Customer:Job dialog box, click each customer you want to export, and then click OK. Then, in the Modify Report dialog box, click OK.
  
  You see the report with the modifications you’ve made.
  
  
• In the Customer Contact List window’s toolbar, click Export. The Export Report dialog box opens. To create a new Excel workbook, simply click Export. Your computer launches Excel and displays the report in a workbook.

Tip: Saving the modified report reduces the number of steps you have to take the next time you export.

Exporting a text file

To create a delimited text file of the entire Customers & Jobs List (or any other QuickBooks list), choose File→Utilities→Export→“Lists to IIF Files”. The first Export dialog box that appears includes checkboxes for each QuickBooks list. Turn on the checkboxes for the lists you want to export, and then click OK.

Tip: If you want to export only customer names and addresses to a tab-delimited file, choose File→Utilities→Export→“Addresses to Text File”.

The Easy Way to View Data

Data is easier to examine when you view an export or import file with a spreadsheet program like Excel. Most programs can open or import delimited text files, but Excel is a master at reading the records stored in these text files and displaying them clearly. When you export data to a delimited text file and then open it in Excel, the program puts the data into cells in a spreadsheet. Because records and fields appear in neat rows and columns, respectively, you can quickly identify, select, and edit the data you want. Furthermore, you can eliminate entire rows or columns with a few deft clicks or keystrokes.

Here’s how to open a delimited text file with Excel:

• In Excel 2010, click the File tab and then choose Open. In Excel 2007, click the Office button and then choose Open.
  
  
• The delimited text files won’t appear in the Open dialog box at first because they’re not Excel files. Delimited text files come with a range of file extensions (the three characters that follow the last period in the filename), so choose All Files in the “Files of type” drop-down list to make sure you’ll see your delimited file listed.
  
  
• To open the file, navigate to the folder that contains the delimited text file, and then double-click the file’s name. The Text Import Wizard dialog box appears. (A wizard is a series of question-and-answer screens that walk you through a particular process.)
  
  
• As you tell the wizard which characters act as delimiters and what type of data appears in each column, it shows you what the data will look like after it’s imported into Excel. When its interpretation of the data is correct, click Finish.
  
  
• Now, you can use Excel to rename column headings or delete the columns or rows you don’t want to import.