Import Credit Card Transactions into QuickBooks

Quick Answer: Connect the card in QuickBooks if the issuer supports it; that covers recent activity. For anything the connection misses, QuickBooks Online accepts an uploaded CSV, QBO or OFX file through Bank transactions > Link account > Upload from file, and QuickBooks Desktop imports a .QBO Web Connect file. Check that charges arrive as charges, and record the card payment only once.

Importing credit card transactions into QuickBooks comes down to one of three routes: a live connection to the card issuer, a file you upload to QuickBooks Online, or a Web Connect file you import into QuickBooks Desktop. Each route lands the charges in the card's review list, where you categorize them. The two mistakes that cost the most time are flipped signs and the card payment counted twice.

Which import route fits your situation?

The connection is the least work when it works. The file routes exist for everything else: history from before you connected, months when the connection broke, and issuers or business cards that QuickBooks can't reach.

How do charges, payments and refunds map to signs?

Before uploading anything, understand how a file represents money on a card. A purchase is money going out on your behalf. A payment to the card and a merchant refund both reduce what you owe.

In a bank-style file, the convention is that charges are negative and payments and refunds are positive, the same way a debit card purchase would look on a checking account. Card issuers' CSV downloads don't all follow it. Some print purchases as positive numbers because, from their side, a purchase increases your balance. Some split the amounts into separate Debit and Credit columns.

That matters because QuickBooks Online's upload expects either three columns (Date, Description, Amount) or four columns (Date, Description, Credit, Debit), according to Intuit's guide to manually uploading transactions. If your file's signs are the reverse of what QuickBooks assumes, every purchase will show up in the review list as a payment.

A quick test: before you import a whole year, upload one statement, then look at a purchase you recognize. If it sits in the payments side or shows as money received, cancel and flip the signs in the amount column (multiply by -1 in a spare column, paste as values) before trying again.

Importing into QuickBooks Online

Intuit's upload rules, from the page linked above:

• File types: .csv, .txt, .qbo or .ofx.
• Up to 1,000 lines per upload, one transaction per line.
• The file must be in English and 350 KB or less.
• Dates in one consistent format throughout. Intuit recommends dd/mm/yyyy.
• Cells in Amount, Credit or Debit that contain only a zero should be left blank.

The path is All apps > Accounting > Bank transactions. Select the card's tile if it exists, open the Link account dropdown and choose Upload from file. Drop the file in, pick the credit card account from the QuickBooks account list, map the columns if it is a CSV, choose which rows to bring in and confirm. Everything lands in the For Review tab for that card, where you match or categorize it; the categorizing guide covers that step.

Coming from Excel

If your charges live in a spreadsheet, save a copy with just the date, description and amount columns, delete any totals or blank rows, and use File > Save As > CSV. Long years should be split into chunks under 1,000 rows. A .QBO file skips the column-mapping step and carries the account type, which is why Intuit suggests it.

Importing into QuickBooks Desktop

Desktop's bank feeds don't take a CSV for card transactions. Its file route is Web Connect: open the File menu, choose Utilities, then Import, then Web Connect Files, select the .QBO file and tell QuickBooks which card account it belongs to. Intuit's page on Web Connect import errors is blunt about the requirement: the .qbo file is "the only type Web Connect can read." Desktop also asks you to sign in with your Intuit account before it will import one.

After the import, review the transactions in the Bank Feeds Center, assign payees and accounts, and add them to the register.

So "import credit card transactions into QuickBooks Desktop from Excel" really means building a .QBO from the spreadsheet rows. Paid add-ons that import spreadsheets into Desktop as journal entries also exist, but they post entries directly instead of going through the review step, so check their documentation before relying on one. For rows that only exist on PDF statements, QuickBankConvert reads the statement and writes a .QBO file on its paid plans; you then import that file through Web Connect like any other. If QuickBooks rejects a file, the free QBO file checker shows what is inside it.

How do you import an Amex statement into QuickBooks?

American Express is the most common case people search for. Amex's customer service page says you can download your recent activity and past six billing statements to Quicken, QuickBooks or as a CSV through your online account, using the options on the left side of the statement page (Amex help).

That gives you three practical options:

1. Connect the Amex card inside QuickBooks for ongoing activity.
2. Download the QuickBooks file from the Amex statement page for any of the last six statements, then upload it to Online or Web Connect it into Desktop.
3. For older periods, start from the Amex PDF statements. Amex statements for business cards often list charges grouped by cardholder; keep each employee card's charges in the same QuickBooks card account unless you track them separately.

The Amex statement to Excel guide covers the PDF side, and the American Express bank page describes how those statements are laid out.

How do you avoid counting the card payment twice?

This is the error that makes a card balance in QuickBooks disagree with the issuer by exactly one payment.

When you pay the card from checking, the payment appears in two feeds: as money out of checking, and as a payment received on the card. Both are the same event. Categorize them as one transfer:

• In the checking account's review list, the payment should be recorded as a transfer to the credit card account, not as an expense.
• In the card's review list, the matching payment should then be matched to that transfer, not added as new.

If you add both as new transactions, the card is credited twice, its balance drops below what you owe, and the bank side may show a phantom expense. The same thing happens when you import old card statements after the checking account already holds the payments: the imported payment rows need to be matched, or excluded, not added.

Refunds are different. A merchant refund only appears on the card, so you add it as a credit to the original expense category.

A pre-import checklist

• Decide the start date: the day after the last transaction already in QuickBooks for this card.
• Make sure the file covers only that range, so the connection's rows and the file's rows don't overlap.
• One file per card account; don't mix two cards in one upload.
• Confirm the sign convention with a single statement first.
• Keep the file under Intuit's size and line limits.
• After import, compare the card's QuickBooks balance with the closing balance on the last statement in the file.

That last check leads straight into reconciliation, which the credit card reconciliation guide walks through, including what to pick on the payment screen afterwards.

Getting rows out of PDF statements

Card issuers keep PDF statements far longer than they offer downloadable activity. When the months you need only exist as PDFs, you have a few choices: retype them, copy the tables out with Excel's Get Data from PDF and clean the result, or use a converter. QuickBankConvert is one such converter: it reads the PDF on its servers and returns one row per transaction with the date, description and a signed amount, with charges as negative numbers. CSV and Excel downloads come with a free account, while the .QBO file sits on the paid plans listed on the pricing page. Whichever method you use, compare the total of the rows with the statement's purchases and credits before importing.

Related guides

• Reconcile a credit card in QuickBooks
• Import bank transactions into QuickBooks Online
• Credit card statement to Excel
• Convert Amex statements to Excel or CSV