Excel Export Accounting: XLSX or CSV for Client Data

For an Excel export of accounting data, use XLSX when people will review the file and CSV when software will import it. XLSX keeps several sheets and formatting; CSV keeps plain text and nothing else. Both break the same way when Excel opens them: dates convert, leading zeros drop, long numbers round and payee names split or garble.

Should accounting exports be XLSX or CSV?

Accounting exports should be XLSX when the file is for people and CSV when the file is for another program. An XLSX workbook holds several sheets, formatting and formulas in one file. A CSV file holds one table of plain text, separated by commas, which almost every accounting import screen accepts and almost no reviewer enjoys reading.

What CSV keeps and loses

Microsoft's article Save a workbook to text format (.txt or .csv) says that when you save a workbook in any text format, "all formatting will be removed," and that the CSV format "saves only the active sheet." Microsoft's article Import or export text (.txt or .csv) files adds that Excel warns some features might be lost, and that each sheet has to be saved to its own file. A three-tab workpaper saved as CSV becomes one tab of values.

What XLSX keeps and risks

XLSX keeps the structure a reviewer needs: a summary sheet, a detail sheet and notes in one file, with column widths and number formats intact. The risk is the reverse of CSV's: an import screen that expects a plain table may not read a workbook with merged headers, subtotals and several tabs.

A rule of thumb

Keep two files for the same data when both audiences exist. Use the XLSX as the reviewed copy and a CSV generated from the final sheet as the import copy. Never edit the CSV by opening it in Excel and saving it again without checking the columns described below.

A worked example of the two-file hand-off

This example uses a fictional bookkeeping client, Harbor Lane Studio, and a fictional firm, Example CPA. The firm extracts March transactions for the checking account ending XXXX-4821. The reviewer works in an XLSX file with three sheets: the transaction list, a sheet of open questions for the client, and a tie-out of opening balance, total deposits, total withdrawals and closing balance against the statement. Once the questions are answered and the tie-out agrees, the reviewer saves only the transaction sheet as CSV for import.

The CSV holds one row per transaction and a fixed set of columns. Before upload, the reviewer opens it in a text editor and checks three things: dates are written one way throughout, the vendor ID column still has its leading zeros, and no payee name containing a comma has spilled into the next column. The workbook stays in the client's folder as the reviewed record; the CSV is disposable and can be generated again.

Questions to ask about any export

Who will open it next, a person or a program? Does it need more than one table? Will anyone edit it before it moves on? A person, several tables or edits point to XLSX. A program, one table and no edits point to CSV.

What does Excel do to dates and account numbers on open?

Excel converts data when it opens a CSV file. Microsoft's article Set automatic data conversions lists four conversions, including removing leading zeros, truncating numbers to 15 digits, and turning strings into dates, and says these "can occur" when "opening a .csv or .txt file." That is why account numbers and dates change before anyone types.

Leading zeros and long numbers

Microsoft's article Keeping leading zeros and large numbers says Excel "automatically removes leading zeros, and converts large numbers to scientific notation," and that its precision limit is 15 significant digits, so digits past the fifteenth are rounded down to zero. An example routing-style code 021000089 opens as 21000089. An example 16-digit card number ending 4821 opens in scientific notation, and the last digit becomes a zero.

Dates

The Set automatic data conversions article names "converting a continuous string of letters and numbers to a date" as one of the conversions. The Import or export text article says that when Excel opens a .csv file directly, "it uses the current default data format settings to interpret how to import each column." A date written day first can be read month first, or left as text, depending on those defaults. Microsoft's article Convert dates stored as text to dates covers the left-aligned text dates that result. The post on bank statement date formats in Excel walks through fixing them column by column.

Turning the conversions off

The Set automatic data conversions article says the options sit under File, then Options, then Data on Windows, and under Excel, then Preferences, then Edit on a Mac, in Excel for Microsoft 365 and Excel 2024. The same article says these options do not directly affect data imported with Power Query. For a file you receive once, importing it with Data, then From Text/CSV, and setting columns to Text is the safer route; the Keeping leading zeros article describes setting columns to Text in Power Query.

Exporting QuickBooks Online transactions to Excel

Exporting QuickBooks Online transactions to Excel starts from a report. Intuit's article Export reports to Excel gives the path: Reports, then Standard reports, select the report, then Export/Print and Export to Excel. A clean transaction list comes from a transaction-level report with only the columns you need.

Shape the report before export

Intuit's article Customize reports says to open the report, select Customize, apply filters such as accounts or customers, choose which rows and columns show, and then select Save customization or Save as. Do the trimming in QuickBooks, not in Excel, so the saved report produces the same columns next month.

Exporting many reports at once

Intuit's article Export your QuickBooks Online data describes Settings, then Export data, where you pick a date range, switch reports and lists on or off, and select Export to Excel. It says the reports and lists arrive "as Excel files in one .zip file." The same article points to Custom Report Builder, available in QuickBooks Online Advanced, for more specific exports.

When the file looks empty

The Export reports to Excel article says that if data is missing when the report opens, "the file's in protected view," and you need to select Enable Editing in Excel to see the full report. Check this before assuming the export failed.

Keep the export as XLSX for review

The export arrives as an Excel workbook. Keep it that way for review. If the data later has to go into another system as CSV, save a copy from the final sheet and check the columns in the table below before importing.

Character encoding and commas in accounting CSV exports

Character encoding and commas cause most garbled CSV files. Encoding is how characters such as é, ñ and £ are stored as bytes; when Excel reads the bytes with the wrong encoding, a payee name turns into symbols. Commas inside a field, such as "Smith, Jones LLP", split one value into two columns when the field is not quoted.

Encoding

Microsoft's article Opening CSV UTF-8 files correctly in Excel says a CSV file encoded with UTF-8 opens normally "if it was saved with BOM (Byte Order Mark)." Otherwise it gives two routes: Data, then Get Data, then From file, then From Text/CSV through Power Query, or the Get Data From Text option, which opens the Text Import Wizard. An example payee, Café Example Ltd, shows correctly only when the file is read as UTF-8.

Commas and quotes

The CSV format standard, RFC 4180, says "fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes," and that a double quote inside a quoted field "must be escaped by preceding it with another double quote." A program that writes Smith, Jones LLP without quotes produces a row with one column too many, and every value to the right shifts.

Six common corruptions, with the cause and the fix

When an Excel export is the wrong hand-off to accounting software

An Excel export is the wrong hand-off when the next step is an import screen. Import screens read plain CSV columns, and each time Excel opens and saves the file it can change dates, zeros and long numbers. When a file goes from one program to another, keep it CSV end to end.

Inspect the CSV as text

A text editor shows the file exactly as the import screen will read it: every comma, every quote and every date as written. Open the CSV there, not in Excel, to check a column before upload. If a value has to change, change it in the source report or the reviewed XLSX and generate the CSV again, rather than patching the CSV in Excel and saving over it.

Bank transaction imports

Intuit's article Format CSV files in Excel to get bank transactions into QuickBooks accepts a 3-column layout of Date, Description and Amount, or a 4-column layout of Date, Description, Credit and Debit, and asks that dates be in one format. It also says "Mac users must save the file as a Windows CSV file." The posts on importing bank statements into QuickBooks and importing bank transactions into QuickBooks Online cover the full upload.

Rows to remove first

A bank CSV often carries header blocks, opening balance rows and totals that an import screen will misread. The post on which rows to delete from a CSV before QuickBooks sees it lists them.

Where the data starts matters

The cleanest CSV is one written once, by the tool that read the source document, and never reopened in Excel before import. Schema CPA reads statements, receipts, invoices and tax forms and exports the results to Excel or CSV, so a firm can keep the XLSX for review and the CSV for import; the Free plan includes Excel and CSV export, as the pricing section lists. It does not connect to QuickBooks or Xero, so the import itself happens in those tools. More conversion guides sit in the bank statements to Excel category.