I’m working at a startup and would like to automate one of my daily tasks.

Currently we receive orders from 3 different distributors in either PDF or Webpage format and I must take some of the information from those PDF/Webpage orders and place them into an excel file in specific places, I’m currently copy and pasting to do this but I’m hoping to find a way to automate this perhaps using scripts.

I have some web development and JavaScript experience so I’m ok if this will involve some coding on my side to get this done but I thought I would get some advice on avenues I could try, as I’ve never attempted anything of this nature or know if there is any relatively inexpensive software ($100 - $1000) doing something like this already?

Is there anything I should know when embarking on research on how to accomplish this, any avenues you would suggest about how I solve the problem most efficiently and easily?

Any information would be much appreciated!

  • 1
    Are you talking specifically about PDF Form data rather than PDF scans? – Steve Barnes Dec 16 '16 at 16:28
  • They are normal PDF files so I'm guessing they are just scans. – Josh Phillips Dec 16 '16 at 17:59
  • Setup the webpage so it uses PHP or jQuery to store the data directly into a database. Then connect via odbc to the database in excel. That only solves 1 issue. – cybernard Dec 28 '16 at 4:40

Problem Statement

Basically your problem space resolves to 3, possibly 4, steps:

  1. Receiving the order - I am guessing this might be an email.
  2. Extracting the correct data from the PDF if it is in that format
  3. Extracting the correct data from a webpage format if that
  4. Inserting the extracted data into the excel worksheet

The tools that I would use to work through this in the Python world.

Getting the "Order" files

Since you aren't specifying how you are getting the orders I will assume that they are coming by email, python has a number of email clients available including a built in email module, examples of how to interact with mail servers abound. However, since you are manifestly getting the data somehow I will leave how to automate that to another question.

Extract from PDF

Within the Python ecosystem there are a number of pdf manipulation tools but probably the best fit for this is PDFMiner.Six which specialises in text extraction including the exact location of texts in a page. An example of using PDFMiner can be found on SO here.

Extract from Webpage, (HTML)

Just about everybody in the python world who needs to parse html data turns to BeautifulSoup4 which can parse just about any web page, (even badly formed ones), to extract the data. Presumably the data that you need to extract are in named fields within the page - if so you will find that they are easy to extract.

Insert into Excel

Depending on whether you need the Excel 2010 XLSX files or Excel 97/2000/XP/2003 compatible XLS files you will find the tools that you need to crate & manipulate excel files in either openpyxl or, for the older file format, XLWT/XLRD/XLUTIL.

One of the nice things about these is that they do not require a copy of Excel to create or manipulate the contents of excel files.

Price & Availability

All of the above tools are free, gratis & open source, and are cross platform so can be installed on just about anything from a Raspberry Pi to a supercomputer cluster, even on MS Windows.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.