Importing Bank Statement PDF into Excel via Power Query
Vložit
- čas přidán 5. 08. 2024
- This tutorial demonstrate how to grab bank statement which is in PDF format into Microsoft Excel directly without using any external software for conversion of PDF to Excel. It uses Power Query which is embedded in Microsoft Excel to import it.
0:00 Intro
0:13 PDF Overview
0:41 Import Demonstration
5:30 End
Below is the setup used for demonstration
Excel: Office 365
OS: Windows 10 (64 bit)
So far the best explained video without any bias
Ty u r only till now I have seen hundreds of videos of excel 2 mention the excel version used ty keep up the good work
Very well explained without any ambiguity and the presentation is excellent to follow and understand
Interesting. Nice sharing. 👍🏼
Perfectly explained. Direct to the point. I am your new subscriber
Well done. Thanks very much.
Your rock! that was super helpful!!!
Excellent..👍👍👏👏 Dhanajay .. Thank you .. keep educating us.. please..
Thx for sharing 😄
Superb...
Excellent
Excellent Dhanjay ji. Will ask any clarification is needed
Hi sir,
i have a bank statement with multiple pages & power query makes a separate table for each page, is there any way to merge all of those tables?
Yes, using Append option
But you need to ensure that you have exactly same columns for all tables, before performing append.
Very good video. However I am not getting an option to import pdf even if I have office 365 subscription. How can I do this function in that case ?
Simply update your MS Office, it will appear
If I want to merge multiple line narration datewise in pq how to do it in power query
I already covered this scenario in this video. Check out below video
czcams.com/video/7av_riLnZNw/video.html
Bro, you lost info from the description when sorting null date)) Anyway, I found what I was looking for. Thank you
No, didn't lost it, rather I deliberately removed it. There is another video, where I have demonstrated, how to combine & retain multi line description.
Hi. I have a file with more than 10 pages. Is there a way by which I can clean all these pages and load to a single excel sheet? Please help me out.
Ideally this trick should work with multiple pages.
@@ExcelKida this helped me convert bank statements with multiple pages that are automatically considered as a single table. But in certain bank statements the pages show as different tables. I want know how to merge those multiple tables that essentially have similar data and is in same order. Pls help.
I struggled to do this because my statement converts to 124 Tables. There are about 5800 lines of data. I am not sure where to go from there.
I covered this scenario in another video. Sharing link of it
czcams.com/video/QCcxQocCrxw/video.html
But I think 124 pages is too much large even for this technique. You should try to grab CSV / text delimited file for this from your banking menu
i use a mac book pro it doesn't show the pdf option on my 365
Microsoft never show up interest in pushing things for Apple, due to the rivalry. Even their official support page too reflects that PDF is not available for Mac OS (refer below link).
support.microsoft.com/en-us/office/import-data-from-data-sources-power-query-be4330b3-5356-486c-a168-b68e9e616f5a#ID0EBF=macOS
My personal suggestion, use AI to get this job done, which is far more intelligent than Excel in such things. I have seen PDF import option failing for many bank statement PDF.
How to import narration also
You can copy paste remarks into narration column
I didn't find PDF option on my excel sheet "From File"
Possible issues & troubleshooting
1) your Office 365 Excel is not updated [go to File > Account > Update Now]
2) you are using Excel version other than Office 365, like 2007,,2013,2016,2019, which does not support this feature
I would suggest go for Excel Online which you can sign-up for free & use this. I have already demonstrated this in latter portion of video.
it is worked on credit card statement also...plz tell sir
It totally depends on the internal structure of the PDF file & if Power Query is able to decode it nicely or not. You can always give it a try.
@@ExcelKida sir can you solve problem of add space between narration in Excel ... Do you have any formula for that..plz help sir how to do space
How to import a file with more than 50 pages... Please help
I have covered multiple pages import in another video. But 50 pages is pretty voluminous. Adobe offers a special discounted plan for PDF to Excel conversion, which is pretty cheap (I guess ₹ 1500 / year). Go for it to perform such bulk level pages conversion to Excel.
I am not getting the preview. Please help me out
Possible issues
1) If you don't have Office 365 Excel, you will not get PDF option
2) Power Query will might not be able to decode every PDF into table
Hi, I have a question about the 2nd column (Narrative). If I want to keep the description which goes over 2 lines and combine them in one cell.
Can you explain to me how to do this?
Yes you can, but the steps are very complex for that. Also, it requires writing some of the M Query script manually. But anyway, thanks for this input. I will definitely plan a video for this too for future
@@ExcelKida thanks for your reply. I look forward to seeing the video.
did you make any video for this?? @@ExcelKida
But I can't SUM the amount of money of the data that converted from pdf. Does anyone know what is the problem?
Just ensure to apply Decimal format in Power Query to columns having numeric / amount type of data. SUM function fails if in the given range text is found in any cell. Sometimes cell appears as number but internally comma symbol might be treated as text when loading from Power Query.
@@ExcelKida Thank you very much, you're so helpful.
If it's password encrypted how do i import to excel
Then you need to use Print to PDF printer to generate password-less file & then go for the steps I demonstrated
It shows "pdf failed to load with result : password Required"
Would not work with password protected PDF. You can try a simply hack. Just open PDF via Acrobat Reader & then use Print to PDF inbuilt printer of Windows to generate PDF, which effectively removes your password in result file. Then you can follow the step of videos
How about the Connection
Can you deliberate your query in a bit more detail ?
Agar get data ka option hi na show ho rha ho Excel 10 me
That means you have very old version of Excel. I will suggest you to upgrade to Office 365 version of MS Office
Windows 10 main Get data option nehi hai, kaise laia
Windows 10/11 had nothing to do with this. It seems you have an outdated version of MS Office. Kindly upgrade to Office 365 to enjoy this feature
plz add space between narration sir
Some cosmetic changes you can do via Excel
GOOD MORNING SIR, HOW TO REMOVE HEADER (LIKE FIRM NAME , ADDRESS , BANK ADDRESS AND MORE ) ITS COME EACH PAGE , HOW TO REMOVE IT , ITS KOTAKMAHENDRA BANK , PLZ REPLY ME , I AM A UR SUBSCRIBER , THANK U
You need to use Power Query "Remove Rows" operation in Home tab of Power Query, where it asks you how many rows to remove from top (for page headers) or bottom (for page footers).
FB link
facebook.com/excelkida/
Not Useful when multiple pages of Bank statement 😅
For multiple pages, you need to use Append option to combine multiple pages & then do the manipulation. If you develop some hands-on on playing with Power Query, then even multiple pages easily
Bro need some sleep..
Sure. You can email / WhatsApp me your query on email ID / number I have shared at the end of video.
while in navigator tab i am getting table is empty
Sometimes, table is not formatted properly, due to which Power Query might not be able to decode data into tabular format, which would display table as empty
What is the remedy for that problem @@ExcelKida