optimize Camelot large pdf files

Question

Good afternoon! I use Camelot to extract data from PDF files (bank statements to be more accurate). However, I have a PDF file with more than 5000 pages, and Camelot is a bit slow. I decided to create a script where Camelot processes a number x of pages at a time, until all pages are extracted. in the process storing all tables found in a dataframe. occurs that I believe the dataframe is becoming somewhat large. i export the dataframe to excel every x pages. however not zero as I need to increment the excel file.

I wonder if there is any way to save this data, incrementing the file, thus zeroing the dataframe every x pages. to_csv defaults text. to_excel does not allow append.

import camelot, PyPDF2, pandas, tqdm
from tkinter import Tk, filedialog as dlg

Tk().withdraw()
file=dlg.askopenfilename()
np=int(input('informe a quantidade de paginas por execução: >> '))
final=pandas.DataFrame()

j=PyPDF2.PdfFileReader(file)
pag=j.getNumPages()
ini=int(1)
eend=int(np)


for k in tqdm.tqdm(range(1,int(pag),np)):
    

    print(ini)
    pp=str(ini)+"-"+str(eend)
    if eend=='end':
        t=camelot.read_pdf(file,flavor='stream',pages=str(pp))
        for n in t:
            final=final.append(n.df)
        final.to_excel(file.strip('.pdf')+'.xlsx')
        break
    t=camelot.read_pdf(file,flavor='stream',pages=str(pp))
    for n in t:
        final=final.append(n.df)
    final.to_excel(file.strip('.pdf')+'.xlsx')
        
    ini+=np
    eend+=np
    if eend > int(pag):
        eend="end"

Answer 1

Maybe this will give some optimized

import camelot, PyPDF2, tqdm
import pandas as pd
from tkinter import Tk, filedialog as dlg

Tk().withdraw()

file_path = dlg.askopenfilename()
last_page = PyPDF2.PdfFileReader(file_path).getNumPages()

number_pages = int(input('informe a quantidade de páginas por execução: >> '))

ini = 1
eend = 0

for k in tqdm.tqdm(range(1, last_page, number_pages)):
    a = pd.DataFrame()

    eend += number_pages 
    
    pages = f'{ini}-{eend}'
    file = camelot.read_pdf(file_path, flavor = 'stream' , pages = pages)
    [a := pd.concat([a, item.df]) for item in file]
    
    a.to_csv('output.csv', encoding = 'latin-1', mode = 'a')
    ini += number_pages

I added a comprehension list and deleted a for that existed in your code. The encoding you can modify to the one that best suits you when saving csv. I haven’t done any validation for the size you can split the pages, so remember to calculate before if it is possible to do the splitting of pages.

Answer 2

after the help and test the use of the tabula the script was like this:

import tabula, PyPDF2, tqdm
from tkinter import Tk, filedialog as dlg

Tk().withdraw()
file_path = dlg.askopenfilename()
last_page = PyPDF2.PdfFileReader(file_path).getNumPages()

number_pages = input('informe a quantidade de páginas por execução: >> ')
if number_pages=='':number_pages=last_page
else:number_pages=int(number_pages)

ini = 1
eend = 0
for k in tqdm.tqdm(range(1, last_page, number_pages)):
    eend += number_pages
    if eend > last_page:
        eend=last_page
    
    pages = f'{ini}-{eend}'
    print(pages)
    
    file = tabula.read_pdf(file_path,guess=False,silent=True,pages = pages)
    for item in file:
        #acrescentei o try devido alguns erros de encoding. assim não houve perda de dados.
        try:
            item.to_csv(file_path.strip('.pdf')+'.csv', encoding = 'latin-1', mode = 'a')
        except:
            item.to_csv(file_path.strip('.pdf')+'.csv', mode = 'a')
        
    ini += number_pages

there was a very big improvement with the use of the tablet.

Answer 3

I have no way to test your program. So the answer will be more theoretical.

Get to the idea:

• Create a list of results (dataframes) and then concatenate them

See the example below:

Creating the dataframes

import pandas as pd

def processa_pagina_do_pdf(n):
    return pd.DataFrame({"col": [n]})    


df_list = []

steps = 10

for x in range(0, 100, steps):
    for pagina in range(x, x+steps):
        df_tmp = processa_pagina_do_pdf(pagina)
        df_list.append(df_tmp)

Concatenating the Dataframe List

final_df = pd.concat(df_list)

Then just save the final_df for csv or Excel.

Note: Give a chance to Pymupdf. Good and fast to extract PDF data

I hope it helps resolve the issue