VBA Code to convert data from a PDF to Excel & organize raw data

moyad612013 · Oct 22nd 2019

Hello - I recently received help in creating a macro that takes data from a worksheet with raw data and organizes it into another worksheet in specific rows & columns. I'm wondering if I can take this a step further by perhaps copying directly off the PDF and pasting it into the "Raw" worksheet and then run a macro/VBA code that would organize specific data into specific rows on another worksheet (SIRs) and placing it in the next empty cell?

I'm attaching a copy of the PDF where I need the raw data from (some data has been redacted/changed for confidentiality).

And I'm also attaching the spreadsheet of which I'm working off of. I've copied the data off the PDF into column A.

I would need the following information from the PDF organized into the worksheet titled "SIRs":

Name of the Program
Event ID
First Name(s)
Last Name(s)
Date Reported to Care Provider
Time Reported to Care Provider
Description of Incident (Full description of Incident)
Gender
Child's Country of Birth
Age
LOS

Any help would be greatly appreciated!

**Kenneth Hobson** · Oct 22nd 2019

If you have Acrobat, not Adobe Reader, there are some methods one might use.

Otherwise, I guess one would have to use a parsing method. The first step though would be to uncompress the pdf. For that, one 3rd party application could be pdftk. e.g.

Code

'https://www.pdflabs.com/tools/pdftk-the-pdf-toolkit/
Sub ken()
  'pdftk doc.pdf output doc.unc.pdf uncompress
  ChDir "C:\Users\lenovo1\Dropbox (Personal)\_Excel\pdf\FDF\wp array method"
  Shell "pdftk ""UACSIR Sample.pdf"" output doc.unc.pdf uncompress", vbNormalFocus
End Sub

From there, copy and paste. To automate that, I would have to think on it some.

Of course if this is not a standard need or standard format, I would not bother doing all that work.

moyad612013 · Oct 22nd 2019

Thanks for your response, Kenneth. I have Acrobat Adobe Pro...if that makes any difference?

**Carim** · Oct 22nd 2019

PDF is by far the worst format on earth ... !!!

The easiest is to use some on-line converter ...

If you feel like digging into this topic ...

https://powerspreadsheets.com/pdf-to-excel-vba/

Hope this will help

moyad612013 · Oct 22nd 2019

Thanks, Carim! I'll check this out.

Do you know if there's a way to organize the data in column A (see attachment) into the respective cells in worksheet "SIRs" ? The same cells you included in the macro you helped me with?

**Carim** · Oct 22nd 2019

Hello,

Not sure to understand what you do mean by " Organize the Data in Column A " ...

To me, your Column A in Sheet ' Raw ' was a constraint ... which led to building the macro in order to correctly link fields with the Sheet ' SIRs ' which must be your working database ...

moyad612013 · Oct 22nd 2019

Notice how on the attached spreadsheet all of the raw data is in column A under the worksheet "Raw". For example, A2 reflects "First Name: John Daniel Status: ADMITTED" - but I would need "John Daniel" to be placed in next blank cell in column C under the "SIRs" worksheet. Another example, A9 in "Raw" reflects "Africa Current Program: Test Program 1" - but I would need "Africa" placed in the next blank cell in column L under the "SIRs" worksheet since that is the child's home county of origin.

Since I copied and pasted directly off the PDF, it put all the data in column A.

Thoughts?

**Kenneth Hobson** · Oct 22nd 2019

I will look at the online conversion of pdf to Excel since I don't have Acrobat on this computer. Since you have Acrobat, you do not need that. I have code that can do that in Acrobat.

Without Acrobat, other methods are needed to get the raw text. For Acrobat, getting the raw text is easier than copy and paste. You should be able to modify this to suit for one file. This one gets the text content from many files.

Code

'https://social.msdn.microsoft.com/Forums/en-US/b2264226-da8e-4453-8f02-3fa3773d090b/how-read-pdf-file-using-vba?forum=isvvba


'https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1194120-pdf-adobe-copy-to-excel
Sub Main()
  Dim a, e, s$, r As Range, i As Long, p$, fso As Object
  'p = VBA.Environ$("temp") & "\"
  p = "C:\Users\hobs0003\Dropbox\Excel\pdf\Acrobat\Copy Pall Corp  Inv 2\"
  a = aFFs(p & "*.pdf")
  Set fso = CreateObject("Scripting.FileSystemObject")
  For Each e In a
    s = ReadAcrobatDocument(CStr(e))
    i = i + 1
    's = s & e & vbTab & i & vbCrLf
    fso.createtextfile(p & i & ".txt").writeline s
    Set r = Cells(Rows.Count, "A").End(xlUp).Offset(1)
  Next e
  Set fso = Nothing
End Sub


'http://www.eileenslounge.com/viewtopic.php?f=30&t=5907
'Add reference: Acrobat
Public Function ReadAcrobatDocument(strFileName As String) As String
'Note: A Reference to the Adobe Library must be set in Tools|References!
  Dim AcroApp As CAcroApp, AcroAVDoc As CAcroAVDoc, AcroPDDoc As CAcroPDDoc
  Dim AcroHiliteList As CAcroHiliteList, AcroTextSelect As CAcroPDTextSelect
  Dim PageNumber, PageContent, Content, i, j
  
  Set AcroApp = CreateObject("AcroExch.App")
  Set AcroAVDoc = CreateObject("AcroExch.AVDoc")
  
  If AcroAVDoc.Open(strFileName, vbNull) <> True Then Exit Function
  ' The following While-Wend loop shouldn't be necessary but timing issues may occur.
  While AcroAVDoc Is Nothing
    Set AcroAVDoc = AcroApp.GetActiveDoc
  Wend
  Set AcroPDDoc = AcroAVDoc.GetPDDoc
  For i = 0 To AcroPDDoc.GetNumPages - 1
    Set PageNumber = AcroPDDoc.AcquirePage(i)
    Set PageContent = CreateObject("AcroExch.HiliteList")
    If PageContent.Add(0, 9000) <> True Then Exit Function
    Set AcroTextSelect = PageNumber.CreatePageHilite(PageContent)
    ' The next line is needed to avoid errors with protected PDFs that can't be read
    On Error Resume Next
    For j = 0 To AcroTextSelect.GetNumText - 1
      Content = Content & AcroTextSelect.GetText(j)
    Next j
  Next i
  
  ReadAcrobatDocument = Content
  AcroAVDoc.Close True
  AcroApp.Exit
  Set AcroAVDoc = Nothing: Set AcroApp = Nothing
End Function


'3rd party, pdf2text.exe
'http://www.vbaexpress.com/kb/getarticle.php?kb_id=977


'Manual method using Distiller:
'http://dmcritchie.mvps.org/excel/pdf.htm
  'http://www.library.mcgill.ca/edrs/Services/publications/howto/PDFtoXLS/PDFtoExcel.html
  
'Set extraSwitches, e.g. "/ad", to search folders only.
'MyDir should end in a "\" character unless searching by wildcards, e.g. "x:\test\t*
'Command line switches for the shell's Dir, http://ss64.com/nt/dir.html
Function aFFs(myDir As String, Optional extraSwitches = "", _
  Optional tfSubFolders As Boolean = False) As Variant
  
  Dim s As String, a() As String, v As Variant
  Dim b() As Variant, i As Long
  
  If tfSubFolders Then
    s = CreateObject("Wscript.Shell").Exec("cmd /c dir " & _
      """" & myDir & """" & " /b /s " & extraSwitches).StdOut.ReadAll
    Else
    s = CreateObject("Wscript.Shell").Exec("cmd /c dir " & _
      """" & myDir & """" & " /b " & extraSwitches).StdOut.ReadAll
  End If
  
  a() = Split(s, vbCrLf)
  If UBound(a) = -1 Then
    Debug.Print myDir & " not found.", vbCritical, "Macro Ending"
    Exit Function
  End If
  ReDim Preserve a(0 To UBound(a) - 1) As String 'Trim trailing vblfcr
  
  For i = 0 To UBound(a)
    If Not tfSubFolders Then
      s = VBA.Left$(myDir, InStrRev(myDir, "\"))
      'add the folder name
      a(i) = s & a(i)
    End If
  Next i
  aFFs = sA1dtovA1d(a)
End Function


Function sA1dtovA1d(strArray() As String) As Variant
  Dim varArray() As Variant, i As Long
  ReDim varArray(LBound(strArray) To UBound(strArray))
  For i = LBound(strArray) To UBound(strArray)
    varArray(i) = CVar(strArray(i))
  Next i
  sA1dtovA1d = varArray()
End Function

Display More

moyad612013 · Oct 22nd 2019

Thanks for your response, Kenneth.

Can I copy and paste this code into the macro and it'll work?

**Kenneth Hobson** · Oct 22nd 2019

Work is in the eye of the beholder I guess. Your pdf file is not generic, nor does it have fields, so it needs some steps to solve. Post #8 was just to get you to step 1.

You have to modify post #8 code to suit. The concept that I showed was how to get the raw data and put it into a text file using Acrobat. This is just the first step to your solution. You can copy and paste the function ReadAcrobatDocument() into a Module, add the Acrobat reference as commented, and then make a Sub to call it and pass the input values. You then get a string back which is the raw data string. In the example code in #8, I wrote that string to a text file. That step is not really needed for your problem since you have Acrobat. It is useful if you want others to help you parse the string stored in a text file. The column A string that you showed was cut and pasted so it may not be consistent and not useful for automation anyway.

I used your pdf file from post #1 in an online pdf to excel site as I said in #8. The converted Excel file would not be that useful to your step 2 needs. It could be done but paring would be more involved than what I detail later. As such, I am not sure how much help posting that code would be.

Making a short Sub to call the function in #8 is easy. If you still need help with that post back.

Step 2 is to parse the raw data string to get the data parts that you want. This will be the more tedious step. It should be doable but depends on the raw data string. From step 1, you can attach a text file with the string if you want help with parsing here in step 2 since others likely do not have Acrobat.

So that others can do step 1 that do not have Acrobat, I used a 3rd party application from xpdf called pdftotext.exe. I have attached it so that you can see that parsing it may not be too difficult.

Before I work on step 2 parsing, what worksheet did you want the parsed data to be put, SIRs?

Before I start on step 3, it would useful to know if this is a one file at a time need or multiple pdf file data imports at once need.

**Kenneth Hobson** · Oct 23rd 2019

This is ok for one file's data import but if many, I would remove the formula in column K and write the row all at once using an array to speed it up. There were a few columns that I was not sure about.

Here is the pdftotext method with parsing. The Acrobat method would be similar but the raw data string would be split to array "a" directly. Since I don't have that data, I don't know if the raw data would be in the same locations as what pdftotext.exe does.

Code

Sub ParsePDF()
  Const ForReading = 1, ForWriting = 2, ForAppending = 8
  Dim fso As Object: Set fso = CreateObject("Scripting.FileSystemObject")
  Dim pdf As String, txt As String, p As String, a, ts As Object
  Dim r As Long
  
  p = "[COLOR=#FF0000]C:\Users\lenovo1\Dropbox (Personal)\_Excel\pdf\FDF\wp array method\[/COLOR]"
  pdf =[COLOR=#FF0000] p & "UACSIR Sample.pdf"[/COLOR]
  txt = [COLOR=#FF0000]p & fso.GetBaseName(pdf) & ".txt"[/COLOR]
  
  pdftotext_Raw pdf, txt
  Set ts = fso.OpenTextFile(txt, ForReading)
  a = Split(ts.ReadAll, vbCrLf)
  ts.Close
  
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Application.EnableEvents = False
  
  With Worksheets("SIRs")
    r = .Cells(Rows.Count, "A").End(xlUp).Offset(1).Row
  
    .Cells(r, "A") = Split(a(8), ": ")(1)       'Program
    .Cells(r, "B") = Split(a(19), ": ")(1)    'Event ID
    'First Name: John Daniel Status: ADMITTED
    .Cells(r, "C") = Split(Split(a(1), ": ")(1), " Status")(0)   'First Name
    .Cells(r, "D") = Split(Split(a(2), ": ")(1), " AKA")(0)   'Last Name
    .Cells(r, "E") = a(16) 'Date of Event
    .Cells(r, "F") = Split(a(19), " Event ")(0) 'Time of Event
    .Cells(r, "G") = Split(a(84), " Date")(0) 'Location of Event
    .Cells(r, "H") = a(22) & " " & a(23) 'Synopsis of Event
    .Cells(r, "I") = Split(a(13), ": ")(1)  'Event Type
    .Cells(r, "J") = Split(a(3), ": ")(2) 'Gender
    'COO, in Synopsis of Event?
    .Cells(r, "M") = Split(Split(a(5), ": ")(1), " LOC")(0) 'Age
    'Category?
    .Cells(r, "O") = Split(a(5), ": ")(2) 'LOC
    .Cells(r, "P") = Split(a(4), ": ")(2) 'LOS?
  End With
  
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
  Application.EnableEvents = True
  Set ts = Nothing
  Set fso = Nothing
End Sub


'http://www.xpdfreader.com/download.html
Sub pdftotext_Raw(pdf As String, txt As String, Optional WindowState As Integer = 0)
  Dim pdftotext As String: pdftotext = "[COLOR=#FF0000]C:\Program Files\xpdf\bin64\pdftotext.exe[/COLOR]"
  'Dim p As String: p = "C:\Users\lenovo1\Dropbox (Personal)\_Excel\pdf\FDF\wp array method"
  'Dim pdf As String: pdf = "UACSIR Sample.pdf"
  'Dim txt As String: txt = "UACSIR Sample.txt"
  Dim wsh As Object: Set wsh = VBA.CreateObject("WScript.Shell")
  Dim q As String: q = """"
  
  'vbHide=0, vbNormalFocus=1, vbMinimizedFocus=2, vbMaximizedFocus=3
  'vbNormalNoFocus=4, ,vbMinimizedNoFocus=6
  'Wait=True
  'ChDir p
  'wsh.Run q & pdftotext & q & " -simple " & q & pdf & q & " " & q & txt & q, 1, True
  'wsh.Run q & pdftotext & q & " -layout " & q & pdf & q & " " & q & txt & q, 1, True
  
    wsh.Run q & pdftotext & q & " -raw " & q & pdf & q & " " & q & txt & q, WindowState, True
End Sub

Display More

moyad612013 · Oct 25th 2019

Wow! I had a feeling this would be a lot more complex than I would have wished :\

I appreciate all your help, Kenneth. It's evident that you know what you're talking about and very knowledgeable...I wish I could keep up Lol! Essentially, I need the data from the PDF to somehow organize into the worksheet titled "SIRs". The following data would need to be organized from the PDF into its respective rows/columns in SIRs:

Name of the Program (next blank row in Column A)
Event ID (next blank row in Column B)
A No. (next blank row in Column C)
First Name(s) (next blank row in Column D)
Last Name(s) (next blank row in Column E)
Date Reported to Care Provider (next blank row in Column F)
Time Reported to Care Provider (next blank row in Column G)
Description of Incident (Full description of Incident) (next blank row in Column I)
Gender (next blank row in Column L)
Child's Country of Birth (next blank row in Column M)
Age (next blank row in Column N)
LOS (next blank row in Column P)

To answer your questions from post #10, yes the data will go into SIRs. And it would be 1 PDF file at a time. So once I'm done with one PDF, I would repeat the same process for a new PDF and so on.

I'm attaching a copy of the PDF with the data that I need highlighted in yellow.

**Kenneth Hobson** · Oct 26th 2019

As I explained, I do not have Acrobat so I can only parse the text file that I created with pdftotext.exe. You can download that and easily test what I posted. Just change the things in red and play from that worksheet active. The only thing that needs changing maybe is the description.

Once you understand the concept of parsing, it is easy. Open the text file in Notepad, view the line number(s) where that data is and then use the concepts that I demonstrated. In some forms, I fear that it will not follow a consistent pattern. From the layout, that might just be the description "field". One can sometimes be more exact by searching for key words in the array elements rather than using line/array numbers. e.g. Line 5 in notepad would be "line"/row number 4 in the array since it is 0 based.

Get it to "work" first, then one can set it up to batch process all as post #8 shows or one at a time using a file dialog pick method or such. It is not difficult to manually add the parts in red from post #11.

As I explained too, if you do want to use Acrobat to get the text, I can show you how so ask if needed. I would need it to help you parse as I did in post #11 I suspect. Acrobat may parse the file more different than pdftotext.exe.

VBA Code to convert data from a PDF to Excel & organize raw data

Participate now!

Share