11

In Python 3, I have a PDF file "Ativos_Fevereiro_2018_servidores_rj.pdf" with 6,041 pages. I'm on a machine with Ubuntu

On each page there is text at the top of the page, two lines. And below a table, with header and two columns. Each table in 36 rows, less on the last page

At the end of each page, after the tables, there is also a line of text

I want to create a CSV from this PDF, considering only the tables in the pages. And ignoring the texts before and after the tables

Initially I tested the tabula-py. But it generates an empty file:

from tabula import convert_into

convert_into("Ativos_Fevereiro_2018_servidores_rj.pdf", "test_s.csv", output_format="csv")

Please, does anyone know of another method to use tabula-py for this type of demand?

Or another way to convert PDF to CSV in this file type?

Reinaldo Chaves
  • 965
  • 4
  • 16
  • 43
  • 1
    try `import tabula` and `tab = tabula.read_pdf("Ativos_Fevereiro_2018_servidores_rj.pdf", encoding='latin-1')` Does it throw an error? if not, run `print(tab)`. Is it also empty? – ilja Mar 29 '18 at 16:05
  • Thank you. Yes, it's still empty (None) – Reinaldo Chaves Mar 29 '18 at 16:09
  • 1
    tabula seems not to find the tables inside the pdf. Without having the pdf it's hart to tell why (on my pdfs tabula works grat). Maybe you could set `guess=False` and specify the `area` and/or `columns` (see options docs) or give `pdfquery` a try (https://github.com/jcushman/pdfquery) or see where "raw strings" can get you (e.g. with PyPDF2) – ilja Mar 29 '18 at 16:35
  • Thank you. I'm looking at the documentation (https://github.com/chezou/tabula-py). But how do I find the area where the table is? My PDF is here: https://drive.google.com/file/d/1P8kF0gUOVls6sOGed4R0C2PlVF5RFtU6/view?usp=sharing – Reinaldo Chaves Mar 29 '18 at 18:01
  • Do I need a PDF editing program to find the coordinates? – Reinaldo Chaves Mar 29 '18 at 18:05

2 Answers2

12

Ok, I've found the issue: you have to set spreadsheet=True and keep utf-8 encoding:

df = tabula.read_pdf("Ativos_Fevereiro_2018_servidores_rj.pdf", encoding='utf-8', spreadsheet=True, pages='1-6041')

In the picture below I tested it with just the first page (because your file is huge):

enter image description here

You can save the DataFrame as csv afterwards:

df.to_csv('otuput.csv', encoding='utf-8')

Edit:

Ok, the error could be a java-memory issue. To make it faster I added the pages option. And there also was an encoding problem, so encoding='utf-8' added to the csv export. If you keep running into the java-error, try parse it in chunks, e.g. pages='1-300'. I just did all 6041 (on a 64GB RAM Machine), it worked fine.

ilja
  • 2,592
  • 2
  • 16
  • 23
  • Thank you! I then tried to do so: import pandas as pd import tabula tab = tabula.read_pdf("Ativos_Fevereiro_2018_servidores_rj.pdf", encoding='latin-1', spreadsheet=True, pages=all, header=0) tab.to_csv('file_ready.csv', index=False) – Reinaldo Chaves Mar 29 '18 at 18:20
  • But I had this error: TypeError: expected str, bytes or os.PathLike object, not builtin_function_or_method – Reinaldo Chaves Mar 29 '18 at 18:20
  • Sorry, I think there were missing quotes on 'all' – Reinaldo Chaves Mar 29 '18 at 18:22
  • 1
    Yes, or remove `pages` it from the options, so: `df = tabula.read_pdf("Ativos_Fevereiro_2018_servidores_rj.pdf", encoding='latin-1', spreadsheet=True)` – ilja Mar 29 '18 at 18:23
  • tab = tabula.read_pdf("Ativos_Fevereiro_2018_servidores_rj.pdf", encoding='latin-1', spreadsheet=True, pages='all', header=0) – Reinaldo Chaves Mar 29 '18 at 18:41
  • 1
    I got this error message: CalledProcessError: Command '['java', '-jar', '/home/reinaldo/Documentos/Code/intercept/seguranca/lib/python3.6/site-packages/tabula/tabula-1.0.1-jar-with-dependencies.jar', '--pages', 'all', '--guess', '--lattice', 'Ativos_Fevereiro_2018_servidores_rj.pdf']' returned non-zero exit status 1. – Reinaldo Chaves Mar 29 '18 at 18:41
  • 1
    I edited my answer. I think there are some issues in the pdf itself (on some pages). I'll run the loop to see which, but its slow on my machine.. ;-) – ilja Mar 29 '18 at 18:55
  • Thank you. There is something very strange, every page here has error messages – Reinaldo Chaves Mar 29 '18 at 19:18
  • Hmm... yeah, thats strange. I'm on page 100 and until now I'ts fine, no errors... – ilja Mar 29 '18 at 19:23
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/167837/discussion-between-ilja-and-reinaldo-chaves). – ilja Mar 29 '18 at 19:28
  • I keep getting "unexpected keyword argument: Spreadsheet" – Chicken Sandwich No Pickles Nov 25 '20 at 01:42
3

Converting PDF to CSV with tabula-py

from tabula import convert_into
table_file = r"ActualPathtoPDF"
output_csv = r"DestinationDirectory/file.csv"
df = convert_into(table_file, output_csv, output_format='csv', lattice=True, stream=False, pages="all")
dataninsight
  • 1,069
  • 6
  • 13