CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
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:
- Migrate data from an Excel worksheet to SQL Server
- 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.
- Migrate data from SQL Server to Excel worksheet
- 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.
- 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.
| Note:
|
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.
