Extract Transactions from PDF Bank and Credit Card Statements files
PDF (Portable Document Format) is a widely used file format for documents that need to be shared and viewed on various devices and platforms. However, having transaction details as PDF makes them impossible to access in Excel or import into accounting or personal finance software as Quickbooks, Quicken, Xero and other.
To resolve this issue, you can use the ProperConvert app that extracts transactions from bank and credit card statement files in PDF format.
Still, many banks prepare QIF, OFX, QFX, QBO, and other formats for you on demand. Look for the ‘Accounting’ section or call your bank and ask them how to generate and download these files for you. At least, they should provide a CSV format. You can convert CSV/Excel/TXT files to OFX, QFX, QBO, QIF, MT940 format using the converter app.
Convert PDF to QBO and import into Quickbooks (desktop or online)
For Quickbooks Desktop QBOnline, convert PDF to QBO (Web connect) format. QBO files are Web Connect files and are imported under Bank Feeds, where you match transactions to vendor records, assign expense and income account and add them to the register.
Convert PDF to IIF and import into Quickbooks Desktop
For Quickbooks Desktop older than three years (Quickbooks after sunset), convert PDF to IIF. IIF files are imported directly into the register. You can rename payees to vendors and assign expense/income accounts (categories) during conversion right in the converter app.
Convert PDF to QFX and import into Quicken for Windows or Mac
For Quicken for Windows or macOS, convert PDF to QFX (web connect) format. Quicken offers the renaming tools (the categorization feature) during QFX import.
Convert PDF to QIF and import into Quicken for Windows
For Quicken for Windows, convert PDF to QIF and import as alternative format. All Quicken versions are supported. Rename payees and assigned categories during import.
Convert PDF to OFX and import into Xero, Sage, Wave Apps, MS Money, and other accounting apps
For online and desktop accounting and persional finance apps, convert PDF to OFX format. Many accounting tools support OFX import.
Convert PDF to CSV and work with transactions in Excel
Extract transactions from PDF file as CSV or Excel format and work with them in Excel, Google Sheets or any other spreadsheet software.
Convert PDF to MT940 and import into your accounting software
Some accounting software import MT940 format only, convert PDF to MT940 and import accounting software like MoneyBird with such capabilities.
An Example: How to convert PDF to QBO for Quickbooks Desktop
This tutorial shows you how to convert a PDF file with transactions to QBO (Web Connect format) and import it into QuickBooks Desktop. PDF statement files have transaction data you need to enter in Quickbooks and extracting this data should help you save time and data entry effort.
To begin, you will need to download and install the ProperConvert app. Once the app is installed, you can start using it to convert your PDF files.
Step 1. Extract transactions from a PDF File
In this tutorial, we have a PDF file containing transactions from Chase Bank from 2016. Our goal is to convert this file into QBO Web Connect format, which can be easily imported into QuickBooks Desktop.
First, open the ProperConvert app on your computer. Once the app is open, you will see a file selection screen. Locate and select the PDF file you want to convert.
When you open this PDF file first, look at your PDF statement and look at how transactions are parsed, check the dates are correct, the month/day/year is correct.
If you need to correct some dates, let's say if the year is incorrect - you can correct, using 'Set year' control.
Or if you have like over-year and statement: some transactions for one year, some transactions for another year, and you need to adjust this - you can select transactions for the previous year, for example, like this, and then right-click and adjust a year.
And if you have a large PDF file, you want to convert specific pages only - then you would use this panel here for different settings, such as 'Pages' from-and-to, you can select from 1 to 2, or 2 to 3 - to limit the number of pages parsed.
You can set 'PDF Password' to be reused for multiple documents, you don't have to enter it over and over, you can specify date format order if you need to ('Input Dates' option). In most cases, this is not required to be set but sometimes is required.
If you have a scanned or image-based, or protected PDF file, then you would use this 'OCR' option - so the parser first will render the PDF file as an image, then tries to read it to optical character recognition. Then, it would parse extract text pieces.
If you have a file, that was partially image, partially text, and you see, it's not parsing correctly if you just parse it - the selected 'OCR' option would give you better results.
Next to 'OCR', or used with 'OCR' - is 'Resolution'. Some PDF documents give you better results with lower resolution, some PDF documents give you better results with high resolution. So, you try for your specific situation and see what works better for you. And if you change any of these settings, then you would click the 'Apply' button to parse the PDF file.
Step 2. Set the QBO File Attributes
Now, we need to provide some additional information to ensure proper conversion. Set the Account ID and Account Type according to your requirements. In our case, we are converting transactions from a Chase Bank account, so we will select the appropriate values for Chase Bank.
- Account ID (actual account number or any other number)
- Account Type (Checking or credit card the most common)
- File Type (Quickbooks US)
- INTU.BID value
To find the correct bank label INTU.BID, enter "Chase" in the search field. Look for the value that contains "CH" and "CC" marks, as this indicates support for checking accounts and credit card accounts. Select this value to proceed with the conversion.
If you prefer more control over the conversion process, you can uncheck the "Open After Save" option. This allows you to create the QBO file first and then manually import it into QuickBooks Desktop. If you want a more automated approach, leave this checkbox checked. The QBO file will be created and QuickBooks Desktop will be called automatically to import the file.
Once you have made your selection, click on the "Convert" button to initiate the conversion process.
Step 3. Import the QBO File into QuickBooks Desktop
After the QBO file is created, open QuickBooks Desktop and navigate to "File Utilities" > "Import" > "Web Connect Files". Select the QBO file you just created.
Now, you have two options. You can either use an existing QuickBooks account or create a new one specifically for the imported transactions. For this tutorial, let's select "New Account" to create a separate account for the transactions.
There is should be a message after the QBO file is successfully imported.
Once the import is complete, you will need to review the imported transactions. This involves matching vendor records, selecting expense and income accounts, and adding the transactions to the register. The process is similar to reviewing transactions downloaded directly from your bank.
Step 4. Alternative to Step3: Import into Quickbooks Online
We then return to QuickBooks Online, navigate to "Bank Transactions," and select "Upload Transactions." After choosing and confirming our bank, we proceed with the import process.
This procedure mirrors downloading transactions directly from your bank, making subsequent steps identical, whether importing from a bank or using a QBO, OFX, or CSV file. Now, you can categorize transactions, assign vendors, and add transactions to the register.
By following these steps, you can convert a PDF file with transactions into QBO Web Connect format and import it into QuickBooks Desktop or Quickbooks Online.
Example: 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 Xero accounting system.
Step 1. Start the ProperConvert app and load a PDF ilfe
Start the app, select a PDF to process and load the file. Review parsed transactions and confirm the transactions details are correct like dates, amounts, amount signs, descriptions and check numbers.
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.
Step 2. Select CSV layout for Xero.
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.
Step 3. Import created CSV into Xero.
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 to how many statement lines were imported, and click 'Ok'.
For the next step, 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.