BooksReady

Import a Shopify Payments transactions CSV into Xero

In short: Shopify Payments' transactions export lists every charge, refund and adjustment with Amount, Fee and Net in the payout currency, the payout status, and the payout date that settled it. Xero's statement import uses its own CSV template: a Date and a signed Amount on every line, plus Payee, Description, Reference and a cheque number column. Money out is negative. Drop the file below. It is converted on your device and never uploaded.

Convert your file

Shopify Payments to Xero (CSV)

payment_transactions_export.csv

Your file stays on this device

It is read and converted by this page, inside your browser. Nothing from the file is uploaded, and the converter keeps working offline once the page has loaded.

One email when it ships. The address is used for nothing else. Details.

1. Export the file from Shopify Payments

  1. In Shopify admin open Finances, then Payouts.
  2. Open View transactions (or a single payout) and choose the dates.
  3. Click Export and download the CSV.
  4. Upload the file here.

Why a raw Shopify Payments export fails in Xero

Xero would treat each Shopify charge as money in a bank account that never received it. Import the activity into a Shopify clearing account in Xero, where payouts then show as transfers to your bank.

Xero imports the CSV line by line, so title rows above the table, balance rows, currency symbols in the amount, or separate gross, fee and net columns end up rejected or imported as the wrong amount.

What is unusual about the Shopify Payments file

  • Columns: Transaction Date, Type, Order, Card Brand, Card Source, Payout Status, Payout Date, Available On, Amount, Fee, Net; newer files add Currency, Presentment Amount and more.
  • Amount is in the payout currency, Fee is positive and Net is Amount minus Fee.
  • Transaction Date carries the store time zone offset; the store's own date is used.
  • Rows marked pending have not been paid out yet.
  • Each payout is identified by its payout date (or Payout ID when the file has one).

What Xero requires

  • Header matches Xero's template: *Date, *Amount, Payee, Description, Reference, Check Number.
  • One signed amount column: money out negative, decimal point, no symbols.
  • Up to 100,000 rows per file.

2. Before and after

Before: the Shopify Payments transactions CSV as downloaded

Transaction Date,Type,Order,Card Brand,Card Source,Payout Status,Payout Date,Available On,Amount,Fee,Net,Checkout,Payment Method Name,Presentment A...
2023-10-31 12:00:42 +0100,charge,#3099,master,online,pending,2023-12-01,2023-11-03,2636.49,107.33,2529.16,#42675990692187,card,113.98,USD,CZK,0.00
2023-10-20 09:00:00 +0200,charge,#3080,visa,online,paid,2023-11-01,2023-10-22,1000.00,40.50,959.50,#42600000000001,card,1000.00,CZK,CZK,0.00
2023-10-14 07:07:50 +0200,refund,#3055,master,online,paid,2023-11-01,2023-10-16,-738.51,0.00,-738.51,#42569948889435,card,29.90,EUR,CZK,0.00
2023-10-13 23:59:59 +0200,adjustment,,,,paid,2023-11-01,2023-10-16,-12.00,0.00,-12.00,,,,,CZK,0.00
2023-10-10 10:00:00 +0200,charge,#3050,visa,online,paid,2023-10-12,2023-10-12,500.00,20.25,479.75,#42500000000002,card,500.00,CZK,CZK,0.00

After: what Xero receives

After: shopify-transactions-2023-10-10-to-2023-10-31-xero.csv
*Date*AmountPayeeDescriptionReferenceCheck Number
10/10/2023500.00Shopify charge #3050
10/10/2023-20.25FeeFee: Shopify charge #3050
10/13/2023-12.00Shopify adjustment
10/14/2023-738.51Shopify refund #3055
10/20/20231000.00Shopify charge #3080
10/20/2023-40.50FeeFee: Shopify charge #3080

Synthetic sample data that follows the real export layout, not a real account. The "after" output was produced from it by the same converter this page runs.

3. Import the converted file into Xero

  1. In Xero open Accounting > Bank accounts and choose the account.
  2. Click Manage account > Import a statement.
  3. Choose this CSV and click Import.
  4. Check the column mapping (Date, Amount, Payee, Description, Reference) and confirm the date format if asked.

Questions

Which Shopify export should I use?
For a Shopify Payments balance account in your books, the transactions export from Finances, Payouts. For one journal entry per payout, the payouts export, which has a page of its own here.
Which date format does Xero need?
Xero lists both month-first and day-first dates as accepted and asks you to confirm the format when it is unclear. Choose United States for MM/DD/YYYY or Elsewhere for DD/MM/YYYY, and confirm the same format in Xero if it asks.
What about rows that are still pending?
Rows whose payout status is not paid have not reached your bank yet. They are included in the account, and the notes say how many are still pending.
Can I import more than one month at once?
Yes. Xero imports the whole statement file you give it. Keep each file to one bank account in Xero, and avoid overlapping a period you already imported so you do not have to delete duplicate statement lines.

Other ways to convert Shopify Payments

Other exports to Xero (CSV)