How can I copy a table of data from a PDF file into Excel?
I have a table of data and I need to get it into Excel. I found a few websites that suggested copy and paste into MS Word and then use the "Convert Text to Table" which unfortunately doesn't work as the columns come in with a space in between them but if I choose space as the column separator it breaks up columns that have multiple words in them into different columns.
Is there a better recommended way to get a table from a PDF file into Excel?
33 Answers
The whole thing is much simpler than mentioned in the other answer.
In Acrobat/Reader, choose the Text Selection tool, and bring your table into full view.
Now press the Ctrl/Option key and select the table. You will notice that the cursor changes. When the selection is complete, you can copy and paste into Excel.
I would like to suggest you VBA Code, this will Transpose the Copied Data Table from PDF to Excel.
Follow below written steps.
- Copy Table Data from PDF file.
- Paste in Excel sheet in a Column.
- Run the VBA code.
Check the Screen Shot.
Private Sub CommandButton1_Click()
Dim xLRow As Long Dim xNRow As Long Dim i As Long Dim xUpdate As Boolean Dim xRg As Range Dim xOutRg As Range Dim xTxt As String On Error Resume Next xTxt = ActiveWindow.RangeSelection.Address Set xRg = Application.InputBox("Select Data Range(only one column):", "Transpose to Excel", xTxt, , , , , 8) Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange) If xRg Is Nothing Then Exit Sub If (xRg.Columns.Count > 1) Or _ (xRg.Areas.Count > 1) Then MsgBox "Used range only contain one column", , "Transpose to Excel" Exit Sub End If Set xOutRg = Application.InputBox("Select output range(specify one cell):", "Transpose to Excel", xTxt, , , , , 8) If xOutRg Is Nothing Then Exit Sub Set xOutRg = xOutRg.Range(1) xUpdate = Application.ScreenUpdating Application.ScreenUpdating = False xLRow = xRg.Rows.Count For i = 1 To xLRow Step 3 xRg.Cells(i).Resize(3).Copy xOutRg.Offset(xNRow, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True xNRow = xNRow + 1 Next Application.ScreenUpdating = xUpdate
End SubNB: The Data I've used to TEST the code has 3 Column(RED Colour Value), so that with For Loop Step & Resize value is 3. You change according to your Data structure.
Hope this help you.
A bit late to the party, but for the benefit of anyone finding this via Google:
There are plenty of free web services you can use for this instead of struggling with copy pasting via Word etc. Picking the right service depends on your PDF and needs.
Is the entire file a table and you want to convert it all? -> Try
Are there individual tables, e.g. on a page with lots of other text, you want to copy? -> Try
Is your PDF a scanned file (i.e. it is actually an image saved as a PDF)? -> Try
1
