Export and Normalize Excel to SQL Server

The problem:


You have an Excel that you want to import to your database and the data in your Excel form are not normalized. The Excel sheet may contain names of cities, countries, products etc. which are repeated. To import the data into your data base you have to manually restructure the data to a relational database in order to reduce data redundancy and improve data integrity. (Do you know that Excel can hold up to 50 millions rows? Click here).


The solution:


We created an application called 'Transform Excel to SQL'. Transform Excel to SQL takes the data from the columns of the Excel sheet that you want to normalize and decomposes the columns into less redundant (and smaller) ones.  The end result is transforming the data into a database schema then importing it into your database in a very easy, fast and efficient way.

Who needs this application:


Developers can use this Add-In to migrate data from any legacy software.

Managers can use this app to transform raw data to meaningful reports.

Marketing department can grab data from a web page, paste to an Excel sheet and load to a database for further process.

In the video below you can see an example of how the Add-In works.


In the screenshoots below you can see the procedure step by step.

  1. Click “Synergy USA LLC” ribbon add-in.
  2. Click “Transform Excel to SQL”.
  3. The window “Transform Excel to SQL Server” will show.
  4. Select your Server Name & Authentication.
  5. Click the “Refresh” button and select your Database.
  6. Click the “Test Connection” button, so as to test the Database connection.

  1. Click the “Transform Columns” tab.
  2. Select the first column that you want to transform.
  3. Click “Load Selected Column”.
  4. Click “Create Table from Column".

Now the column you have selected will be created and will look like that.

Repeat steps 2, 3, 4 in the previous picture for all the rest of the columns that you want to transform, if any.

When finished, Excel will look like that.

  1. Click the “Transform Excel” tab.
  2. Click the “Load Columns” button.
  3. For any column that you have reformed, on the “Data Type” choose “int”. For all the rest choose “nvarchar(50)”.
  4. Click the “Transform Excel” button. Now SQL is normalized.

On SQL under Tables you will have your Master Table and all the rest of the columns you have transformed.

Create a new view, add all the tables.

Link every secondary table, using the auto generated Foreign Key, with the master  table respectively. Select from the MasterTable the columns that you want and from the other tables "Descr". Execute SQL. You data are represented now in a normalized form.