Excel "Get Data" from PDF using table that extends over multiple pages

Question

I have one table that extends over three pages in a PDF. When I do a Get Data > From File > PDF in Excel, the one table that displays over three pages in the PDF is identified as three separate tables in the Get Data Navigator screen when I am selecting tables. So I select all three "tables" for the one table and click Transform Data, but the problem is that only the first table contains the original column titles, and the other two tables display "Column1, Column2, etc..." for their column titles. And if the column headers don't match up in all three tables, I can't Append the three tables back into one table. So my question is, how do I tell the Transform Data screen to ignore the column headings in tables 2 and 3? I tried changing "Use First Row as Headers" to "Use Headers as First Row", but that doesn't help. Sure, I can edit all of the column headings so they all match between the three tables - they append just fine then - but it's a pain, and if this happens with a data set with a lot of columns, well, there has to be a better way than manually changing column names.

Answer 1

Hi Stewie

Below is a simulation of what you seem to have with 2 tables only, approach will be the same with 3, 4... just adapt below step #3

1. Click on Table1
2. Go to the Transform tab > little arrow below Use First Row as Headers > Use Headers as First Row
3. Go to the Home tab > Append Queries > Append Queries > Select Table2 > OK
4. Go to the Transform tab > Use First Row as Headers

With this approach no manual column renaming

Hope this makes sense

Answer 2

Change the header of the first table to Column1, Column2...

so now you can append then change the headers back to the original headers in the append query. You only have to do this once since it will follow the same steps when you update all queries.

Answer 3

Hi Stewie

Below is a simulation of what you seem to have with 2 tables only, approach will be the same with 3, 4... just adapt below step #3

1. Click on Table1
2. Go to the Transform tab > little arrow below Use First Row as Headers > Use Headers as First Row
3. Go to the Home tab > Append Queries > Append Queries > Select Table2 > OK
4. Go to the Transform tab > Use First Row as Headers

With this approach no manual column renaming

Hope this makes sense

Wow! VERY clever! This way it doesn't matter how many smaller tables are made due to being split by paging. All that's needed is to demote the original column headings in the first table, append all of the tables (since now ALL have the same column headings, including the first one), then in the combined table promote the original column headings back to their official column headings position. Nice - thank you!!!!!

Answer 4

All that's needed is to demote the original column headings in the first table, append all of the tables (since now ALL have the same column headings, including the first one), then in the combined table promote the original column headings back to their official column headings position

Exactly Stewie. Glad you got it & Thanks for posting back

Nice day...