The small scale approach: How to cleanup messy data, standardize enrich data

You ever faced to the following challanges:

• You  have a huge amount of master data needs to be cleaned up. Over time the same terms are used differently, buildup of the names using different styles, etc.
• Have to split data currently part of one column into different one, e.g. a product name contains dimension which should be processed in a different column in the future, etc.
• common faults in the data needs to be harmonized (multiple whitespaces, upper vs. lower case letters, etc.)
•  Transform well structured data into a table for relational usage of the data, e.g.
• The data needs to be enriched based on public available or internal services, e.g. you have the address of your customer available and want to add latitude, longitude to display amount of customer on a map.
• You have to merge two sets of data from two different systems and want to identify same entries even if the both lists does not share a common key.
• ....

For those tasks you have several ways to work with.

One interesting tool which is very useful in those kind of tasks is "OpenRefine" (formerly known as Google Refine).
You can easily download the latest version and install it on your local client (or server) and start it just by following the easy installation instruction provided on the homepage.

A good overview of the main functions is shown in : https://github.com/OpenRefine/OpenRefine/wiki/Screencasts

The tool is very easy to use. It works for huge amount of data and for the most tasks it is pretty fast  (especially compared to doing the same with spreadsheet software). It is more likely used for ad-hoc tasks compared to fully automated and repeated tasks in the enterprise - but it provides  handy functions to reapply and reuse already created rules.

For more sophisticated analyses goes out of the scope of one columns and rows of one table tools like RapidMiner are more sufficient. But those kind of tools require a bit more upfront invest to get the first problem solved.

HTML5 to PDF to HTML5

PDF is still the most used format to distribute electronic documents - especially for "paper based" workflows. There are obvious reasons still using PDF even if evolving technologies brought alternatives to the table.

On the other hand HTML and related technologies (namely CSS and JavaScript) has made a major step forward to produce enterprise looking documents with additional value a PDF cannot provide.

I do not want to compare the pro and cons - just want to mention well suited tools to convert from one format into the other.

HTML5 to PDF

Whenever you have to create "paper based" workflows based on data you already have a HTML (5) version from the best tool I'm aware of is PrinceXML.  Styling is done using CSS and JavaScript - no additional technology you need to learn.

This technology is not able to fully replace advanced rendering technologies like XSL-FO (and corresponding commercial layout engines) or even more advanced ones based on vendor specific layout definitions.
But there are many use-cases where you just have the HTML you have to distribute as PDF (e.g. confirmation of orders, archiving of a business transaction, etc.) this product might enable this for a reasonable price.

Note: there are many commercial and open source alternatives out there for this approach - non of them are as robust as the mentioned solution based on my experience.

PDF to HTML5

You already have PDF created within your workflow and need HTML5 to support more advanced integration in your content distribution you can tryout PDF2HTML5 Converter.

There are different options you can choose from how the content is transformed (one of them is creating SVG instead of HTML5 but this is a different story). The most useful conversion mode if the content should be further integrated creates:

• one HTML file per page
• all distinct block of content is stored within a div tag
• layout is applied using CSS
• some JavaScript

Note: The created HTML5 does not result in a responsive design (using absolute positioning and non semantic markup) - but that is more or less by design and cannot be solved in a general purpose implementation.

Both tools are commercial but in case you have a good use-case for one of the two mentioned conversion the price is reasonable. Try out first - both tools provide ways to easy try out the results.

Search and Replace on multiple files

Search & Replace is a common task in data processing environments. You cannot avoid to build process your data to replace or add a word, syntax or even multiple lines of text in several different resources.

If the task can be fully automated, means there is a unique algorithm to transform a resource A to A' based on the content of A than you will look for available methods and tools supporting you to do this kind of operation.

Methods

Regular expressions are very powerful rules to express not only finding common pattern in text based resources but also a good foundation to replace or extend existing content.
Compared to simple phrase based pattern most imaginable rules can be expressed and used as a source for the required transformation.
But regular expressions come with high cost of complexity. It is very likely to defines rules which results in "false positives", means matches that you didn't want to match.

Tools

Doing Search & Replace in the file system on multiple resources (files) is easy for IT people using linux tools like grep, ....
On Windows you also can install those tools and make them a powerful foundation for those kind of operations (see http://gnuwin32.sourceforge.net/packages/grep.htm).

TextCrawler

But not all people like to become an IT expert for simple replacing the term "foo" with "delicious" . On Windows you can use TextCrawler for this. One of the best UI based tools I'm aware of.

It provides

• simple phrase based operation "Replace phrase A with B" on multiplier files
• more complex regular expression based operation 
• and in addition a fuzzy search operation for more advanced search operations

It also supports the use of Unicode characters to search and replace and the processing of files encoded in Unicode (utf-8, utf-16).

To avoid false positives you can

• preview the hits before actual performing the replace operation
• use a dedicated regular expression tester to see what exactly match and what will replace

 Search and Replace is something you have to consider harmful but in case you have to do it on a Windows Desktop using this tool is something I can recommend.

transform pdf to word / excel

online conversion from pdf to word / excel is available from http://www.pdftoword.com/. you have to upload your pdf and will receive the converted word / excel file as long as the result is smaller than 12 MB. result looks good for most use-case. of course you have to manual rework based on the intension you have with the result but content, images and layout is converted and accessable for subsequent tasks.

i like that kind of advertising - a easy to use subset of the functionality is available for free and if i like the feature and want more enterprise features i will probably pay the price.