To Add External Data to the Virtual Database (Excel Spreadsheet)

Before You Begin

Make sure you have the two data sources, a spreadsheet and a delimited text file. These will be merged into one table. For this step add the spreadsheet to the table, and then fine-tune some of its fields.

1. From the NetBeans IDE tool bar, select Tools->Virtual Database->Add External Tables.

2. In the Choose Virtual Database window, select the appropriate database from the list of available databases.

   For this exercise, select VirtualMashupDB, which is the database you just created.

3. In the File field on the Choose Data source window, navigate to the directory that contains the spreadsheet and double-click the file.

   For this exercise, select company-data.xls. The file now appears in the Selected Table Source list. You can remove it by highlighting the file name in the Selected Table Source and clicking Remove.

   ---

   Note –

   Although you are selecting a file that is located in a local directory, you can also select data located on the web by entering the URL.

   ---

4. Click Next.

5. In the Enter Table Details window, confirm that the table name, encoding, table type, and resource URL are correct.

   The resource URL is the directory path and file name of the spreadsheet you selected.

6. Click Next.

7. In the Choose a Sheet window, select the sheet containing the data to use, and then click Preview to view the information.

   In the example data, the information is in Sheet1.

8. Click Next.

   Step 5, Choose a (HTML) Table is skipped since no HTML sources were selected. The Import Table MetaData window appears.

9. In the Import Table MetaData window, define how to parse the file.

   If the data in the preview does not contain column names, and in the example it does not, deselect the First line Contains Field Names? check box. This is also the case for .csv files, and you must deselect this check box prior to setting the FIELD names.

   ---

   Note –

   If a data file does not exist, you can select to create one. The available fields on this window vary based on the type of data you are importing into the database.

   ---

10. Click Next.

11. In the Enter Column Properties window, double-click on a column field to edit it.

    The # symbol represents the numerical order of the columns, and in the example FIELD_1 is the name of the first column. For this example, change the column names to names that more appropriately represent the values. This makes it easier to know what data is in which column when you merge the spreadsheet and the text file fields together. Use the following column names:

    FIELD_1

    PRODUCT_IDENTIFIER

    FIELD_2

    PRODUCT

    FIELD_3

    VENDOR

    FIELD_4

    VENDOR_CODE

12. To populate the Preview Table Content columns, click Back once, and then click Next.

13. When you are satisfied that the table represented in the Preview Table Content in the lower portion of the Enter Column Properties window is correct, click Finish.

    A message confirms that you created the table successfully. You are now ready to repeat the process and add the text file to the database.