Pdf converted to excel needs to be cleaned for analysis weekly.

[asad1463]

Hi All,

I am required to work on pdf files every week which have a lot of data that need to be analyzed for raising invoices.
Currently I load the pdf files into excel which creates multiple tables for each of the different invoices in the pdf.
I then run a series of steps to clean the data & put it in a format that can be used for analyzing.
I have to do the same steps for each tab every week & this is an extremely clumsy process.
I want to learn a better way to work these smartly.

The data in the excel appears in the following format in multiple tables:

1Capture.PNG


I need to them remove some of the unwanted columns & get the data in the following format:

Capture.PNG

Kindly advise.
Thanks.

[PeteABC123]

Hi asad1463. Welcome to the forum.

Best done with a full version of Adobe Acrobat Standard DC and Excel.
If you have Adobe, get it into Excel by: File. Export to spreadsheet.
Delete the K1 cell, which would correspond to the description heading. Shift cells up.
Now all your data is in a single row.
Delete the columns you don't want, delete the rows you don't want, and end up with the desired table. Choose your row criteria carefully; likely the blank rows when filtered to column B blanks. A macro can do all of this in a pinch.

Pete