Migrating Data Between Excel 2000 and SQL Server 2000

Environment: ADO, databases

Overview

Most of our day-to-day managerial analysis activities take place in Excel spreadsheets. After assimilation and analysis, data is generally discarded, although ideally we would have liked to keep it. Thus, we are unable to maintain this data because we do not have a proper data store. Even if we did have a data store, it is not always possible to transfer all the data from the Excel sheets to the data store. Again, even if we have all the data in a data store, to analyze data, it may still have to be transferred to an Excel sheet to draw graphs or charts as part of the analysis activities. Thus, we have a situation where we need a tool that would transfer data from Excel sheets to a persistent data store and vice-versa.

How to Make It Work

The application launches a small window with a menu bar. The menu bar provides two options:

  1. Migrate data from an Excel worksheet to SQL Server



    Click here for a larger image.

    • Selecting this option opens a dialog box where the user can provide information for Excel and SQL Server.
    • The user can provide the Excel file name and the Worksheet/Range (which contains the data that is to be migrated) name.
    • The SQL Server options, such as server name, username, password, database name, and finally the destination table name where the data retrieved from the Excel file will be stored then can be provided.
    • After all the parameters are specified, the user may click the "Migrate Data from Excel to SQL server button" to begin the migration.
    • If the application is successful in migrating the data, a message box ID displays, confirming the same; otherwise, an error message may pop up.


    Note:

    • All errors are logged in "C:\MigratorLog.txt".
    • If the table name, provided by the user, does not exist, a new table will be created in the database specified.
    • The Data in Excel sheets has to be in named Ranges or in Worksheets in tabular form.

  2. Migrate data from SQL Server to Excel worksheet


  3. Click here for a larger image.

    • Selecting this option opens a dialog box where the user can provide information for SQL Server and Excel.
    • The SQL Server options, such as server name, username, password and database name then can be provided.
    • The user can provide the Excel file name and the Worksheet/Range (which would contain the migrated data) name.
    • The query that the user wants to fire on the SQL server database is to be provided in the text box provided.
    • After all the parameters are specified, the user may click the "Fire Query" button to begin the migration.
    • If the application is successful in migrating the data, a message box ID displays, confirming the same; otherwise, an error message may pop up.


    Note:

    • All errors are logged in "C:\MigratorLog.txt".
    • The Query text box currently supports only DML statements where a result set can be retrieved. Although it does accept any DDL statements, it should be avoided because no result sets are retrieved in the process.
    • If a Range name provide by the user does not exist, a worksheet is created of that same name.
    • The Excel file where data is to be migrated needs to be closed before the migration operation can begin.

How It Works

The application uses two core classes, ADO_DataBase and LogFile. The LogFile takes care of logging all errors and ADO_DataBase takes care of the migration part. Two classes, ADO_DataBase_SQL and ADO_DataBase_Excel, have been derived from ADO_DataBase, which takes care of the migration functionality specific to SQL Server and Excel, respectively. The class ADO_DataBase can be extended to include Oracle or any other database as well, as long as there is ADO support for that database.

Limitations

  • User can be debarred from entering DDL statements in the query dialog box.
  • Some error checking may have to be improved.
  • While reading data from an Excel Worksheet/Range, the data needs to be in tabular form.
  • User can be made aware that the Excel file where data is to be migrated needs to be closed before the migration operation can begin.
  • The core classes do not support UNICODE.
  • Have been tested only in Excel 2000 and SQL Server 2000.

Known Issues

Excel does not support some data types that are supported by SQL Server. Thus, while migrating data from SQL Server to Excel, there may be some problems in mapping the data types. This may lead to unpredictable results. However, because all errors are logged, it would be easy to figure out the exact nature of the error. More testing in this regard is required.

Downloads

Download demo project - 22 Kb
Download source - 18 Kb


Comments

  • DTS

    Posted by Legacy on 04/15/2003 12:00am

    Originally posted by: Saugata

    This application was meant to be generic so that it can support data migration between Excel on one hand and SQL Server or Orcale on the other. In fact it should support any other DB as long as it supports ADO. DTS definitely does the job of migrating between SQL Server and any other DB but does it migrate data between Excel and Oracle? In any case using DTS does require a minimum knowledge of SQL Server whereas the application simplifies the whole approach. Although currently the application GUI does not support Oracle but the internal design does support it.

    Reply
  • DTS Wizard

    Posted by Legacy on 04/14/2003 12:00am

    Originally posted by: Michael Williamson

    Whats wrong with the DTS Wizard that ships with SQL Server 2000? It already supports importing from Microsoft Excel amoung other sources, and it should be faster since it uses BULK INSERTs.

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • The explosion in mobile devices and applications has generated a great deal of interest in APIs. Today's businesses are under increased pressure to make it easy to build apps, supply tools to help developers work more quickly, and deploy operational analytics so they can track users, developers, application performance, and more. Apigee Edge provides comprehensive API delivery tools and both operational and business-level analytics in an integrated platform. It is available as on-premise software or through …

  • The first phase of API management was about realizing the business value of APIs. This next wave of API management enables the hyper-connected enterprise to drive and scale their businesses as API models become more complex and sophisticated. Today, real world product launches begin with an API program and strategy in mind. This API-first approach to development will only continue to increase, driven by an increasingly interconnected web of devices, organizations, and people. To support this rapid growth, …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds