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 |
Microsoft SQL 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 <default>. 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
Summary
This article described importing Excel Spreadsheet data into a
database table on SQL Server 2008. It also described how text data in
the source can be mapped to char on the SQL Server. The default
conversion during copying is nvarchar. While the explanation in the
article is long, the process of transferring is very fast and takes only
a few steps.
|