How to: Generate Data from an Excel Spreadsheet
To make your database unit tests more meaningful, you can fill a test database with test data that is appropriate for the schema of the database but unrelated to the production data. By using the data bound generator in Visual Studio Team System Database Edition, you can fill columns in a table with data from a Microsoft Excel workbook. For more information about how to generate test data, see Generate Test Data for Databases by Using Data Generators.
In this procedure, you will perform the following actions:
Assign the data bound generator to a column in a table in your database project.
Create a connection to your workbook by using the OLE DB provider.
Specify which data from the workbook will fill the column in your table.
Prerequisites
This procedure assumes that you have an open database project and that you have created a data generation plan for it. These steps also assume that you have an Excel workbook from which you want to generate data. For more information about how to create a data generation plan, see How to: Create Data Generation Plans.
To assign the data bound generator to a column
Double-click the data generation plan in Solution Explorer if the plan is not already open.
The data generation plan appears.
Click the table for which you want to generate data.
In the column-details pane of the data generation plan window, find the row for the table column that you want to fill with data from the workbook. In that row, click the cell in the Generator column, click the down arrow, and then click Data Bound Generator.
Next you specify the information to connect to your workbook.
To create a connection to your workbook using the OLE DB provider
In the Properties window, locate the Connection Information property, click the down arrow, and click Add New Connection.
The Choose Data Source dialog box appears.
In the Data Source list, click <other>.
In the Data Provider list, click .NET Framework Data Provider for OLE DB, and then click Continue.
The Connection Properties dialog box appears.
In the OLE DB Provider list, click Microsoft Jet 4.0 OLE DB Provider, and then click Advanced.
The Advanced Properties dialog box appears.
In the Extended Properties property, type Excel 8.0 if you are using Microsoft Office 2003 System or Excel 12.0 if you are using 2007 Microsoft Office system, and then click OK.
In the Server or file name box, type the path and name of your workbook (for example, C:\Temp\TestData.xls).
Click Test Connection.
If the connection is successful, a message box appears with the message: Test connection succeeded. If an error indicates that you cannot connect, make sure that the workbook is not being used.
Click OK.
In the final step, you select data from the workbook by using the new connection, and you examine the results that appear in the Data Generation Preview window.
To select data from the workbook and verify your results
In the Properties window, type your SELECT statement in the Select Query property for the column that you want to fill. For example, to retrieve all data from a worksheet that is named AnnualSales, type the following:
SELECT * FROM [AnnualSales$]Note
You must enclose the worksheet name in brackets and specify the trailing $.
In the column-details pane of the data generation plan window, find the row for the table column that you want to fill with data from the workbook. In that row, click the cell in the Generator Output column, and click the down arrow. Then click the name of the worksheet that contains the data that you want to use.
Open the Data menu, point to Data Generator, and click Preview Data Generation.
The Data Generation Preview window appears. The column that you chose to fill from the spreadsheet displays values from your workbook.