This documentation is archived and is not being maintained.

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 Premium, you can fill columns in a table with data from a Microsoft Excel workbook. For more information about how to generate test data, see Generating 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

  1. Double-click the data generation plan in Solution Explorer if the plan is not already open.

    The data generation plan appears.

  2. Click the table for which you want to generate data.

  3. 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

  1. 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.

  2. In the Data Source list, click <other>.

  3. In the Data Provider list, click .NET Framework Data Provider for OLE DB, and then click Continue.

    The Connection Properties dialog box appears.

  4. In the OLE DB Provider list, click Microsoft Jet 4.0 OLE DB Provider, and then click Advanced.

    The Advanced Properties dialog box appears.

  5. 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.

    NoteNote

    If an error appears when you try to connect to a spreadsheet that you created by using Microsoft Excel 2010, you can save the spreadsheet as an Excel 97-2003 Workbook.

  6. In the Server or file name box, type the path and name of your workbook (for example, C:\Temp\TestData.xls).

  7. 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.

  8. Click OK.

    NoteNote

    If an error appears when you try to add the connection from the Properties window, you can instead add the connection by using Server Explorer. For more information, see How to: Connect to a Database from Server Explorer.

    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

  1. 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$]
    
    NoteNote

    You must enclose the worksheet name in brackets and specify the trailing $.

  2. 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.

    If you did not specify a query in Step 1 or if your query specified a worksheet name that was not found, the Generator Output column will contain only <No Coercible Output>.

  3. 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.

Show: