Create SQL Server tables from your data in Excel

1. Create SQL Server tables from your data in Excel
   
   
   
   The SQL Spreads Table Creator is a new feature in SQL Spreads 5.0 to create SQL Server tables directly from within Excel and pre-load them with the existing data in your Excel spreadsheet.
   

   
   

   The Table Creator makes it possible to create SQL Server tables in just a few clicks. For those of you who are more advanced users, it is possible to fine tune the creation of the table like setting the exact data types on each column. 

   1. Download and install the SQL Spreads Excel Add-In

   1. Download the SQL Spreads Excel Add-In from here.
      
      
   2. Run the installer and follow the instructions. If you need any help you can take a look at this Installation Guide. 
      
      
   3. Restart Excel and go to the SQL Spreads tab in Excel: 
      
      

   Note: The Table Creator features are free to use without a license. The Trial will limit the features to Update existing tables in SQL Server from Excel.

   2. Prepare your data in an Excel

   
   The SQL Spreads Table Creator use the Excel table format to fetch extra information about the data formats, so you will need to have your data in an Excel table like this:

   
   
   If you don’t have an Excel Table it is super easy to create one:
   

   1. Click a cell within your data range
      
      
   2. Press CTRL + T (hold down the CTRL-key and press T)
      
      
   3. The Create Table dialog will be shown:
      
      
      
   4. Click OK and your data will now be in an Excel table.

   3. Create the table in SQL Server
   

   1. Click the Create SQL Table button in the SQL Spreads tab in Excel:
      
      
      
   2. If this is the first time you use SQL Spreads or the Table Creator, you will see the dialog to connect to your SQL Server.
      
      
      Fill out your server details and click OK
      
      If you need help with connecting to your SQL Server, please see this article about how to connect to SQL Server.
      
      If you have used the Table Creator before, the last used SQL Server connection will be used by default.
      
      
   3. Once you are connected to SQL Server, you will see the Create SQL Table dialog:
      
      
      Specify the table name and which SQL Server database to create the table in. SQL Spreads will always remember the last used table.
      If needed to change to another SQL Server click the Edit button next to the server name.
      
      
   4. Click OK to create the table.
      
      
   5. A confirmation dialog will be show that the table was created and how many rows of data that was inserted into the table:
      

   4. Fine tuning the data types to use in SQL Server
   

   
   

   To specify the exact data types to use, click the More Settings button at the bottom of the Create SQL Table dialog:
   
   
   The dialog will expand and you will see all the columns in Excel:

   
   
   To use another data type, click the Type drop down list and select another data type:

   
   
   For the following following data types you can also specify the exact size of the column in the database:
   

   • Varchar and NVarchar: Can be set to the max number of characters or MAX to auto-adjust the size based on the content in the column.
     
     
   • Decimal and Numeric: The length must be entered as '(7,3)' where the first number is the total number of figures and the last number is the number of figures after the decimal separator.

   Using a new table in the SQL Spreads Designer
   

   
   To see the new table in the SQL Spreads Designer, click the Design mode button twice to close and reopen the Designer.
   
   
   This will refresh the list of databases and tables, and you will see your new table in the list.