Wednesday, November 11, 2015

How ClipTable works?

A few days ago I was approached by someone at work, who got some files needed to be exported into one of our SQL Servers.
The file format wasn't very intuitive, and caused this coworker a lot of different errors trying to use the "Import and Export Wizard". (Which is a basic SSIS task behind the scene)

Anyway, after being asked to "give it a shot", I naturally went to ClipTable, and of course, got this done in less than 2 minutes.

So this coworker asked me how this was done so fast, and what he was doing wrong.
I've explained that I was using a different tool. (and explained a bit about ClipTable)

"Yeah, but how was ClipTable able to determine how to turn the file into a table without any configuration?" she asked.

So, I answered and decided to write this post about some behind-the-scene ClipTable techniques.

If you're new to the tool -
ClipTable is a very fast SQL Import tool, which allows you to turn almost any tabular data into a SQL Server/Oracle/MySQL/PostgreSQL/Firebird Table.

Here is a behind-the-scenes glimpse to ClipTable's engine (that could) and some of the things it's able to do:

So, when you import from memory (clipboard) or file, here is a partly list of what's happening:

[The engine analyzes the data, answering the following questions]

  • Is this an XML string?
    • Can this turn into a 2-d table?
  • Is this a JSON data?
    • Is this a serial JSON data? (repeated columns)
    • Which parts of the data can turn into a 2-d table?
  • Is this a textual data?
    • Does the data seem valid?
    • Analyzing the string pattern,
      what is most likely to be the column/row delimiters?
    • Does the data include headers?
    • Are there white-spaces to be removed?
  • Are there any duplicate field names?
  • What is the best Data Type for each column?
  • What's the fastest method to write the table into the destination server?
    • Does this server support Bulk Copy/Insert?
    • Can insert statement sent in a big batch?
These engine features help ClipTable turning this:

Into this:

Into this:

Into this:

In just a few seconds.