{"id":7395,"date":"2017-10-09T14:08:35","date_gmt":"2017-10-09T14:08:35","guid":{"rendered":"https:\/\/www.investintech.com\/resources\/blog\/?p=7395"},"modified":"2019-08-22T13:04:58","modified_gmt":"2019-08-22T13:04:58","slug":"how-to-clean-large-pdf-datasets","status":"publish","type":"post","link":"https:\/\/www.investintech.com\/resources\/blog\/archives\/7395-how-to-clean-large-pdf-datasets.html","title":{"rendered":"How To Clean Up Large PDF Datasets"},"content":{"rendered":"

\"Analyzing<\/p>\n

For big data analysts<\/a>, working with clean data is a must. The major hurdle, though, is actually cleaning that data. Right now, analysts are spending more than half of their time cleaning up unstructured datasets. And if you aren\u2019t an advanced expert with cleaning datasets, just knowing some basic data cleaning tasks becomes even more crucial.<\/p>\n

Datasets can represent a large variety of information. From government and healthcare data to demographic and financial numbers, datasets come from all different areas. They also come in all different forms, like the PDF format. Getting it into a form you can manipulate is your first goal– and your biggest challenge.<\/p>\n

The PDF format isn\u2019t easily editable. In addition, it may contain hundreds of pages, consist of tables that span the entire file, be scanned in from a hard copy document, be created from an Excel spreadsheet, or be protected against copying and pasting.<\/p>\n

You need to be able to analyze that locked down data. But how do you get started?<\/p>\n

<\/p>\n

Extracting Large PDF Datasets<\/b><\/h2>\n

The key to working with PDF datasets is to extract that data. A PDF to Excel<\/a> conversion is usually the first step. We all know that PDF conversion results can give you some post-conversion manipulation work. However, there are tools, like Able2Extract Professional, that will get most of the legwork done before the data is extracted, giving you less work in cleaning that PDF data.<\/p>\n

\"Customizing<\/span><\/p>\n

It can tailor your PDF data extraction with a custom PDF to Excel conversion feature that lets you manually adjust rows and columns, delete headers and footers, select portions of the data to convert, decide how content is treated across columns. Once everything is set up the way you want in Excel, you can convert it as usual.<\/p>\n

Most of the unwanted data will be already eliminated from the conversion results. All you have left to do is deal with is the more refined data cleaning process in basic go-to data cleaning tools like Microsoft Excel.<\/p>\n

Basic Tips For Cleaning PDF Datasets <\/b><\/h2>\n

Whether you\u2019re learning more about Excel or are just learning how to clean data, here\u2019s a look at how to accomplish some of the most basic data cleaning tasks. This includes deleting duplicates, getting rid of blank cells, deleting extra spaces, re-organizing data in columns and rows, or cleaning the text\u2019s formatting.<\/p>\n

As a rule of thumb, don\u2019t forget to make a copy of your original dataset. This way, if you make a mistake spanning the entire file, you can always revert back.<\/p>\n

Deleting Duplicates<\/b><\/h3>\n

It isn\u2019t uncommon to have duplicates due to data entry errors. However, these must usually be weeded out before any analysis of the data can happen.<\/p>\n

    \n
  1. Select your data.<\/li>\n
  2. Go to Data > Remove Duplicates<\/strong>.<\/li>\n
  3. In the resulting dialog, select the Column(s) from which you want to remove duplicates and click OK.<\/li>\n<\/ol>\n

    \"Removing<\/p>\n

    Getting Rid Of Blank Cells <\/b><\/h3>\n

    In PDF datasets, it\u2019s common to have tables that convert improperly due to text splitting among the columns, causing the data to shift. You can pick out and delete all blank cells.<\/p>\n

      \n
    1. Select the entire data range.<\/li>\n
    2. Hit F5 >Special.<\/b><\/li>\n
    3. In the dialog that appears select Blanks<\/b>. This will highlight all blank cells in the spreadsheet data.<\/li>\n<\/ol>\n

      \"Selecting<\/p>\n

        \n
      1. Right-click on one of the blank cells and select Delete<\/b> from the context menu.<\/li>\n
      2. In the dialog select how you want the other cells to be shifted once the blank cells are deleted.<\/li>\n<\/ol>\n

        \"Deleting<\/p>\n

        Deleting Extra Leading and Trailing Spaces<\/b><\/h3>\n

        One common issue with large PDF datasets is that it may contain extra spaces. Instead of going to each cell one by one, \u00a0you can get rid of leading and trailing spaces simultaneously.<\/p>\n

          \n
        1. Create a blank column adjacent to the column with the data you want to clean.This will be your Helper column.<\/li>\n
        2. In this Helper column, enter the formula =TRIM (cell reference or text<\/i>) in the cell adjacent to the data you want to be cleaned.<\/li>\n
        3. Press Enter.<\/li>\n
        4. Continue to copy the formula into the blank cells of the Helper column to delete the extra spaces in the original cells as needed.<\/li>\n
        5. Once done, replace the data in the original column with the Helper column containing the cleaned data.<\/li>\n<\/ol>\n

          Re-organizing Data In Columns and Rows<\/b><\/h3>\n

          Oftentimes, PDF datasets you receive won\u2019t be organized in the way you need them to be. Organizing the rows and columns can be a quick task. Below are a few basic things you can do in Excel:<\/p>\n