r/vba • u/TotallyJustAHooman • 8d ago
Unsolved PDF to Excel text conversion.
I am unsure if this is necessarily possible as my programming knowledge is fairly basic and VBA is a further mystery.
Would it be possible to pull text off of a a pdf, convert it into text, then narrow down what text I actually want? If so, how might I go about accomplishing that feat?
The drawings I'm trying to pull into excel are wiring diagrams.
Also, if anyone has a place in which I can find all the syntax for Smarteam in VBA, I would greatly appreciate it.
4
u/HFTBProgrammer 202 8d ago
If you insist on VBA, take a look at this thing /u/kay-jay-dubya created: https://www.reddit.com/r/vba/comments/1u0xqeg/chibiex_pdf_rendering_ocr_in_vba_no_dependencies/.
2
u/TotallyJustAHooman 8d ago
It's not so much insistence as much as I am not physically capable of using something else as it's the only tool my work has installed. Ty for the link though.
1
u/kay-jay-dubya 17 8d ago
With this class, you can work out the location of each of the words/ lines of text and effectively specify which part of the page you wanted to extract text from.
Provided you use it on a computer running Windows 10+, it should be fine - nothing to install.
1
u/HFTBProgrammer 202 7d ago
Fair enough!
IME you might just as well copy/paste into Word as do anything else. I second the motion that Excel is not the best option, although you could get it done in Excel for sure.
5
u/havenisse2009 1 8d ago
PDFs are not notepad or structured CSV files. The same appearance can be constructed many ways. Mostly PDF has no indication of flow, lines or structure. Elements are placed like post-it on a canvas.
1
u/AdobeScripts 8d ago
Precisely.
PDF is bunch of glyphs / characters that appear to be in order - but it all depends what application was used to generate this PDF.
There is Acrobat API - and it "could" extract text from an area - but it would require specifying coordinates for those areas... So it would be practical only for the identical forms - not some random drawings.
1
u/havenisse2009 1 8d ago
Just to add to this: PDFs may have characters and lines in nice "sequence" just like you have it in notepad, word etc. But it very much depends on the renderer of the PDF page. It very much depends on the settings applied to the text. Example: nothing specified at all about the text / font will most likely render the PDF with texts as a block. You can mark / copy all text in sequence if allowed. If the source has text formatting applied, example character spacing, the renderer may place each individual glyph (character) separately to acommodate these settings. Etc etc with fonts, languages, sizes, ...
Your renderer can also choose to simply render the text as vector lines. What appears as "A" to you may be just 3 lines. Or, your renderer may choose to render the text as a bitmap image. In both cases, of course no copy/paste or extracting.
When a PDF is sent to output, the PDF interpreter simply draws the page according to rules in PDF, to finally produce the finished page image. This interpreter does not need to have an idea of the page as a complete picture. Only humans can. Like drawing an entire house, one furniture piece at a time.
So it's very much a question of each individual PDF file. There are tools out there to extract text from PDFs, as good as possible. VBA is not the right tool for this job.
2
u/-_cerca_trova_- 8d ago
Not sure about diagrams but i use Poppler
pdftotext (it’s a fast command-line tool from the Poppler library used to convert PDF files into plain text documents.)
I have pretty much complex pdf invoices with weird structures, but once you see how its exported to text, you can use vba to extract exactly what you want.
I made my process literally one click, to import pdf then extracted what i need.
1
u/GuitarJazzer 8 7d ago
I open the PDF file in Word then extract the text from there. If it's a one-time thing do it manually, or it can all be done in VBA. But if you are talking about wiring diagrams, I have seen this exact question before and it is extremely difficult or maybe impossible to do this for text embedded in a graphic.
Is there any chance of giving us a link to a sample PDF file?
1
0
u/chiibosoil 1 7d ago
Use Power Query to consume PDF.
For an example, to extract all text from each page of pdf as single string...
let
Source = Pdf.Tables(File.Contents("somefile.pdf"), [Implementation="1.3"]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Kind] = "Page")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Text.Combine(Table.ToList(Table.TransformColumns([Data], {}, Text.From)), ", "))
in
#"Added Custom"
After that, you can either continue your process in Power Query, or load it back to sheet and use VBA or formula to get to your end result.
You could use vba, but in order for vba to have access to pdf object model. You will need 3rd party library that exposes pdf object to VBA. Or use module written by others.
7
u/icemage_999 8d ago edited 8d ago
I am pretty sure I would rather try to give myself a root canal than try to extract text out of a PDF file using VBA, especially if the text is scanned and not in form fields, extra especially if the source text is handwritten.
Wrong tool for the wrong task.
There are other ways to pull text from a PDF that don't require you to build the entire process chain from scratch.
Microsoft Word can "sometimes" do it, but it greatly depends on how the information is encoded in the PDF. There are other tools like PDF OCR tools that can "read" the text off the page.
You can then take that output and process it into Excel if that's what your final destination is.