PDF to Excel Without a Converter — Excel's From PDF, Why 00123 Becomes 123 (8 PDFs Tested)

Importing a PDF table into Excel — the menu path

1. 1Open a new workbook in Excel and go to the Data tab.
2. 2Choose Get Data → From File → From PDF and open the PDF.
3. 3The Navigator lists tables such as 'Table001 (Page 1)' and pages such as 'Page001' on the left. Click a table to preview it on the right.
4. 4Click Load to put it on a new sheet as a table, or Transform Data to fix things in the Power Query Editor first.

According to Microsoft's documentation, this is Power Query's PDF connector (Pdf.Tables), which returns the tables it finds in a PDF. Running the same function in a hidden Excel took 1.5–2.6 s per PDF to read the table list (Excel for Microsoft 365, version 16.0 build 20527).

The two automatic steps — why 00123 becomes 123

The Navigator preview already showed the item code as 123. After loading, the query's formula had two steps I never asked for.

Source = Pdf.Tables(File.Contents("…\invoice_border.pdf"), [Implementation="1.3"]),
Table001 = Source{[Id="Table001"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Table001, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Client", type text}, {"Item code", Int64.Type}, {"Qty", Int64.Type}, {"Amount", Int64.Type}})

(My Excel runs in Korean, so the steps were named 승격된 헤더 and 변경된 유형 and the columns had Korean names; the code above is translated.) The table the PDF hands over is all text — columns Column1 to Column5, with 'Date, Client…' as the first row. Excel promoted that row to headers and then picked a type for each column from its values. Microsoft's documentation describes this automatic detection for unstructured sources such as Excel, CSV and text files, based on the first 200 rows; in this test it was applied to the PDF too.

Amounts turning into numbers is actually handy — you can total them right away. The problem is any column where leading zeros mean something: item codes, employee IDs, ZIP codes, phone numbers.

Keeping the zeros — edit the step, don't add one

1. 1In the Navigator, click Transform Data instead of Load (if you already loaded it, double-click the query to open the editor).
2. 2Under Applied Steps on the right, select Changed Type.
3. 3In the formula bar, change {"Item code", Int64.Type} to {"Item code", type text} (turn the formula bar on from the editor's View tab).
4. 4Click Close & Load.

I tried both on the same PDF. Adding a step after Changed Type that turns the item code into text gave '123', '456', '1020' — the zeros were already gone. Editing Changed Type itself kept '00123', '00456', '01020', and the amount (1250000) and date stayed a number and a date.

When the table splits by page

Microsoft's documentation says similar tables on consecutive pages are combined into one automatically (on by default). The Word table with its header row repeated was indeed combined; without the repeat it split into three. The PDF made in Hangul 2024 had its header on page 2 as well, yet still split into two — I couldn't find out why it didn't count as 'similar'.

Split tables can be stacked again in Power Query. Combining the tables first and then promoting the first row gave exactly 60 rows for the no-repeat PDF.

Source = Pdf.Tables(File.Contents("…\long_norepeat.pdf"), [Implementation="1.3"]),
Combined = Table.Combine({Source{[Id="Table001"]}[Data], Source{[Id="Table002"]}[Data], Source{[Id="Table003"]}[Data]}),
Headers = Table.PromoteHeaders(Combined, [PromoteAllScalars=true])

Doing the same with a table whose header repeats on every page, like the Hangul 2024 PDF, gave 61 rows — one of them was page 2's header row left in as data. Filtering it out with Table.SelectRows(Headers, each [No] <> "No") (the first column's name) left 60.

Instead of From PDF — extract the text and paste

If your Excel has no From PDF, or a PDF won't open (like the password one), you can extract the table's text and paste it. The PDF Text Extractor put a tab between the cells of the same invoice table, so pasting into A1 split it into five columns (the borderless table came out the same).

Pasting also turned 00123 into a number. The amounts, though, came in as numbers with a comma format, so the total (5,857,733) worked straight away. For a column that needs its leading zeros, select it and set Format Cells → Text before pasting, and 00123 stays as is.

FAQ