SQL Server
Three Options
In this report, we discuss these three options for importing data from Microsoft Excel to a SQL Server database:
1. SQL Server Management Studio 2. SQL Server Integration Services (SSIS) 3. XLhub Excel Add-In
The right option depends upon one’s situation. The factors to consider are: 1. Is the data in the spreadsheet organized as a table?
2. Will the export be performed by a DBA or programmer? 3. Is the export going to be performed on a recurring basis?
An additional consideration may be whether there is a need to import data back from SQL Server into Excel.
Outline
Some Options for One-Way Import (from Excel to SQL Server):
SQL Server Management Studio Import and Export Wizard
SQL Server Integration Services
XLhub
Factors to Consider:
Structure of the data in Excel (tabular vs non-tabular)
Recurrence of import
Programming skills of user
Two-Way Connections (Import + Export):
Decision Flowchart
Below is a decision flowchart for identifying the appropriate option for importing data from Excel to SQL Server.
Does the import need to happen on
recurring basis?
Does the user have SQL or Programming
Skils?
Does the user have SQL or Programming
Skils? Is the data in Excel
organized as a table (rows and
columns)? Yes No Yes Yes No Yes No or or
“Frequency of
importing and the
technical capabilities
of the users are
important drivers”
This section discusses each of the three options in some detail.
SQL Server Import and Export Wizard
SQL Server Management Studio is the console for managing a SQL Server installation. It is rich with functionality for managing databases and performing actions on the data contained in the databases. Management Studio contains a utility for importing and exporting data from various sources.
This utility, called “SQL Server Import and Export Wizard” is meant for the non-programmer.
The wizard is initiated as a task for a database within Management Studio.
The wizard walks the user through a series of dialogs, ultimately requiring the mapping between the Excel file and the target database table.
The data in the Excel file must begin in the top row of the sheet or be in a named range or table.
“Data in the SQL
Server is stored in
database tables.
Each tool requires
that a mapping be
performed between
the Excel dataset and
the SQL Server
database tables”
The column mapping dialog shows the columns in the Excel file (source) and corresponding columns that will be created in the SQL Server table (destination).
The wizard essentially enables the formation of a SQL statement that is executed to create the desired table and columns.
requires a programmer to set up the export process.
If the data in the Excel file is not organized in a tabular form (simple rows and columns) then SSIS may be the only choice.
SSIS enables the creation of recurring process for reading an Excel file (or a CSV), parsing the data, and loading the data into appropriate columns of a table in a SQL Server database.
Additional logic for data cleansing can also be included in the import process. The entire flow for reading, parsing, transforming and loading the data can be defined as a flowchart and then executed.
The programming logic is defined within Microsoft Visual Studio. Once the program (referred to as a “package”) has been created and tested, it can be deployed for execution.
Creating data import jobs within SSIS requires a substantial amount of training, and a good knowledge of SQL is usually mandatory.
Once a SSIS package has been created, it can be scheduled to run automatically.
XLhub
XLhub is a Microsoft Excel add-in that enables a user to import and export data between Excel and SQL Server. It is designed to be used by non-programmers. The XLhub functionality is accessible from within Excel via its own ribbon.
Using a wizard-driven process, XLhub performs the following tasks:
a) Analyzes the data in the Excel file to determine the column names and data types
b) Creates a table within SQL Server that corresponds to the table in Excel c) Pushes data from the Excel file into the SQL Server table
Each time data is saved, it is appended to the same table. This way previous versions of the data are preserved
d) Reads data from the SQL Server into Excel, and provides the user to select which previously saved version to retrieve into Excel
Two-Way Connections
A two-way connection between Excel and SQL Server enables saving data from Excel to SQL Server and also retrieving data from SQL Server into Excel. With this capability, applications can be rapidly developed which utilize an Excel front-end and a SQL Server back-end.
“Two-way
connections between
Excel and SQL Server
open up a world of
possibilities for
creating
collaborative
departmental
applications”
Multiple users can save data from their Excel spreadsheets to the same database table. This way, consolidations are simple, and data can be protected using security policies within SQL Server. XLhub enables two-way connections.
Multi-user applications that lend themselves well to this capability include:
Budgeting
Forecasting
Financial Consolidations
Timesheets
More information on XLhub can be found at www.xlhub.com.
About Metric-X:
Metric-X is an information technology firm whose mission is “to help companies manage and analyze their data.” Metric-X specializes in developing Business Intelligence solutions based on the Microsoft toolset, including SQL Server, Visual Studio and Microsoft Office. Within BI, Metric-X focuses its capabilities on the “last mile of BI”, with solutions aimed at the widespread use of Microsoft Excel among BI users. Metric-X was founded in 2000, and is located in Perrysburg, Ohio.
Metric-X, LLC
28304 Cedar Park Boulevard, Suite D Perrysburg, Ohio, 43551 U.S.A. (248)495-4925 www.metricx.com
