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
