New Decade - New Versions

Hey there folks,

I wouldn't call 2020 a great year (so far), but some good things are happening:

After a *long* break, I took some time to update all of the Apps to make a leap and get some refreshed features.

Some of the features across all products include:

All:

- SQL Authentication types extended to Azure/AAD

- Contact UI options updated

- Engine/Framework updates

- Launcher Dark Mode support

- Core Engine & Target Framework updated

Server Repository:

- Added support for various Azure AAD Authentication

- Fixed multiple thread-related UI issues

- Fixed column conflict issues

- Modified internal default timeout

- Using Repository Database version 2.6

Logger:

- Removed redundant code

- Better handling of log file writing conflicts

As a reminder, all products are completely free! 

Enjoy!

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:

View image | gettyimages.com

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.

SQL ClipTable now offers better JSON handling and SQL Server 2016 support

There's a new version of SQL ClipTable released today, which has multiple engine updates as well as two important features:

• SQL Server 2016 support (tested against CTP's, will make sure RTM is fully supported upon release)
• JSON format support has been extended to support a 'serial' JSON file, which is basically a file on which each line represents a new JSON-based row, with a similar schema.

As a reminder, ClipTable lets you import almost any data, of any shape and form into your database. Supporting all major relational database engines available today, including SQL Server, Oracle, MySQL, PostgreSQL, FireBird and more.

Want to see the extended JSON support in action?

Here's an example for such a file: Try Copy the code below & Paste into ClipTable

{"accept_lang":"hr-HR","object_id":"149987216","object_pos":1,"client_ip":"212.92.194.156","correlation_id":"0AF0010A-B638_AC110006-0050_560B25D6_1FDF5E8-0001","event_ids":["496538287"],"gallery_object_count":15,"gallery_title":"Jimmy Page","gallery_type":"personality","gallery_type_id":"208663","geo":{"city_name":"","continent_code":"EU","coordinates":[15.5,45.1667],"country_code2":"HR","latitude":45.1667,"longitude":15.5,"timezone":"Europe/Zagreb"},"has_ads":true,"referer":"http://www.google.hr/url?sa=i","signal_brand":"dl","signal_event":"object Viewed","signal_source":"dl","spectrum":"dl-prod-producer-51","timestamp":"2015-09-30T00:00:03Z","url":"http://www.retrofile.com/detail/news-photo/","user_agent":"Mozilla/5.0 (Windows NT 6.1; Trident/7.0; rv:11.0) like Gecko","user_id":"0","visitor_id":"5bf0ba11-2373-44c0-b4d3-b0ccad75f4fa"}
{"accept_lang":"en-US,en;q=0.5","object_id":"160011161","object_metadata":{"object_family":"Creative","object_type":"image","editorial_products":[],"has_nudity":false},"client_ip":"12.204.91.250","correlation_id":"0AF0010A-E611_AC110006-0050_560B25ED_1FE07C4-0001","geo":{"city_name":"Riverside","continent_code":"NA","coordinates":[-117.4031,33.9059],"country_code2":"US","latitude":33.9059,"longitude":-117.4031,"timezone":"America/Los_Angeles"},"queried_keywords_by_clause":null,"referer":"http://www.doccolabs.com/","signal_brand":"dl","signal_event":"object Details Viewed","signal_source":"dl","spectrum":"dl-prod-producer-51","timestamp":"2015-09-30T00:00:02Z","url":"http://www.doccolabs.com/blog","user_agent":"Mozilla/5.0 (Windows NT 6.1; WOW64; rv:38.0) Gecko/20100101 Firefox/38.0","user_id":"0","visitor_id":"0a67788b-0022-455c-85b3-6e420787a069"}
{"accept_lang":"en-GB","ads_viewed":[8.2626951421e+10],"object_id":"175554847","object_pos":12,"client_ip":"81.108.221.204","correlation_id":"0AF0010A-C299_AC110006-0050_560B25E6_200D348-0001","event_ids":["526528765"],"gallery_object_count":15,"gallery_title":"Una Healy","gallery_type":"personality","gallery_type_id":"5523039","geo":{"city_name":"Royal Leamington Spa","continent_code":"EU","coordinates":[-1.5047000000000001,52.3123],"country_code2":"GB","latitude":52.3123,"longitude":-1.5047000000000001,"timezone":"Europe/London"},"has_ads":true,"referer":"http://www.doccolabs.com/purchase/","signal_brand":"dl","signal_event":"object Viewed","signal_source":"dl","spectrum":"dl-prod-producer-51","timestamp":"2015-09-30T00:00:01Z","url":"http://www.doccolabs.com/purchase/","user_agent":"Mozilla/5.0 (Windows NT 6.1; WOW64; Trident/7.0; rv:11.0) like Gecko","user_id":"0","visitor_id":"c902ed6c-96ff-4778-a85e-ca9fc7a9dd13"}
{"accept_lang":"en-US,en;q=0.8","object_id":"112027935","object_pos":0,"client_ip":"108.54.239.65","correlation_id":"0AF0010A-0B19_AC110006-0050_560B25F5_20070C2-0001","event_ids":["75330148"],"gallery_object_count":15,"gallery_title":"Charmed Celebrates 150 Episodes and First Season on DVD","gallery_type":"event","gallery_type_id":"75330148","geo":{"city_name":"Jamaica","continent_code":"NA","coordinates":[-73.7939,40.7148],"country_code2":"US","latitude":40.7148,"longitude":-73.7939,"timezone":"America/New_York"},"has_ads":true,"referer":"http://www.google.com/","signal_brand":"dl","signal_event":"object Viewed","signal_source":"dl","spectrum":"dl-prod-producer-51","timestamp":"2015-09-30T00:00:01Z","url":"http://www.google.com/flights","user_agent":"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/45.0.2454.101 Safari/537.36","user_id":"0","visitor_id":"44e8d178-4022-4495-9ea7-412e797ae96b"}
{"accept_lang":"hr-HR","object_id":"149987216","object_pos":1,"client_ip":"212.92.194.156","correlation_id":"0AF0010A-B638_AC110006-0050_560B25D6_1FDF5E8-0001","event_ids":["496538287"],"gallery_object_count":15,"gallery_title":"Jimmy Page","gallery_type":"personality","gallery_type_id":"208663","geo":{"city_name":"","continent_code":"EU","coordinates":[15.5,45.1667],"country_code2":"HR","latitude":45.1667,"longitude":15.5,"timezone":"Europe/Zagreb"},"has_ads":true,"referer":"http://www.google.hr/url?sa=i","signal_brand":"dl","signal_event":"object Viewed","signal_source":"dl","spectrum":"dl-prod-producer-51","timestamp":"2015-09-30T00:00:03Z","url":"http://www.retrofile.com/detail/news-photo/","user_agent":"Mozilla/5.0 (Windows NT 6.1; Trident/7.0; rv:11.0) like Gecko","user_id":"0","visitor_id":"5bf0ba11-2373-44c0-b4d3-b0ccad75f4fa"}
{"accept_lang":"en-us","client_ip":"107.220.186.143","correlation_id":"0AF0010A-F236_AC110006-0050_56297879_115B0572-0001","gallery":{"object_count":8,"related_celeb":0,"title":"46th Mt. San Antonio College Relays - April 18, 2004","type":"event","type_id":"75059348"},"geo":{"city_name":"Cumming","continent_code":"NA","coordinates":[-84.1404,34.1483],"country_code2":"US","latitude":34.1483,"longitude":-84.1404,"timezone":"America/New_York"},"referer":"https://www.google.com/","session_id":"276095c4fc","signal_brand":"dl","signal_event":"Gallery Viewed","signal_source":"dl","spectrum":"dl-prod-producer-52","timestamp":"2015-10-23T00:00:00Z","url":"http://www.doccolabs.com/","user_agent":"Mozilla/5.0 (iPhone; CPU iPhone OS 9_0_2 like Mac OS X) Safari/601.1","user_id":"0","visitor_id":"81ebb423-bda9-4707-b897-7cc86d58de91"}
{"accept_lang":"","client_ip":"81.158.98.51","correlation_id":"0AF0010A-1680_AC110006-0050_56297882_115B0C02-0001","gallery":{"object_count":15,"related_celeb":0,"title":"Prince Harry Visits Nottingham","type":"event","type_id":"166614266"},"geo":{"city_name":"Dundee","continent_code":"EU","coordinates":[-2.9766,56.461],"country_code2":"GB","latitude":56.461,"longitude":-2.9766,"timezone":"Europe/London"},"referer":"http://www.google.com/search","session_id":"9f1bcbe265","signal_brand":"dl","signal_event":"Gallery Viewed","signal_source":"dl","spectrum":"dl-prod-producer-52","timestamp":"2015-10-23T00:00:03Z","url":"http://www.doccolabs.com/detail/news-photo/prince-harry-signs-his-name-with-a-paint-pen-on-a-large-news-photo/167504061","user_agent":"Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko; Google Web Preview) Chrome/27.0.1453 Safari/537.36","user_id":"0","visitor_id":"1e65bdc1-7862-438c-99e5-55a0d89b7cbd"}
{"accept_lang":"","client_ip":"173.88.159.62","correlation_id":"0AF0010A-3757_AC110006-0050_56297881_1148FB3A-0001","gallery":{"object_count":15,"related_celeb":0,"title":"Sports Contributor Archive 2015","type":"event","type_id":"530880577"},"geo":{"city_name":"North Ridgeville","continent_code":"NA","coordinates":[-82.019,41.3895],"country_code2":"US","latitude":41.3895,"longitude":-82.019,"timezone":"America/New_York"},"referer":"http://www.google.com/search","session_id":"7125b47d91","signal_brand":"dl","signal_event":"Gallery Viewed","signal_source":"dl","spectrum":"dl-prod-producer-52","timestamp":"2015-10-23T00:00:02Z","url":"http://www.doccolabs.co.uk/detail/news-photo/andre-mcgee-of-the-louisville-cardinals-in-action-during-a-news-photo/493431424","user_agent":"Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko; Google Web Preview) Chrome/27.0.1453 Safari/537.36","user_id":"0","visitor_id":"fc049f9e-22c0-408f-8466-fa4ee376ba10"}