[HOW TO] Convert pdf bank statement to csv for Xero

Introduction

Ever need to convert your PDF bank statement to CSV for importing to Xero?

Extracting bank transactions out of a PDF can be a pain in backside. Some problems that can come up:

• The date format is not right for Xero,
• The “amounts” are incorrect when copying from the PDF
• Random empty rows and columns that Xero will complain about when importing.

In this post will go over the 4 steps to convert your bank statement to CSV for importing to Xero import.

Step 1: Get your bank statement

Firstly, you will need a copy of your bank statement. You can download it from your bank’s website or app.

In my case, I am using the HSBC bank statement like below (I have redacted the private bits)

Step 2: Convert the PDF to CSV

The second step is to use a tool to convert our PDF bank statement to CSV. We can do this with the free PdfDodo tool:

👉 Use PdfDodo to extract the data to CSV

Now you can extract the bank statements by using PdfDodo.

In my case I was using my HSBC bank statement. Should have the same process with other banks.

Just sign up with an account and you try it out for free.

To do start the CSV conversion:

• Step 1: Get an account with PdfDodo
• Step 2: Go to the home page and click “Upload Document”

• Step 3: After the document has uploaded - click “Start Analysis”. This will usually take a few seconds.

• Step 4: Click on the uploaded document link to see the results.

After PdfDodo has completed analysis, it will list out all the data that it thinks is in a table format.

Now we can see our result and download the CSV or CSV file:

Alternative option: Use Microsoft Word

If you don not want to use an online tool, then another option is Microsoft Word. Since Word 2013, you will have the ability to open up PDFs and convert it to Word.

We can then use this Word document as a intermediate step, then copy/paste the data into Excel. After that you can save it as CSV.

(Phew thats a lot of steps)

1. Launch Microsoft Word.
2. Go to ‘File’ -> ‘Open’, select the PDF file for conversion.
3. Word converts the PDF to an editable document, duration varies with file size and complexity.
4. Post-conversion, edit the content in Word as required.
5. Conversion quality may fluctuate with complex PDFs, some formatting adjustments might be necessary.
6. Select, copy and paste the desired data into an CSV spreadsheet.
7. Some formatting readjustments may be required as the original formatting might not be perfectly retained.

Simpler PDFs work best with this method; for complex files, consider using a dedicated PDF to CSV converter or OCR software. This method might struggle with scanned PDFs, as conversion accuracy may be compromised.

Limitations

• Do I really need to say more? It works with simple text based PDFs and not scanned PDFs
• Data can contain random characters that are unicode so be careful when you are copying and pasting to Excel. I would usually copy this and paste it into notepad first. This way we can see the dodgy unicode characters (if any).
• If Microsoft Word cannot covert, it usually gives you a image instead. So in this case, you will have to enter the data manually yourself!

Step 3: Clean data for Xero

Thirdly, the most time consuming step is to make sure that our CSV is clean and formatted the way that Xero expects. We need to remove extra data, and make sure that our CSV at least has the “Date” and “Amount” columns.

1. Remove extra data

To make sure the import is successful, we need to make sure that we remove the following:

• empty rows,
• bank account numbers
• the bank balance columns that contains the open and closing balances. Xero will do this automatically for us.

2. Prepare the columns

Make sure that we have at the least the “Date” and “Amount” columns. Xero only needs these two columns for it to work. However having more columns helps you map the data easier.

• Date: Make sure that we follow the formats:
  • DD/MM/YYYY (eg 15/01/2023)
  • MM/DD/YYYY (eg 01/15/2023)
  • YYYY/MM/DD (eg 2023/01/15)
• Amount is the debit and credit amounts. If its credit - then use positive number, if debit we use negative.

Optional columns thats not required but can improve your import

• Payee – (Recommended) Make sure to match the name of the payee to be exactly as the contact in Xero. This helps to avoid duplicates.
• Description – Description of the transaction.
• Reference – Transaction reference number Cheque number – Enter the cheque number if applicable.
• Analysis code – Enter the bank’s analysis code to help identify the bank statement line. This is different to the account code the transaction is reconciled to in Xero.
• Transaction Type – Bank’s reference for the transaction type. This only displays on the bank statement line in the reconciliation screen.

Step 4: Import into Xero

After we have cleaned up our CSV, we can then import it to Xero:

1. Navigate to the Accounting menu and select Bank Accounts.
2. Find and click Manage Account next to the bank account you want to import data into, then choose Import a Statement.
3. Under File to upload, either drag and drop your CSV file or click Select file to choose the file from your computer, then click Next.
4. If prompted, assign the columns in your CSV to the matching bank statement fields in Xero, then click Next.
5. If prompted, select the date format used in your bank statement, then click Next.
6. Review the transactions. Uncheck any transactions you don’t want to import.
7. Click Complete import to finalize the import process.

Final thoughts and tips

Overall using PdfDodo can drastically reduce your time extracting the data out of your PDF bank statement.

The remaining time is spent massaging the data so that it fits withing the Xero structure.