How to Export a Table From a PDF File into An Excel Spreadsheet


Table of Contents
  • Introduction
  • Part 1: Basic Exports
  • Part 2: Maintaining Original Document Format
  • Part 3: The Text Import Wizard 

  • Introduction

    Why Are So Many Documents in PDF?

    Many statistical documents are becoming more widely available to the public. Often, these documents are available only in a PDF file. Portable Document Format, created by Adobe Acrobat, has the following advantages:

    • It's very easy to convert almost any document into PDF while preserving the document's original format, i.e. layout, fonts, graphics etc.
    • Converting documents into HTML often alters the documents original format.
    • PDF files are accessible on the World Wide Web.
    • PDF files are device-independant, i.e. the files can be viewed on any computer platform as long as the Adobe software is present.
    • The Adobe Acrobat Reader can be downloaded for free from Adobe's web site.
    • PDF files are often compressed, taking up less space than the original file.
    • PDF files have good viewing quality, and have additional properties such as magnification and paginated viewing.
    Unfortunately, the Adobe Acrobat Reader does not have any editing options, nor does it have the versatility of a spreadsheet program. This makes it difficult to perform statistical analyses and manipulations on any data within the document. One way to overcome this problem is to convert the document (or parts of it) into another format, for example, you can select a table from the PDF document and export it into an Excel Spreadsheet. Although the new document will not contain any macros upon exportation, you will have the capacity to create them yourself. 
     

    What Software Do I Need to Convert a PDF Document into Another Format?

    In order to export a PDF file into another application you will need Adobe Acrobat 5.0 installed on your workstation. This is not the same software as the Adobe Acrobat Reader. Adobe Acrobat 5.0 is a much larger software package that provides many functions unavailable in the Reader (e.g. converting virtually any document into PDF). Unlike the Reader, Adobe Acrobat 5.0 cannot be downloaded for free.

    If you do not have Adobe Acrobat 5.0 you can come to the EDRS office where we will make the conversions for you, provided that you are part of the McGill faculty, staff or students. Click here for the EDRS office information.
      


    Part 1: Basic Exports

    The following instructions describe how to export a table from a PDF document into an Excel spreadsheet: 
     

    1. In Adobe Acrobat 5.0, open the PDF document. Make sure you are working in Adobe Acrobat 5.0, as opposed to the Adobe Acrobat Reader

    NOTE: PDF documents on the Web open in the Reader, even if you have Adobe Acrobat 5.0 installed on your workstation. If you would like to export a table from a document on the Web, you must first save the PDF document within your browser (Select File -> Save As... etc.) and then open it in Adobe Acrobat 5.0
     

    2. From the main toolbar, select the Table/Formatted Text Select Tool (or click Shift+V). This tool's icon, , will appear in the main toolbar once it has been selected from the drop down menu.

    NOTE: If you prefer to export a column of data as opposed to an entire table, you can use the Column Select Tool as opposed to the Table Select Tool and continue with the instructions. 
    3. Select the table that you would like to export by clicking and dragging a box around the table. 
    • Right click within the box. A menu window will appear.
    • Make sure that the Table option is checked  in this window (see diagram below). 
    • Select Save As…from this window (select Copy if you are using the Column Select Tool).


    4. Select a file in which to save your table, and type a name for the file containing your table.  In the Save As Type window select ANSI Text (*.txt) and click on Save.


    5. Now minimize Adobe Acrobat 5.0 and open Excel
    • In Excel, select File -> Open…
    • In the Open window, make sure under Files of Type: Text Files is selected. This way you will be able to see your .txt document. 
    • Select your table.
    • Select Open.


    6. The Text Import Wizard window will appear. 
    • Observe the Preview of File window. View the entire table by scrolling to the bottom of the file.
    • If the preview of the file looks normal (e.g. see Figure 1 below) and has the correct formatting throughout the entire file, skip the following three instructions and continue with Step 10 in Part 3: The Text Import wizard
    • *IMPORTANT*: If the preview of the file does not look like the data from the original table (e.g. see Figure 2 below) the following instructions (Steps 7 through 9, titled Part 2: Maintaining the Original Format of Your Document) will describe to you how to maintain the original formatting of your document. Once your data is properly formatted you can begin the export process again with this new data.
    Figure 1

    Figure 2


     

    Part 2: Maintaining The Original Format of Your Document

    Sometimes when you select material in an application to be copied into a different application, the original formatting of the material is lost during the import process. An easy way to ensure that the original format is retained is to print your document to the Acrobat Distiller. The Acrobat Distiller is a simulated printer that is available to use in Adobe Acrobat 5.0. When a document is printed to the distiller, a printed copy is NOT produced. Instead, a copy of the original document is created in PDF format. That is, the Acrobat Distiller exports documents from virtually any application into PDF format.

    The Acrobat Distiller has print job options that can be customized to change the quality or size of a document. The default print job options are automatically set to cover the basic customization needs by the users. The default setting for the print job allows the original formatting of a document to be maintained during an import.  For more information on the Acrobat Distiller, open Adobe Acrobat 5.0 and select Help -> Acrobat Help, and see Acrobat Distiller Options, page 46.
     
     

    7. In Adobe Acrobat 5.0, open the document which you would like to send to the Acrobat Distiller. NOTE: The Acrobat Distiller is not going to print your document, it going to export the document into PDF format.
    • Select File -> Print… 
    • Under Printer Name: select Acrobat Distiller
    • Select OK.


    8. The Save PDF File As window will appear. 
    • Select a folder in which to save your Distilled PDF. 
    • Type a name for the Distilled file (e.g. OriginalName.Distilled.pdf). 
    • Select Save.


    9. The distilled file will open automatically in your Adobe Acrobat window.  Return to Step 2 in Part 1: Basic Exports.

     
    Part 3: The Text Import Wizard


    10. In Step 1 of the Text Import Wizard;
    • Select Delimited. 
      • Delimited means that row and column fields are separated by some form of data (e.g. commas or semicolons) which you specify. 
      • The other option is Fixed Width, which is when the fields are separated by fixed amounts of space. 
      • For Fixed Width, you manually select the column widths.
    • Select Start import at row: 1 
    • Select File Origin: Windows (ANSI). 
    • Select Next>.
       
      11. In Step 2 of the Text Import Wizard;
      • The most common tables will have only tab selected for Delimiters.
      • The options you should choose may vary from table to table so you may want to try different options while viewing the preview of your table. 
      • Once you've selected the best options for your table, select Next>.
      12. In Step 3 of the Text Import Wizard;
      • Adjust the Column Data Format if need be. 
      • Select Finish.
      13. Your table will appear in the correct format. 
      • Adjust any extra properties if necessary. 
      • Save your document, under Save As Type: select Microsoft Excel Workbook. This will give it an .xls extension.
      • You may now create macros, perform statistical analyses or format your data as needed.



    Prepared by: Tira Cohene & Anastassia Khouri
    Last updated:  February 15, 2002  (TC)
    ©  EDRS - McGill University