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 Bank2CSV utility. IMPORTANT: Bank2CSV is now replaced with the ProperConvert app, which converts from more formats and converts to more formats.
We will use a sample.pdf file to convert data.
It is a Chase statement PDF file, it has some deposits and some withdrawals.
Bank2CSV 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 a PDF file, how dates are inside the software, how dates are outside. It is easy to get confused about 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.
From the 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.
PDF Converter looks in the PDF file on the statement for the year. The distinctive indication of year, like in this example, determines 2016 and it shows dates with the 2016 year because dates year is missing in the Chase. In some PDF files, where there is no clear indication, what the year is, you can control the year manually by setting it. You can basically set the year you like.
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 files - 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.
We will use the CSV Xero. The Date Format for CSV Xero could be month/day/year or day/month/year, as the 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.
The checkbox 'Open after conversion' will make the converter open the CSV file after a conversion, so you can review it before importing it into your accounting system.
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.
To create a CSV file, first, select a Target, confirm Date format and then click 'Convert' button.
Then create a CSV file, offered with the same name, as a PDF file, you can change it to what you like.
And then in the related form click 'Ok', open Xero, and import the CSV file.
We can use Notepad to look at the CSV file.
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 the 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.
The file is saved and the last thing is to import it into Xero. We have a test account in Xero. Click 'Accounts' - 'Bank Accounts'.
Let's say we have a Checking account. Click 'Manage Account' - '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.
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 products, like CSV2CSV and you want to create a CSV layout compatible with Xero, then you would Map the 'Reference' column and use it here. Check 'Don't import the first line because they are column headings' and click 'Save'.
Before you click 'Save', 'Convert', 'Import' pay attention, that 14 statement lines were imported, 0 were duplicates, and click 'Ok'.
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.
See this tutorial as a video: