To clean a CSV before importing to QuickBooks, delete every line that is not a transaction, such as titles, balances, totals and pending items. Then put all dates in one pattern, turn amounts into plain signed numbers, trim the descriptions, and remove the running balance column. Save it as CSV and check it in a text editor before upload.
Which rows should you delete from a CSV before QuickBooks sees it?
The rows to delete from a CSV before QuickBooks sees it are every row that is not one posted transaction. That covers title lines above the header, beginning and ending balance lines, subtotals and totals, repeated header rows, blank lines and pending items. QuickBooks reads each line below the header as a transaction, so anything else causes errors.
Intuit's article Manually upload transactions into QuickBooks Online sets the upload limit at 1,000 lines, one transaction per line. A line that holds a total or a balance is still a line, so it counts against the limit and can show up as a transaction.
Here is an example bank download before cleaning: eight lines for a fictional client, Example Client LLC, with a Harbor Federal checking account ending XXXX-4821. Names, numbers and amounts are examples.
Harbor Federal Credit Union Checking XXXX-4821 June 2026
Date,Description,Amount,Running Balance
06/01/2026,BEGINNING BALANCE,,"12,480.55"
2026-06-03,"POS PURCHASE EXAMPLE CAFE #0412 06/02","($18.75)","12,461.80"
06/05/2026,ACH DEPOSIT RIDGELINE STUDIO,"$2,950.00","15,411.80"
06/09/2026,CHECK 1047,(400.00),"15,011.80"
06/30/2026,PENDING EXAMPLE FUEL,(52.10),
,TOTAL,"$2,531.25",
And the same file after cleaning:
Date,Description,Amount
06/03/2026,POS purchase Example Cafe,-18.75
06/05/2026,ACH deposit Ridgeline Studio,2950.00
06/09/2026,Check,-400.00
Four of the eight lines went: the title, the beginning balance, the pending fuel charge and the total. None of them was a posted transaction. The header stayed, minus one column. The three remaining transactions were fixed in place. The sections below explain each change.
The ten-item cleanup checklist, in the order to run it:
- Save a copy of the original download and work on the copy.
- Delete title and summary lines above the header row.
- Delete beginning balance, ending balance, subtotal, total, repeated header and blank lines.
- Delete pending rows.
- Use the running balance to check the file, then delete that column and any others QuickBooks does not use.
- Put every date in one pattern.
- Turn amounts into plain signed numbers, with blanks instead of zeros.
- Trim and simplify descriptions.
- Check the date range against what is already in QuickBooks.
- Save as CSV, then open the saved file in a text editor to confirm it.
Fixing dates and amounts in the CSV before importing
Fixing dates and amounts in the CSV before importing means making every date follow one pattern and every amount a plain number with a sign. Intuit's manual upload article asks for one consistent date format. Its article Common errors for importing bank transactions using CSV lists currency symbols and commas in amounts as a formatting error.
In the example, the second transaction's date read 2026-06-03 while the others read 06/01/2026. That kind of mix appears when two downloads are pasted together or when Excel reformats part of a column. The fix is to pick one pattern and convert the stragglers. Intuit's common errors article suggests setting the date column's number format to Short Date in Excel. Whatever pattern you end with, choose the same one on the QuickBooks mapping screen.
Text dates need converting before formatting will work. If some dates are stored as text, changing the number format does nothing to them. Microsoft's Top ten ways to clean your data covers converting text-based dates and numbers stored as text back into values Excel can format.
Amounts in the example had three problems:
- Currency symbols and commas. "$2,950.00" became 2950.00. Intuit's common errors article says to change the column's format from Currency to General.
- Parentheses. "($18.75)" and "(400.00)" became -18.75 and -400.00. In the 3-column layout, Intuit's article Format CSV files in Excel to get bank transactions into QuickBooks shows payments as negative amounts.
- Zeros. None here, but Intuit's formatting article says to remove zeros and leave the cell blank.
The accepted layout for each column, including the 4-column Credit and Debit option, is in the post on which columns a QuickBooks Online bank CSV requires.
Should you keep the running balance column in a QuickBooks CSV?
A QuickBooks CSV should not keep the running balance column. QuickBooks Online accepts a file with either 3 or 4 columns, per Intuit's manual upload article, and a running balance is neither a date, a description nor a transaction amount. Left in, it is one more column to map or, worse, a column mapped to Amount by mistake.
Use the balance before you delete it. A running balance is the account balance after each row, so it gives a free check on the file. Start from the opening balance, add each transaction, and the result should equal the balance printed on the last row. In the example, 12,480.55 plus 2,950.00, minus 18.75, minus 400.00 equals 15,011.80, which matches the final running balance. Every posted row is present and correctly signed.
If the check fails, a row is missing, doubled or signed wrong. Find the first row where your own running total and the bank's figure disagree; the problem is on that row or the one before it. Fix it now, while the bank's balance is still in the file to compare against.
Intuit's common errors article says to delete CSV columns you do not need. The running balance is the most common one, followed by check numbers, reference numbers, type codes and bank-assigned categories.
Trimming CSV descriptions so QuickBooks rules match
Trimming CSV descriptions so QuickBooks rules match means removing extra spaces, store numbers, dates and codes from the description field. Bank rules in QuickBooks Online match on the description, so a description that changes slightly every month can slip past a rule that should catch it.
Intuit's article Set up bank rules to categorize online banking transactions in QuickBooks Online says a rule's conditions can test the description with Contains, Doesn't contain or Is exactly. An Is exactly rule for "POS purchase Example Cafe" will not match "POS PURCHASE EXAMPLE CAFE #0412 06/02," because the spacing, the store number and the date all differ.
Intuit's own upload guidance pushes the same way. Its manual upload article says to remove numbers from description cells, and its common errors article says to remove special characters, suggesting Ctrl+H to find and replace them.
In Excel, three steps handle most of it:
- Extra spaces. Microsoft's TRIM function "removes all spaces from text except for single spaces between words." Use
=TRIM(B2)in a helper column, then paste the results back as values. Microsoft notes TRIM does not remove nonbreaking spaces on its own. - Codes and numbers. Find and replace patterns such as "#0412" and the trailing date. In the example, "CHECK 1047" became "Check." Keep the original download, which still holds the check number.
- Case. Microsoft's Top ten ways article covers the LOWER, UPPER and PROPER functions for standardizing capitalization, which makes descriptions easier to read during review.
Trim with a light hand. The description is also what the reviewer reads in the For review list. Cut the noise, but leave the payee name and enough context to recognize the transaction.
CSV cleanup traps: multi-month files and overlapping date ranges
A CSV that looks clean can still cause trouble when it covers several months at once or overlaps transactions QuickBooks already has. A multi-month file can pass the 1,000-line limit or hide one bad month. An overlapping date range creates duplicates, which no amount of row cleaning will prevent.
Overlap is the bigger risk. Intuit's article Fix duplicate transactions in QuickBooks Online Desktop bank feeds lists importing a file that overlaps transactions already in the bank feed as one cause of duplicates. Intuit's video guide How to upload transactions manually says that for an account with existing transactions, the download should start the day after the last one.
Before saving, filter the date column and note the first and last dates. Then open the account in QuickBooks and check the last transaction already there. If the account has a feed, the post on the bank feed versus a CSV upload in QuickBooks explains how far back the feed reaches, which tells you where the file should end.
Multi-month files have two smaller traps:
- Size. Intuit's manual upload article sets a 350 KB and 1,000-line limit and says to shorten the date range for large uploads. A year of a busy account will not fit in one file.
- Hidden gaps. A file built from several monthly downloads can be missing one. Sorting by date and looking for a month with no rows takes a minute and catches it.
Splitting by month or quarter solves both. Each file is smaller, and each one can be checked against its own statement balances.
If duplicates already made it into QuickBooks, the post on duplicate transactions after a QuickBooks import covers finding and removing them.
Saving the cleaned CSV without Excel undoing your work
Saving the cleaned CSV without Excel undoing your work means saving once as CSV and not reopening the file in Excel by double-clicking it. Excel reinterprets each column when it opens a CSV, so dates and number formats you fixed can change again. Check the saved file in a text editor instead.
Microsoft's article Import or export text (.txt or .csv) files says saving as CSV saves only the current worksheet and that some features are lost.
A safe save routine:
- Paste helper columns as values and delete the formulas, so the saved file holds results, not references.
- Keep one sheet. Only the active sheet is saved to CSV, so make sure it is the cleaned one.
- Save as CSV. On a Mac, Intuit's formatting article says to save as a Windows CSV.
- Check in a text editor. Confirm the header, the date pattern and the minus signs look the way you left them.
- If you must reopen in Excel, use the import route Microsoft describes and set the date column's format by hand.
What Excel does to dates and leading zeros on open, and when to keep an xlsx copy alongside the CSV, is covered in the post on whether accounting exports should be xlsx or csv.
When the starting point is a stack of PDF statements rather than a download, the file has to be built before it can be cleaned. Schema CPA reads bank statements, ties each month's balances to the next, points out a missing month, and exports the data as CSV or Excel. The firm still runs the checklist and does the QuickBooks upload, since Schema CPA has no QuickBooks connection. The pricing section of the homepage lists which plans include statement reconciliation. More on getting statement data into a spreadsheet is in the bank statements to Excel category.