The Goal:
Have you ever had a lot of data on a PDF but no way to take that text and put it on a spreadsheet for ease of use? Have you been manually slogging through the process with no hope of salvation? Slog no more! Because I’m going to show you how to make some slick looking spreadsheets using free software!
The Resources:
- Microsoft Excel
- Microsoft Word
The Process:
The first thing you should do is open the PDF in your browser or any software that supports OCR. Once you do that, simply select all the text on the PDF and copy it.
Next, open Microsoft Word and paste all the text there. Once you do that, save the document as a text file(.txt).
Now, open Microsoft Excel. Go to the search bar and type in “Get data from text”. Once you do that, a window should open. Select the text file you saved and click OK.
After a pause, a window should open. This window will let you choose if you want your file to be delimited(columns separated by a certain character) or have a fixed width. Then click on next.
On the next screen, you can pick your delimiters. You can even add your own delimiters by picking the other option. You can choose more than one at the same time. Now press next.
Here, you can choose exactly what kind of data each column holds. Select a column and pick of the types of data present in the box.
Now choose whether you want the data to go to a new worksheet or an existing worksheet. Once you’ve selected that, click on OK.
Now your data has been imported into a spreadsheet!
This looks really useful when you have a long text and don’t want to put the data column by column (and row by row). I am wondering how easy it is to clean up the data once you get the Excel file ready, especially in your specific case with the Zoobook.
What I did is clean it up in word first and then imported to excel. I couldn’t find any easy and convenient way to clean it up so I just read through everything and cleaned it up manually. One thing that did help was the replace feature in word, which I used to remove unnecessary full stops and commas that messed up my excel sheet.
That makes sense. Thank you!
I liked your tutorial on how to convert text from a PDF to Excel! Although I am not very good with using Excel so an extra step as to showing how to import a file into the sheet would have been nice and showing a picture of what option to choose because it asks you what type of file you want to put into wether it is a CSV file or etc. I eventually figured out what to do but people who aren’t so savvy with this type of software may have trouble figuring out simple stuff like that. Overall, good job and thanks for the lesson!