How to make CSV for Xero from a PDF statement [video]

This video shows how to convert a PDF file downloaded from your bank or created card account, saved as a CSV file with layout compatible with Xero accounting system. We use PDF2CSV Converter.

We use a sample.pdf file to convert data and this is to show the pdf file, this is chase statement pdf file, it has some deposits and some withdrawals.

PDF2CSV Converter shows Date, Amount, Withdrawal, Deposit. The date in this view is specifically shown as a month, as a name, this is just to review transactions. How dates are on PDF file, how dates inside the software, how dates outside are three different formats. It is easy to get confused which format is shown. The converter always shows inside dates as a year/month as name/date. So dates are properly parsed from the PDF file. You see here, there is no year, only month and date: 07/02 or it is July, 2 or it is Feb, 7. So converter looks for dates and if it sees a date certain way, like here, dates start to be clear, that it is July, this is month/date format and the converter shows this is all July dates.

PDF Converter tries to look in the PDF file to the statement for the year. The distinctive indication of year, like here, it determines 2016 and it shows dates with 2016 because dates' year is missing here. 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 set the year you like.

Now when we go to CSV file, there are different CSV layouts, CSV generic formats. The Regular CSV layout, that will just save all the columns you see in the application. Also there are: Excel file - the same, XLSX Excel layout - recent XLSX format, XLS Excel - old XLS format, CSV format 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 without saving the file, copy to Clipboard and then you start to excel and just paste it.

Today we use CSV Xero and the Date Format for CSV Xero could be month/day/year or day/month/year, as Xero suggests on their help page, depending on your country settings, what the Converter does, it looks at your country setting and use as month/day or day/month suggesting the format, but you can set it the way you like. We use for Xero month/day/year. This checkbox will make the Converter to open CSV file after the conversion, so you can review it before into your accounting system. You can check the Payee in the Description and see if it's correct, what's you expect. What does it do for PDF file? It takes the Description from PDF file and tries to remove all noise and just leave only Payee, related information, details, Payee name. In this case, this is not a good example, but let say something like Description has some Date, some Numbers, some phrases, like Deposit, POS. So all this stuff is removed for Payee.

To create a CSV file with selected Target and format we click Convert. Then we create CSV file offered with the same name, as PDF file, you can change it what you like and then in the related forms you open Xero and import the CSV file. We gonna click Ok, 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 CSV file open. On my system there is no install, so Notepad could be ok to review the CSV file. As you see, the date format, as I specified here, this is gonna be month/day/year and here this is 07/02/2016. It's not month name, it's not year/month/day, its a format, you specified.

The last thing is to import into Xero. We have some test accounts in Xero and we click Accounts - Bank Accounts - Manage Account - Import a Statement. It says, that it supports a CSV file. Then we Browse for CSV and select sample.csv - open it -Import and it selected everything for us. The right columns - names of CSV files, left - names inside Xero. Select Check Number - Check No., Reference - 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 this column and mute here. Click Don't import the first line because they are column headings - Save. It's important - just a little attention before you click Save, Convert, Import - what you importing, what is mapping. So, 14 statement lines were imported, 0 were duplicates.

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

Related artciles