How to Import Bank PDF Statements into Microsoft Excel for Free

While most banks now allow importing of bank statements directly into the CSV format, occasionally you may need to convert a PDF of a bank statement into a CSV file that can be opened with Microsoft Excel or Google Sheets.

The following websites can convert PDF files to CSV for free:

• Zamzar
• Nanonets
• Convertio
• Adobe Online (Supports converting an XLSX file that can be saved as a CSV)

Step 1: Converting PDF files to CSV

Step 1a: Using Power Query to import data from a PDF file

Alternatively, on Windows OS, you can directly use Power Query to import data from a PDF file, without having to convert it to a CSV first.  Unfortunately, this option is not available for users on alternative operating systems.

• Use Data > Get Data > From File > From PDF and click Import.
• In the Navigator box, click on the desired item and click Transform Data or if it is clean, you can directly Load to > Excel

Source: https://www.excel-university.com/import-pdf-data-into-excel-with-power-query/

Step 2: Cleaning up your bank activity

• Cleaning up will allow your data to be more concise and easier to read. To clean the bank activity, you will need the Power Query Add-in in Excel. 
• Open a new window and excel. Click on Data 🡪 Get Data (Power Query)

• Under Choose Data Source, select Text/CSV

• Select the CSV file downloaded earlier, then click on Get Data, then click Next.

• In the file data preview, click on Transform Data or Edit (depending on your OS). Your data will then show up in the Power Query Editor, where you can clean it up.

• Right-click on the Column to rename the columns as required.

• Use the Remove Rows option to remove any unwanted data that has been exported from the PDF. A dialog box will appear asking for the number of rows that need to be removed.
• Insert a number and click on OK.

• Next, we will use the Conditional Column command, which is quite useful in cleaning up and organizing bank data. Click on Add Column > Conditional Column and a dialog box will appear asking you to assign conditions/clauses to organize your data.

• In the dialog box for Conditional Column, insert the column name (in this case it’s Category), and then assigned conditions through clauses.
  • For the purpose of this data set, if the Transaction column contains the value E-TRANSFER (case sensitive) then the category assigned to it should be Email Transfer Received. We click the Add clause button to create our next rule.
  • If none of these clauses are true, then, we set the Else value to Miscellaneous.
  • You can continue to add as many clauses to the column as required, and then click OK. A new column will appear next to your data set as per the rules set in the Conditional Column.

• At this point, you can Close and Load the Editor, and all your data will appear on an Excel worksheet.

References: