How do you convert an Adobe pdf file to Excel?

[yoda313]

Hi,

I'm an accountant and I work with statements all the time. A lot of them come in pdf files which is a pain because you can only print them out and work by hand with them. Until recently I discovered my work computer has Microsoft Office Document Imaging software which allows me to print to the microsoft program and then import to Microsoft Word which then can be copied to Excel. However not everybody in my department has this program installed (some are still using win 2000 and this seems to be only preinstalled on our XP machines).

Is there a more direct way to take a chart of numbers from an Adobe pdf file and transfer them directly to excel for easier manipulation? (I don't think we're allowed to run programs off the net so I don't think offering seperate conversion programs from a website will work - only if its from microsoft or adobe itself).

Anyone have an easier way to do this then the one I mentioned with the Office Imaging software?? (by the way it does it through OCR detection - and it actually does it pretty well. I did a dump to word and was able to then copy it to excel. However I tried a direct dump to excel but unfortunately it only pasted it to a single cell and was all jumbled to the point where TEXT-TO-COLUMNS was ineffective). Thanks for any tips.

[redwudz]

Sort of basic, but I just use the 'Select' tool in Adobe Reader, select a column and save to the clipboard, then open Excel and paste in one column at a time. Kind of tedious with 256 columns, though. If you had the full Adobe Acrobat, you may be able to output the formatting, but I don't have it on this machine.

At work, I have a dual monitor setup and those kinds of cuts and pastes are a lot easier. I seem to remember 'translating' a whole .pdf file, complete with the formating to Excel with Acrobat, but it's been a while.

[JohnnyMalaria]

I've done this from time to time for my wife (who is also an accountant and is forever receiving pdfs).

It depends on the version of Acrobat Reader. The Text Select tool can be configured to copy a table (i.e., multiple columns), a single column etc.

In my experience, though, it is a bit clunky and gets rather frustrating, especially if there are empty "cells" in the original pdf table.

Sometimes, I end up printing the pdf and then rescanning it with OCR. So much for the paperless office!

If you can get Adobe Acrobat (instead of Reader), you can also save the pdf in rtf and txt.

[Nelson37]

Under absolutely NO circumstances would I advise a customer to rely on OCR for converting large quantities of accounting information.

Unless you are willing to pay someone to error-check each and every single number. OCR is just quite simply not good enough for this purpose.

If you are simply looking at quotes or something, fine. If profit and loss statements are created, and/or if actual checks are cut based on this data conversion, the data need to be accurate.

There are text-conversion programs that are free or quite inexpensive. There is also the full version of Acrobat. One of these is a necessary business tool for the job at hand, management needs to be made aware of the need and time/cost savings involved in obtaining such a tool.

[JohnnyMalaria]

Under absolutely NO circumstances would I advise a customer to rely on OCR for converting large quantities of accounting information.

Unless you are willing to pay someone to error-check each and every single number. OCR is just quite simply not good enough for this purpose.

I wholly agree. When I've done the OCR, it is for small numbers of pages and I always verify the accuracy. I never assume it is correct.

I apply the same caution to the PDF copy-and-paste, hence my comment about the somewhat clunky nature of the process. Adobe's formatting of the copied text often defies logic.

PDF To Excel Converters

There are a number of PDF to Excel converters, often with free trial versions:

http://www.google.com/search?as_q=%22pdf+to+excel%22&hl=en&newwindow=1&num=100

[yoda313]

Sort of basic, but I just use the 'Select' tool in Adobe Reader, select a column and save to the clipboard, then open Excel and paste in one column at a time.

Thanks. I'll have to try that. Though from what I remember we have a very limited adobe reader version - not much options. I'll try to remember the select feature.

Thanks for the other advise to everyone

[ahhaa]

a couple suggestions:

Sun Microsystems offers Open Office for free; it has '3rd party' powers, opens and creates pdf files natively and should allow direct to spreadsheet in a single operation.

Sun is fully as legitimate as Microsoft, they are the Java people for one thing... and have been around as long if not longer than M$.
Another reason I recommend it is that it is current, and will stay current because it is free. This would allow your fellow workers to both 'move up' & work on a cross-platform product (for clients with Apples, etc.) Check it out: http://www.openoffice.org/

second, you might eyeball Google Office; or even suggest to them this is a conversion that needs to be done right... they are amazingly friendly when given an inte4resting task!

[rallynavvie]

I tried this with Acrobat 7 Pro:

As long as the source PDF was output to PDF from the speradsheet file (as opposed to scanned to PDF from paper copy) and contains the text data within it you can save it as a Word file right out of Acrobat. The Word file will contain each table from the original PDF which you can then select and easily copy into Excel. You'll probably have to resize some columns but the data is there and delineated properly.

[Nelson37]

You are an accountant, right?

Take a pdf home and download some tools from the Net. Find one that will output text to Excel, should be several. Get the site discount for the number of copies of the tool that you need. Do the same calculation for Acrobat pro, and present the numbers to management for immediate necessary action.

I am absolutely floored that an accounting department would be using OCR in large quantities for business-critical data rather than a direct text-conversion tool. This is completely unacceptable. My guess would be that this violates some type of CPA rule, and may even be illegal.

I would be flipping out if I discovered one of my customers doing this. Conversion tools would be on every PC that needed them within 20 minutes, THEN options would be selected and the customer could decide what features they wanted to buy. But the OCR conversion would stop ASAP.

[yoda313]

?????? Not really I'm just looking for an easy way to get adobe to excel.

In the end I'll probably just keep doing what I've been doing... Printing out the damn pdfs........

[JohnnyMalaria]

You're assuming that OCR is being used blindly and without verification.

Though it may not be common, it certainly isn't illegal. As long as you have the right procedures in place, OCR can be a valuable tool. There's nothing in Yoda313's original post to assume that this isn't the case.

Also, I think you have some naive assumptions about how businesses decide whether to buy time-saving software or not. Regretfully, the expectation is that the employees will have to just put in the hours. After all, it's cheaper to get a salaried employer to bust their guts by doing 80 hours a week than to buy the software that will let them get it done in 40. (Neglecting, of course, the cost of hiring replacement staff when the burnt-out ones leave).

I've come across that time and time again. On paper, the argument is a no-brainer. But it doesn't get you anywhere.

My wife is a CPA and a Controller - it never ceases to amaze me the nonsense she has had to endure in a variety of small- to mid-size businesses. Usually from the owners.

In larger businesses you then run into the problem of software validation and compatibility testing. Downloading and installing Open Office, for example, becomes non-viable either because employees have locked-down computers and/or because the company has very strict policies about validating new software before allowing it on the company's systems.

[skulldrinker]

check out this yahoo group for Excel

http://tech.groups.yahoo.com/group/ms_excel/

[Nelson37]

Naive my ass. I was being trained to run a million-dollar business when I was six years old. I know all about how to calculate business costs, such as the value of free advertising. This particular company has some kind of dumbass in charge.

If there is no double-check, this is just plain stupid, and I would bet money it violates standard accounting practices. If there is, it is incredibly wasteful of time and resources. If these clowns control your investment plan, yank it out and put it somewhere else.

He mentioned an entire department, presumably more than two machines. Appropriate tools would not cost more than one day's salary for one employee. From the number of people involved, I assume there are some large dollar amounts involved on the accounting end. One OCR screwup could cost huge amounts of money.

As I said, get the numbers and put them in front of management. Likely is they simply do not understand the issues involved. The accountants job is to explain the numbers. This would include explaining the possibility that the numbers have no basis whatsoever in reality, why this is so, and how it could be solved for relatively little expense, in very little time.

If I did work for this company, became aware of this practice, could not get it changed, and continued to do sub-contract work on their PC's, I would get WRITTEN verification that I had informed them of the unsafe nature of this practice and advised them to change it. If the OP is a CPA, I would advise them to do the same as IMO their accounting certification may be in jeopardy.

This is not a rickety staircase, this is an open elevator shaft.

Suggest to management that they just take pictures of various piles of money and make guesses at the amounts, and enter that information into Excel. At least these numbers would be based on an educated guess, the amount of an OCR error is bound only by random chance.
The probability of a single-digit dropout that is not noticed through human error is an eventual certainty. What is the biggest number you deal with, and what happens if you add or remove a single digit on the top end?

[JohnnyMalaria]

Human data entry clerks can also make mistakes and they can go undetected.

Your assertions about OCR being illegal in the accounting world are just nonsensical.

You could apply the same argument to using Excel to do the accounting calculations. Is it validated? Does it maintain audit trails?

In my other life, I work for a multi-billion dollar organization with hundreds of thousands of employees. It is a very heavily regulated industry. We can be audited by our regulators at the drop of a hat. I can assure you that as you ascend the ladder, politics beats common sense. Maybe not in a million dollar company. Heck, my wife has worked for such small entities who, for some bizarre reason, insisted on using Quickbooks. The egos of the owners precluded rational decision-making on their part. I've written Excel macros for her because the owners just flat-out refused to buy a $200 piece of software that would do the same thing. When she (finally) left the company, they had to pay me for the development cost of the macros - otherwise they would have disappeared along with her!

[ahhaa]

I'm defintiely setting up that duelling ground over to 2nd Life!:]

[Nelson37]

Gee, I love a good reader.

Find me an assertion that it is illegal. "MAY BE" is not an assertion. Neither is "I would bet money", though you would have to notice that that was concerning standard accounting guidelines.

My opinion comes from the assumption that having a blindfolded chimpanzee enter in the numbers would violate some sort of guideline or rule, without rigorous error-checking.

It is my understanding that dispersing financial data that is known to be potentially inaccurate can be, in fact, a violation of the law. This can vary depending on whether the company is publicly or privately held, does work for the federal government, or is providing the data to some third party.

It is my understanding that Excel calculations are accepted to conform to standard accounting guidelines, understanding that the individual calculations themselves are correct.

There seems to be some question among CPA's I have talked to about whether the audit trail is an absolute requirement, or a recommendation, of standard practices. Types of reports maintained along with backups and size and type of ownership of company seem to vary this.

I have many customers for whom Quickbooks is an extremely valid solution. Quite rational for smaller companies. Some companies cannot justify multiple thousands of dollars and a trained accountant to enter in relatively simple data.

I would positively assert that this practice of using OCR for accounting data entry, when there is a much more accuract and inexpensive, also much faster, solution available, is stupid.

I would also positively assert that an Excel macro which appeared on an employee's PC would be considered to have been created on company time, represent employee work product, and therefore be the property of the company. In the absence of a specific agreement to the contrary, or proof of creation outside the company, the "disappearance" of such work product would constitute theft.

The politics of working for dumbasses is one reason I did not take over the family business. Running my own business, I can choose not to work for these types of idiots. That does not guarantee that I still don't have the same problem.

[JohnnyMalaria]

Gee, I love a good reader.

So do I. More of that later.

Find me an assertion that it is illegal. "MAY BE" is not an assertion. Neither is "I would bet money", though you would have to notice that that was concerning standard accounting guidelines.

Hmmm. "I would bet money" is an assertion. At least based on the dictionaries I have.

1. To affirm; to declare with assurance, or plainly and
strongly; to state positively;

I would also positively assert that an Excel macro which appeared on an employee's PC would be considered to have been created on company time, represent employee work product, and therefore be the property of the company. In the absence of a specific agreement to the contrary, or proof of creation outside the company, the "disappearance" of such work product would constitute theft.

You, me and the ass make three.

It didn't appear on an employee's PC. It appeared on my personal, aging laptop that I provided to my wife to allow her to do work at 8pm in the evening because her short-sighted and ego-tripping owner-employers wouldn't give her the right tools to do her job during normal working hours. It also carried a clear and unambigious copyright statement. When she left, she explained that in order for her replacement to pull out of the hat the miracles that she did, they'd need something akin to the macro I provided to her.