Export to Excel hangs on a large QuickBooks list: batching and IIF

When a large customer, vendor, or item list hangs, truncates, or returns a blank workbook, we get every record into Excel and prove nothing was dropped.

The Excel button in a QuickBooks Desktop list window builds a worksheet in memory before Excel ever receives it. On a customer, vendor, or item list of several thousand rows, that build can stall the window, stop partway, or hand over a blank workbook with no error at all. Nothing is necessarily wrong with the data. The whole list is simply more than the exporter can assemble in one pass.

Back up first, then work in single-user mode

Create a backup before you change anything: File > Back Up Company > Create Local Backup, then confirm the file exists and carries today's date. Switch the company file to single-user mode (File > Switch to Single-user Mode) and close any other company file windows. Multi-user access does not cause this fault, but it adds contention while you repeat the export many times.

Two housekeeping items save time. Close Excel completely, because a half-open workbook with the same name blocks the export. And check free space on the drive holding the Windows temp folder, since the exporter stages a temporary file there and a full drive stalls it.

Batch a big list into exportable ranges

Note the true record count before you start. A list report carries a total line, and the list window shows a count once a search is applied.

The list window exports everything in the list, so build batches in a report instead. Run Reports > List > Customer Contact List, or Vendor Contact List and Item Listing for the other lists. Open Customize Report > Filters and restrict the report to one slice of the list. Where the Name filter allows a range, use it. Where it does not, slice on Customer Type, Sales Rep, Terms, or for items on Item Type instead, choosing fields that split the list into chunks of a few thousand. Export each slice with the Excel button on the report toolbar and save each file with the slice in its name.

Keep batches small enough to finish in under a minute. If one batch hangs where the previous one did not, you have also located the section of the list behind the original failure.

Write the list to IIF and open it in Excel

The list-to-IIF export writes plain tab-delimited text and never builds a worksheet, so it does not hang the way the Excel button does. Choose File > Utilities > Export > Lists to IIF Files, tick only the list you need, and save the file.

Open it through Data > Get Data > From Text/CSV, or Data > From Text in older builds, set the delimiter to Tab, and skip the header lines at the top of the file. The data block starts at the row containing the list marker: CUST for customers, VEND for vendors, ITEM for items. Load it, then delete the marker rows.

One caution. The IIF file carries every field in the list, including custom fields the list window hides. That makes it a useful diagnostic, and it also means a long memo field containing a tab character can shift a row out of alignment. Check a few rows against the screen before you trust the columns.

Which column is stalling the export?

The list window exports the columns currently displayed and nothing else, which gives you a way to isolate the offender. Right-click any column header, choose Customize Columns, remove half the columns, and export a small batch. If it completes, add columns back in halves until the export stalls. The last column you restored is the one to examine.

Custom fields are the usual suspects, especially long free-text fields and fields where dates were typed in mixed formats. Leave a problem column out of the workbook and export it separately, or clean the values on the records that fail. If a one-row batch hangs with a single column shown, the fault sits in the record or the file, not in the volume.

Did every row arrive in the workbook?

Select the name column and read the Count value in Excel's status bar, then compare it with the count you noted first. Press Ctrl+Down to see where the data actually ends, and press it again if the cursor stops early on a blank cell. Look for a repeated header row partway down, a run of blank rows before the end, and a last record that stops well short of the alphabetical end of the list.

Older versions still write an .xls workbook, which cannot hold more than 65,536 rows, so a long list truncates without any warning. Spot-check three or four records you know well, custom fields included, against the on-screen record. A workbook that passes those checks is safe to work from.

Problems that batching cannot reach

If even a single record refuses to export, or Verify Data reports errors on the list, the company file is the cause and splitting the list will not help. A Pro or Premier list approaching the 14,500 name limit also misbehaves everywhere, not only at export. Our QuickBooks list reduction service covers that situation, and where list records themselves are damaged, QuickBooks company file repair is the route back to a list that exports cleanly.

Batching is a workaround for size. It is not a repair, and it will not make a damaged list behave.

Keep going

Your Desktop doesn’t have to end when Intuit says so.

Start with the master survival guide, or jump straight to the fix you need.