Create SQL Server tables from your data in Excel
1. Download and install the SQL Spreads Excel Add-In
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:
- Click a cell within your data range
- Press CTRL + T (hold down the CTRL-key and press T)
- The Create Table dialog will be shown:
- Click OK and your data will now be in an Excel table.
3. Create the table in SQL Server
- Click the Create SQL Table button in the SQL Spreads tab in Excel:
- 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.
- 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.
- Click OK to create the table.
- 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
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.