• SQL Spreads Blog

  • How to import data from Excel to SQL server

    May 8th, 2019

    Intro

    In many businesses, the number-one choice to edit, analyze and store data is still Microsoft Excel.

    But, as today’s businesses become more and more dependent on data, people often feel the need to structure how they store the data and how they can collaborate with other users.

    An easy way to structure the data for most mid- to large-size companies is to move the data into a database such as Microsoft SQL Server. Microsoft SQL Server gives the structure and reliability needed to trust your data.

    New reporting and analytics tools, such as Power BI and Qlik, also drive the demand for simple methods to manage and update structured and centralized data in a simple way.

    Data in SQL Server will be hard to update and manage for most non-technical users, as the database tools are complex. There are methods used by most Business Intelligence and Data Warehousing projects, such as importing files using SSIS (SQL Server Integration Services). These methods still require technical and database skills to set up. And, in the end, the final solution can have latencies requiring hours or days to complete an import and needing hand-holding by technical personnel to clear out import issues with wrong data types, etc.

    What if there was an easy way for non-technical users to use their much-loved Excel to update and import data from Excel to SQL Server? What if they could use Excel to collaborate with others using the same structured and centralized data stored in Microsoft SQL Server?

    In this article, I will show you how a modern Excel Add-In can be used to collaborate and allow even non-technical users to import data from Excel to SQL Server in a simple, secure and structured way.

    Importing new data or updating existing data

    In many cases, importing a spreadsheet file from Excel to SQL Server is done to update existing data in the database. Sometimes just a few rows of data need to be changed, but sometimes thousands of rows need to be updated.

    In other cases, new data exists outside of SQL Server. In most cases, it is easy to get the data to Excel, but hard to import the data from Excel to SQL Server.

    We will describe both the scenarios here: when data exists in the SQL Server database and end-users need to update the data from Excel, and when the users have new data that needs to be imported from Excel to SQL Server.

    Importing data from Excel to SQL Server

    This section describes how to use the SQL Spreads Excel Add-In to set up an Excel spreadsheet that can be used to insert or import new data from Excel to SQL Server.

    Once the spreadsheet is set up, you can save the Excel file and re-use it later for importing another set of data from Excel to SQL Server. The connection to SQL Server and all the settings will be stored in the Excel document.

    You can also share the Excel document with colleagues who need to import data from Excel to SQL Server without knowing anything about how the document is connected to SQL Server. Be aware that your colleagues will need to have the SQL Spreads Excel Add-In installed in order to import and update the SQL Server data using the Excel documents you have created.

    These are the steps to import new data from Excel to SQL Server:

    1. First, open Excel and go to the SQL Spreads tab. Then, click the Design mode button:
    2. The SQL Spreads Designer will open on the right side. Click the Edit button to connect to your SQL Server:

    3. To connect to your SQL Server databases, enter the name of your SQL Server in the SQL Server name field in the connection dialog. Use the same name as you use when you connect to SQL Server from SQL Server Management Studio.
    4. When you have connected to SQL Server, your databases and tables will be shown in the SQL Spreads Designer panel on the right side. When you select a table, the table data will be loaded into Excel:
    5. To import new data into SQL Server, scroll down to the first empty row and either type in your new data or paste a set of rows copied from another Excel workbook:
    6. Lastly, click the Save button to import your new rows of data to the SQL Server database.

    Updating existing data in SQL Server

    Importing data from Excel to SQL Server is often done to update existing rows in SQL Server. The SQL Spreads Excel Add-In can also update existing data in SQL Server directly from Excel.

    This section describes how to use the SQL Spreads Excel Add-In to set up an Excel document to update existing data in a SQL Server database using an Excel document.

    When you are done with your setup, you can save the Excel document and share it with your end-users, who can then use the Excel document to update and manage their data in SQL Server themselves. To use the Excel document to update the data in SQL Server, they will need to have the SQL Spreads Excel Add-in installed.

    These are the steps to follow to set up an Excel document to update data in SQL Server from Excel:

    1. Click the Designer mode button in the SQL Spreads tab in Excel:
    2. The SQL Spreads Designer will open on the right side. Click the Edit button to connect to your SQL Server:
    3. To connect to your SQL Server databases, enter the name of your SQL Server in the connection dialog. Use the same name as when you connect to SQL Server from SQL Server Management Studio.
    4. You will now see your databases in the Designer panel on the right side. Click a database and select the table to use. Once the table is selected, the data will be loaded into Excel. The data is now ready to be updated, so you can just change a value and click Save to update the data from Excel to SQL Server.
    5. You can easily fine-tune how your SQL Server table is shown in Excel. In the Columns tab in the SQL Spreads Designer, you can:
      • hide database columns so they don’t show up in Excel
      • make database columns read-only so end-users cannot edit the data in these columns
      • re-order the columns
      • update the name of a database column that is shown in the header row in Excel

    6. Once you are done fine-tuning your table, update a value in the Excel spreadsheet and click Save to save the changed value to the database table in SQL Server.

    You have now created an Excel document that you can use to update and manage SQL Server data using Excel.

    You can save your Excel document and share the document with your end-users. Your end-users can use your Excel document to update and manage the data in SQL Server themselves.

    When an end-user receives your document, they can:

    1. Open the Excel file and refresh the data from SQL Server
    2. update the data in the Excel file
    3. save the changes back to SQL Server

    Please be aware that the end-users will need to have the SQL Spreads Excel Add-In installed on their machines.

    Improving your data quality

    When you update or import data from Excel to SQL Server, you need to know that the data entered into Excel meets the data requirements set up in the SQL Server database.

    Data validation

    The SQL Spreads Excel Add-In automatically checks every entered value against the data types in the SQL Server database as soon as the value is entered in Excel. End-users will get immediate feedback if their data do not meet the requirements in the database and can correct the data before it is imported from Excel to SQL Server.

    If you need to import a larger set of data and paste thousands of cells into Excel, the value in every column and row will be validated against the data types in the SQL Server database before the values are inserted into the database.

    All updates that are done in the database are done using transactions. If any value in an update does not meet the requirements of the specific database table, the update will be rolled back, and the user will be informed about what needs to be changed to meet the requirement of the database table.

    Drop-down lists and relational tables

    Relational tables with columns containing key values from other tables can easily be updated. You can define a drop-down list that fetches readable text values from a relational table, and then let the user select the text value in the drop-down list instead of typing cryptic codes.

    Change tracking

    To be able to go back and see who has changed a value in the database, you can turn on change tracking in SQL Spreads.

    Change tracking will track the changes that are made to each row and will keep track of which user and what time and date a row was last changed.

    Sharing and collaboration

    SQL Spreads is designed for collaboration. All settings for how the Excel document is connected to SQL Server and how each column in the database is presented in Excel are stored in the Excel file.

    You can share your SQL Server-connected Excel document like any other Excel document. All the settings will follow with the document.

    Editor mode

    To allow non-technical end-users to update and import data into SQL Server from Excel, SQL Spreads can be installed in a special Editor mode were all the Design features are locked out.

    How to avoid conflicts when updating data from Excel to SQL Server

    When multiple users are working with the same SQL Server table at the same time, you want to make sure that they do not overwrite each other’s data.

    SQL Spreads has built-in conflict detection that will warn users before any possible conflicts occur.

    The user will be shown what values the other user has entered and can choose to keep that data or overwrite it with the new changes. 

    Working with large databases in SQL Server

    When working with large databases containing hundreds of thousands – or even millions – of database rows, you need a way to bring down that data set to a manageable number of rows.

    Using the SQL Spreads tree filters, you can let your end-user select which data to load from the SQL Server database and which data to update from Excel to SQL Server.

    Tree filters allow end-users to select exactly which data to fetch from SQL Server. Tree filters can be populated from the data in tables in SQL Server to create a dynamic data-driven filter, which allows you to work with tables containing millions of rows of data.

    For advanced use cases, the tree filters can be customized to show different items for different users. This can be used, for example, to give a business unit manager access only to the data for her or his specific business unit in a corporate database containing all business units’ data.

    Benefits of updating and importing data from Excel to SQL Server

    There are several benefits from using Excel as an interface for working with data in SQL Server.
    Some of these benefits are:

    • End-users often having extensive experience working with data in Excel.
    • Lead times can be shortened when users can update the data themselves.
    • The quality of the data will improve when the people who own the data can take direct responsibility for it.
    • Excel solutions are fast to deploy and can be easily updated and maintained by in-house personnel if something needs to be changed down the line.

    SQL Spreads will not replace the standard line of operation systems, but it will be a great complement to move up a step in all those cases when data is either kept unmanaged in SQL Server or stored in personal Excel files.

    Summary – conclusion

    Importing and updating data from Excel to SQL Server using the SQL Spreads Excel Add-In is a fast and easy way to implement a user-friendly data management solution. End-users who work with Excel on a daily basis can now use their favorite data tool to also update and import data in Microsoft SQL Server.

    The new SQL Spreads Excel Add-In is available for download from this page.

Leave a Reply


No comments yet. Be the first!

Envelope Iconsmall picture of an envelope signifying email Need help?
Need help?
Arrow Downa small down arrow to minimize chat box
Send us a message
We love hearing from you.