Thursday, February 2, 2012

Get to know – ClipTable


ClipTable objective is quite simple – 
revolutionize the way you’re importing data into your SQL Server.



New: ClipTable video is now available - Watch it!



Why:
I’ve been serving as a DBA for quite some time, performing production as well as development tasks.
One of the things I always found quite time-consuming was import data, any data, into a SQL table format for additional processing.
While it always works at the end, this type of task never became easier.

Here’s a typical scenario:
  • One of my customers hands me an Excel file containing his user data (let’s say, 50,000 users).
  • I open the Excel, see that it contains multiple sheets; one of them contains the data I need.
  • I choose the direct import option, using SSIS.
  • Trying to use Excel as input, getting an error and after a while, recalling that there’s an issue of reading Excel files when 64-bit office is not installed (hence missing the Jet/ODBC driver)
  • OK, giving up on the direct Excel->SQL path, going for the CSV->SQL
  • <Taking a deep breath, making another cup of coffee>
  • Saving sheet as CSV
  • Trying SSIS again with flat-file input
  • Battle continues with:
    •   Finding the right row/col delimiters
    •    Finding the best data-types (number, dates, text etc…)
    •    Finding the correct length for each column
    •    Hoping there are no “text qualifiers” (because this means further work and rules out working with bcp)
  • Importing… Finger crossed…
  • Worked? Great
  • Didn't work? Got an annoying unreadable SSIS error? Go back and try again…


Pardon for not drawing the above as a flowchart, but I hope you’re getting the point.
This is just one example. I’ve personally had so many of them I could write an entire article on the subject.
I could - but instead, I’ve spent my time simply writing a solution for this annoying problem.

So, the incentive was there, and my objective was to eliminate the frustration on this process.

How:
First, I wanted to eliminate the need of having input drivers for any format (my example above was Excel, but the table may come from PDF, Word, any webpage etc…)
So the natural solution was to use the clipboard. Instead of re-inventing the wheel, we let Windows do the work of translating anything to a readable text format.

Next, we need to “understand” the data;
This is where ClipTable’s smart engine is being used – analyzing the reverse-engineering the live data to understand the correct fields, types, delimiters etc…

And last – I wanted to quickly access any of the SQL Servers within my organizations (i.e. the “Destination”) and for that, I’ve implemented the Shared Repository component (which will be discussed on a different blog post)

The Result:
Taking the same example from the 1st paragraph, this could have taken way over an hour.
By using ClipTable, I simply:
  • Copy the users data from the relevant sheet
  • Letting ClipTable do the field/type analysis and turn it into a SQL formatted table
  • Choose my destination
  • Done!


That’s it – no more than 2 minutes and all done.

If you want to see more, go to our website (doccolabs.com) and get it – it’s totally free.
Hope we managed to help you save at least an hour on your next import task.

Let us know J