How to make CSV for Xero from a PDF statement

This tutorial shows how to convert a PDF file downloaded from your Bank or Credit Card account and save it as a CSV file with layout compatible with zero accounting system. We will use PDF2CSV utility.

We will use a sample.pdf file to convert data.

How to make CSV for Xero from a PDF statement Step 1: sample pdf

It is a Chase statement PDF file, it has some deposits and some withdrawals.

How to make CSV for Xero from a PDF statement Step 2: chase

PDF2CSV converter shows Date, Amount, Withdrawal, Deposit. The Date is specifically shown as a month, as a name in this video. This is just to review transactions. There are three different formats: how dates are on PDF file, how dates inside the software, how dates outside. It is easy to get confused which format is shown. So the converter always shows inside, always shows dates as a year/month (as name)/date. So dates are properly parsed from the PDF file.

How to make CSV for Xero from a PDF statement Step 3: review pdf2csv

From PDF file you see, that there is no year, only month and date (07/02 may be as of July,2, as of Feb,7). So the converter looks for dates and if it sees the date in a certain way, like in this example, the date starts to be clear, that it is July, this is month/date format and the converter shows that this is all July dates.

How to make CSV for Xero from a PDF statement Step 4: chase date

PDF Converter tries to look in the PDF file on the statement for the year. The distinctive indication of year, like in this example, it determines 2016 and it shows dates with the 2016 year because dates year is missing in the Chase. Some PDF files, where there is no clear indication, what the year is it, you can control the year manually by setting it. You can basically set the year you like.

How to make CSV for Xero from a PDF statement Step 5: set year if missing

There are different CSV layouts, CSV generic formats. The Regular CSV layout, that will just save all the columns you see inside the application. Also there are: Excel file - the same, XLSX Excel layout - recent XLSX format, XLS Excel - old XLS format, CSV layout for Quickbooks Online, CSV layout for Xero, Quicken for Mac (CSV Mint layout), POSH layout, Clipboard layout - the same as CSV layout, you can copy it without saving the file, copy to Clipboard and then you start Excel and just paste it.

How to make CSV for Xero from a PDF statement Stepb6: csv target

We will use the CSV Xero. The Date Format for CSV Xero could be month/day/year or day/month/year, as day suggests on the help page, depending on your country settings. The Converter looks at your country setting and uses as month/day or day/month suggesting the format, but you can set it the way you like. We will use month/day/year for Xero.

How to make CSV for Xero from a PDF statement Step 7: dates for xero

The checkbox 'Open after conversion' will make the converter to open CSV file after a conversion, so you can review it before importing into your accounting system.

How to make CSV for Xero from a PDF statement Step 8: open after conversion

You can check the Payee and the Description. And you can see if it's correct, if it's what you expect. What does it do for the PDF file? It takes the Description from the PDF file and tries to remove all Description noise and just leave only Payee, related information, details or Payee name. In this case (this is not a good example), but let's say that Description has some Date, some Numbers, some Frasers, like Deposit, POS. So all this stuff is removed for Payee.

How to make CSV for Xero from a PDF statement Step 9: payee, description

To create a CSV file, first, select a Target, confirm Date format and then click 'Convert' button.

How to make CSV for Xero from a PDF statement Step 10: convert

Then create a CSV file, offered with the same name, as a PDF file, you can change it to what you like.

How to make CSV for Xero from a PDF statement Step 11: save data to

And then in the related form click 'Ok', open Xero and import the CSV file.

How to make CSV for Xero from a PDF statement Step 12: related form

We can use Notepad to look at the CSV file.

How to make CSV for Xero from a PDF statement Step 13: open with notepad

The converter tries to call the system and open the CSV file with whatever App you have installed. If you have Excel install and it register to open the CSV file, so then Excel will start with CSV file open. In this example, there is no Excel install, so Notepad could be still ok to review the CSV file. As you see, the date format, as specified in this example is month/day/year (07/02/2016). It's not month name, it's not year/month/day, it is a format, you specified.

How to make CSV for Xero from a PDF statement Step 14: csv in notepad

The file is saved and the last thing is to import into Xero. We have a test account in Xero. Click 'Accounts' - 'Bank Accounts'.

How to make CSV for Xero from a PDF statement Step 15: test account in xero

Let's say we have a Checking account. Click 'Manage Account' - 'Import a Statement'.

How to make CSV for Xero from a PDF statement Step 16: import a statement

It says, that it supports a CSV file. Then click 'Browse' for CSV, select a sample-bank-statement.csv, open it and click the 'Import' button.

How to make CSV for Xero from a PDF statement Step 17: open a sample csv

The right column is the names from the CSV file and the left column is the names inside Xero. Select Check Number is 'Check No.' and Reference is 'Reference' if you have it. When you have CSV or Excel with additional information, in other product, like CSV2CSV and you want to create a CSV layout compatible with Xero, then you would Map 'Reference' column and use it here. Check 'Don't import the first line because they are column headings' and click 'Save'.

How to make CSV for Xero from a PDF statement Step 18: reference

Before you click 'Save', 'Convert', 'Import' pay attention, that 14 statement lines were imported, 0 were duplicates and click 'Ok'.

How to make CSV for Xero from a PDF statement Step 19: 14 statement lines were imported

Now it shows that it is Jul 2, 2016, the Amount, the Deposit. Now we have to process it inside the application, like Map it to Proper Account, select Proper Vendor Name. It is a usual process in the accounting system after you import any file with transactions. Quickbooks called it 'Mapping', Xero called it 'Review'. Before you just have done those transactions into your system, it usually goes to some intermediate place, when you review imported transactions and add them to register.

How to make CSV for Xero from a PDF statement Step 20: reference

Related artciles