Create SQL Server tables from within Excel

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. 

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.

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:

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.


Feedback and Knowledge Base