Extract text from PDF

[ShcreeminEagle]

My ERP system at work produces reports in the form of PDFs. I used an online file conversion to reformat the PDF as a CSV. However, there is a key piece of data I need from the PDF that is not in table format so when I convert from PDF to CSV this is not included. Please see attached to further explain.

The sections blocked out in yellow in the PDF snippet become the values in excel replacing the X's.

The only thing I am missing is the number next to the red arrow (I only need the number, don't need "Summary of"). Is there a way to have excel reference a PDF file and extract this number? There are hundreds of pages in my PDF with each page representing a different part number.

I'd like to populate the cell highlighted in yellow in my excel sheet with the referenced number in the PDF.

The spreadsheet only shows data from page 1 of the PDF, I'd like to repeat this for every page, without having to go through it manually.

EDIT: Added a PDF file as well as the PNG

[Marc L]

I've never seen any ERP able to export a pdf file but can't create an easier text file, so weird !

[alansidman]

Look at the attached which I imported using Power Query

Please Login or Register  to view this content.

Excel 2016 (Windows) 64 bit

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
3	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Column13	Column14
4	FROM: 11/30/2021			TO: 11/30/2022				Summary Report
5	Summary of 01110-3150
6	Mold Size			PX Density	1.4	Target Wt	1836	Parts Produced	2,268	Target Cycle	140	SRT		77.36
7	Units/Shot	2		Final Density	1.5	Avg Wt	1725.08	Parts Packed	2,124	Avg Cycle	150.51	ART		45.3
8	Material	EPS - Modified		Matl Used	9,027.75	Wt Var	-6.04%	Pack Rate	93.65%	Cycle Var	6.98%	Run Var		-41.44%
9	Bead	EPS				Bead Cost	$1.28
10	Material Used		$11,470.14		Price/Part	13.49		Total Value	$0.00		Income/SRT		$-306.91
11	Packaging Matl		$1,112.17					Contribution	$-23,742.52		Income/ART		$-524.12
12	Machine Overhead		$9,006.23					Gross Operating Margin
13	Packaging Labor		$2,153.98
14	Freight Cost		$0.00
15	Commission		$0.00

Sheet: Sheet1

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.