Automate pdf reader and convert data to excel table with correct column mappings

[Miguel_n11]

Hi all, I need assistance with creating a pdf reader which accurately reads the pdf file and converts the file into an excel document with the correct level of column mapping of the data.

[mlauber71]

@Miguel_n11 welcome to the KNIME forum. There was a discussion about this with some example workflows.

Extract Tables from PDF

Hello everyone, It is possible if the PDF allows it, meaning if the string we get from the PDF represents the table so that we can manipulate the String, to extract the table. I took @JAGBI’s example and parsed the first three pages as an example. In order to extract the whole table/document, some more string manipulation would have to be done. The magic happens in the Extract Table metanode, where the string is parsed to an actual table. The workflow would have to be adapted to other PDFs/tab…

Maybe you can provide us with a sample to see how the tables would look like, if you have one that is ok to be published.

[Miguel_n11]

Hi I have an issue with uploading the pdf file as it is not an accepted file format. Could you assist?

[ScottF]

I think if you change the extension to something else (like txt) you should be able to upload it - then whoever downloads can change it back to PDF afterward.

[Miguel_n11]

Hi I have embedded the PDF file here: https://www.beamium.com/FGGQVRWP

[Miguel_n11]

Please see pdf which requires KNIME to extract data from PDF tables: https://www.beamium.com/FGGQVRWP

[ipazin]

Hello @Miguel_n11,

please make sure that you are not sharing any kind of private or sensitive information publicly.

Br,
Ivan

[Miguel_n11]

Hi,

This is a dummy data view of the data we receive.

Regards,

Miguel

[mlauber71]

I had problems with the mentioned KNIME only approaches so I tried something with KNIME and R. It has these steps:

• run and configure R’s “tabulizer”
• it seems the settings ‘stream’ and GUESS are working best in your case
• it would extract one table from each page and try to find headers and bring them to a table
• not all information would be in the same columns (we come to that later)
• the tables are saved as single CSVs (with their varying structure)
• then they would be imported into KNIME forcing the columns to be all strings and be brought into a single table
• the text fields which contain information in three columns would be integrated
• the summary lines with the Credit balance would be separated
• a single ID for each transaction block is created and distributed
• the “our reference” field is extracted separately and be stored in a separate column (you might do that to other information as well)
• the remaining “communication” is brought into one cell
• all the information is being put together and could be stored

Of course, you might do further manipulations like converting the sums into numbers. Introducing checks with the separate balances and so on. If you have columns that would change very much you might have to alter the workflows and change the definitions in R.

The results would then look something like this:

Unfortunately on my Mac, I experience problems with the R package when running from within KNIME.

Extract Table from PDF with the help of R "tabulizer" and KNIME – mlauber71

[ipazin]

Hi @Miguel_n11,

thought so but wanted to check

Br,
Ivan