During the course of a recent project I needed to take data that was provided to me in an Excel spreadsheet and compare it to data that was stored in a database table. Both the table and the spreadsheet were quite large and I needed to determine if any records exited in the database that did not exist on the file.
Since I didn’t know the source of the Excel document and I couldn’t recreate the query used to pull the data I decided the best course of action was to import the Excel document into the database as it’s own table so that I could run a quick compare script to see if any of the records were missing from the file.
Importing Excel documents is easy using the SQL Server Import and Export Wizard in SQL Management Studio.
The first step is to connect to the SQL Server housing the database where you want to import the data. Locate the database from the drop-down list, right-click it and select Tasks–Import Data.
Using the SQL Server Import and Export Wizard you can now begin the process of importing the data from the file into a new table on the database.
On the first screen you will need to set the source of the data you will be importing. Since the data is stored in an Excel file you will need to select Microsoft Excel from the Data source drop-down. Next, click Browse and select the actual file you want to import.
By default the option to note that the first row has column names will be checked. If the data you are importing does not have column names assigned in the first row of the spreadsheet you will need to uncheck this option. The column names in the new table will correspond to the column names assigned to the Excel spreadsheet. Click Next.
The next step is to choose the destination. Generally this window requires no changes as the default destination should be SQL Server Native Client 11.0 (depending on your version of SQL Server).
The server name and database will also be populated based on the server and database you selected to import into in the first step. Click Next.
On the next screen make sure the option to Copy data from one or more tables or views is selected. Click Next. You could also write a query to limit what data from the Excel file is copied over to the new table but for this example we want to import everything.
The next screen will show you the worksheet name from the source file along with the destination table that will be created. In this example the source is the EmployeeRef worksheet in the Employee_Data.xls file and the destination will be a newly created table named EmployeeRef$. By default the table name will always match the worksheet name.
From this screen you can also configure the destination tables column names, datatypes and size. Click the Edit Mappings button to view the mappings and make any necessary changes.
In most cases you can just leave the default setting and make any changes once the table has been created and populated. Its up to you how you want to manage it. Click either OK or Cancel to close the Column Mappings window and then click Next to finalize the import.
On the Save and Run Package window the option to Run Immediately will be checked. Unless you want to save the package for a future time, simply click Next at the bottom of the screen.
The next screen will give you a summary of the actions that are about to be taken.
Review the details to make sure you configured the import properly. Once you are ready to import the data click Finish.
The import will run. Once it has successfully completed you should see a screen similar the the following confirming that all steps were completed.
Its best to close out the file on your computer before importing. If the file is open it can cause the import to error out.
Finally, lets confirm that our data is now in the database. The following script will query the newly created table in the TSQL2012 database.
SELECT * FROM dbo.[EmployeeRef$]
ORDER BY EmployID
The output should look something like this:
I now have a new table containing all the data from the Excel spreadsheet. From here I can do my compare and determine what data is missing from the file or table.