Importing MS Excel data to SQL Server 2008

The Import and Export Wizard

The import and export wizard was available even with SQL 2000 has remained an important tool for exporting from and importing into SQL Server data from many different kinds of data sources. It can also be used for transferring data between non-Microsoft data sources. In this article, an example of transferring an MS Excel spreadsheet data to SQL Server 2008 is described. In any of the transformations it is important to realize that data types used in data sources are not exactly the same and that there are differences to be reckoned with. The basic steps to take are to indicate the source of data and the destination to which it needs to be transferred. In order to match the differences some mappings may be necessary if the source and destination are not both SQL Servers.
The MS Excel file PrincetonTemp.xls used in this example is a simple spread sheet data that shows the temperature variations during a year and the maximum recorded temperature. The data type used for the column ’Month’ is text and of the others are numbers.

Figure 1: PrincetonTemp.xls

Invoke the Import and Export Wizard

Bring up the Import and Export wizard from Start | All Programs | MS Server 2008 | Import and Export Data (32 bit). This pops-up the Welcome Wizard as shown. Make sure you read the explanations provided.

Figure 2: Welcome page of Import and Export Wizard

Choose source of data

Click Next. The default page gets displayed. In the 'Choose a Data Source' page click on the handle along the data source and choose Microsoft Excel file as the data source as shown.

Figure 3: Choosing the source of data

Click Next. The 'Select the source from which to copy data' shows up. Use the Browse...button to bring in the location information of PrincetonTemp.xls to the window as shown. The Excel version displayed by default (Microsoft Excel 97-2003) is proper for the MS Access version used in this article. Keep the 'First row has column names' option checked. Note that the MS Access 2007 is not supported.

Figure 4: Choosing the data file

Choosing the data destination

Click Next. The 'Choose the Destination' page shows up with SQL Server Native Client 10.0 as default and the resident server as Hodentek2\Mysorian. The server is configured for Windows authentication. Accept the defaults. In case your server is configured for SQL Server authentication you need to have the information ready. The database is displaying . Click on the handle and choose a database from the drop-down list. Herein TestNorthwind is chosen. You can choose any database including the tempdb. Note that you can begin to create a new database as well, if you choose to do so by using the New...button.

Figure 5: Choosing SQL Server 2008 as the destination

Copying the table

Click Next. The 'specify the Table Copy or Query' page of the wizard shows up. Since we are transferring only one table, accept the default option, ‘Copy data from one or more tables or views ‘.

Figure 6: Choose option to copy a table

Click Next. Since sheet one has the data place check mark for 'Sheet1$' as shown. Only Sheet1 has data in this XLS file. Modify the destination column to read dbo.PrincetonTemp instead of the default [dbo].[Sheet1$] as shown.

Figure 7: Choosing a sheet from the Workbook

Click Next. In the 'Save and Run Package' page of the wizard accept the defaults shown. You could also save it as a package as well for later use.

Figure 8: Choose ‘run’ immediately option

Click Next. The 'Complete the Wizard' page gets displayed. Check if the information is correct (this is a summary of options you have chosen). If it is not correct you can hit the back button and move back to the pages you visited earlier in the reverse order.

Figure 9: The completed wizard

Click Finish. The program starts running and you should see a progress window displaying 'Performing Operation...' as shown.

When the operation is completed you should see the following window and you can keep a copy of the report as to how the import was executed using the Report drop-down button.

Figure 11: Successful execution of transfer

The import in this case was successful as shown above. If there is an error there should be a hyperlink to the message in the Message column of the above window, presently the message is ’12 rows transferred’. Close the wizard. The transfer is finished.

Verifying the import

Open the Microsoft SQL Server Management Studio and login to display the database engine using your Windows credentials. Expand the databases node and the TestNorthwind database node as shown.

Figure 12: A new table in the database

Data type mismatch and the fix

Also check if the data is brought in correctly as shown by right clicking the dbo.PrincetonTemp table and choose 'Select Top 1000 rows'. You can see that the Month names are all showing 'Null'. The ‘text’ data type in the XLS file became nvarchar type.

Figure 13: Null data in the ‘Month’ column

Modify the default mappings

In order to fix this, you can use either Drop table statement or right click and choose delete to delete the table from the TestNorthwind database. In the Delete Object window click OK. Refresh the Tables node by right clicking the Tables and choosing refresh. Now the imported table is gone.
Repeat the process that you did earlier and when you come to the stage shown in Figure.6 click on the table Edit Mappings...button. The Column Mappings page shows up as in the next figure

Figure 14: Column mappings window

The month column data type for the destination is nvarchar (255). The Source had 'Text' as data type for this column. We need to cast it properly. Click on nvarchar in the 'Type' column and change it to ‘char’ as shown. Click OK. Change destination table name from[dbo].[Sheet1$] to [dbo].[PrincetonTemp] as done previously. Click Next.

Figure 15: Modify mapping

In the 'Save and Run Package' page accept defaults as previously. Click Next. The 'Complete the Wizard' page shows up. Click Finish. You get the wizard announcing 'The execution was successful'. Close the wizard.
Refresh the Tables node of the Northwind database in Management Studio. Now right click the PrincetonTemp and choose to select top 1000 rows as before. You will see that all the data in source is in the destination.

Figure 16: The Source data is imported correctly