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.
No comments:
Post a Comment