Oct 31 2021 08:33 AM
Hello
Developed a spreadsheet used for audit and automated it as much as possible. The last step for the user is to enter numbers in certain cells that come from a PDF/Adobe. I want to cut their time so I was thinking of linking my Excel test sheets to the Adobe docs. But, is there a way to do that?
I have been copying and pasting from Adobe to Excel, thinking "wow, really, there must be something I can use to eliminate this task".
Every month the s/s will diff and the PDF as well. They contain anywhere between 20 to 400 pages of customer's bills. So s.s needs to find the pdf with specific acct# then grab the values associated to the specific line description.
Oct 31 2021 10:50 AM
Excel import tool: In Excel from Office 365 (the version with subscription) there is an elegant and convenient way. This is suitable if you want to adopt more than just one column. Here is a link with the relevant information.
Announcing data import from PDF documents
If your version does not offer the above option, you can upgrade your office package or manual tricks for marking and copying from Pdf to Excel. Here is a manual trick:
Manually import data from a PDF into Excel (includes photos and text in German)
In order to be able to use this trick, the creator of the PDF must not have applied any technical restrictions, such as a ban on copying data from it. It must be a searchable PDF document.
In common PDF readers such as Foxit Reader or Adobe Acrobat Reader DC there is usually a problem when marking table contents in PDFs. But if you do not want all data from Pdf, but only individual columns. If you start to mark with the mouse in the PDF reader you will notice that the reader marks the columns with blue.
Press and hold the two keys Alt + Shift (Alt + Shift) with your left hand while using your right hand to mark the numbers in the desired column using the mouse. You will see that vertical marking is allowed by the reader (if the aforementioned requirements are correct) without including other columns and rows in the selection.
Copy the marked data from the PDF with Ctrl + C and switch to your Excel table. Place the cursor in the cell from which you want to insert the data. Depending on the original material, try Ctrl + V to insert the data. If all the numbers end up in a single cell, go to Edit / Paste Special / Text ... and the copied data end up in Excel.
Hope I was able to help you with this info.
NikolinoDE
I know I don't know anything (Socrates)
Oct 31 2021 11:00 AM
Oct 31 2021 08:55 PM
SolutionIf you have managed to insert the data / values from the pdf document into the worksheet, with whatever option, it is possible to accomplish your project without problems with the IF function or whatever other function is necessary. However, the data must first be available on the worksheet in the cells you want.
If you want you to be able to compare the data directly from the PDF document and transfer it to the worksheet, it is not possible to do with a simple function.
The first step is always to get the data / values / text on the worksheet beforehand and then it is possible to use whichever function.
I do not know whether it is possible with the full version of Adobe Pro DC, or with any other third-party software, you would have to do some research on the Internet.
With the right Office version (where it is possible to import data from pdf - please see the link that I sent you before) you could transfer / import the data on the Excel sheet from the pdf document and use a macro (VBA Code) or cell formulas they could accomplish your project.
I wish you continued success with Excel
Nov 01 2021 06:30 AM
Nov 01 2021 06:35 AM
Dec 03 2021 04:27 AM
Dec 03 2021 05:19 AM
Dec 03 2021 07:12 AM
I'd suggest you create a folder where you can save all the PDFs as they come. Import the folder into excel with Power Query and after some cleaning, you would have combined all the files into a single Excel Table.
The best part is that the next month, you don't have to go through all that rigour again. Just refresh the table and it appends the most recent PDFs into your Excel table.
You might find this thread useful: https://community.powerbi.com/t5/Power-Query/PDF-from-Folder-Multiple-Tables-and-Multiple-Pages/m-p/...
Oct 31 2021 08:55 PM
SolutionIf you have managed to insert the data / values from the pdf document into the worksheet, with whatever option, it is possible to accomplish your project without problems with the IF function or whatever other function is necessary. However, the data must first be available on the worksheet in the cells you want.
If you want you to be able to compare the data directly from the PDF document and transfer it to the worksheet, it is not possible to do with a simple function.
The first step is always to get the data / values / text on the worksheet beforehand and then it is possible to use whichever function.
I do not know whether it is possible with the full version of Adobe Pro DC, or with any other third-party software, you would have to do some research on the Internet.
With the right Office version (where it is possible to import data from pdf - please see the link that I sent you before) you could transfer / import the data on the Excel sheet from the pdf document and use a macro (VBA Code) or cell formulas they could accomplish your project.
I wish you continued success with Excel