To Add External Data to the Virtual Database (Delimited Text File)

Before You Begin

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

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

2. In the Add External Tables window, select the appropriate database from the list of available databases.

   In this exercise, VirtualMashupDB is the database you created.

3. Click Next.

4. In the File field on the Choose Data Source window, navigate to the directory that contains the delimited text file and then double-click the file name.

   The file now appears in the Selected Table Source list. You can remove the file 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 on the web by entering the URL.

   ---

5. Click Next.

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

   For a delimited text file, which you are using in this example, the table type should be Delimited Flatfile. The resource URL is the directory and file name of the delimited text file you selected.

7. Click Next.

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

   ---

   Note –

   The available fields on this window vary based on the type of data you are importing into the database.

   ---

   • In the example the pipe represents the delimiter, so change the field delimiter from a comma (,) to a pipe (|).

   • If your delimited data does not contain column names, as in our sample file, deselect the First Line Contains Field Names? check box.

   • Review the text file in the Preview panel to ensure the selections are correct.

9. Click Next.

10. In the Add External Tables/Enter Column Properties window, edit the Column Definitions if desired.

    In this exercise, if there are columns that are similar between the two data files, the names you use for the related columns in the delimited data file must match the names you used in the spreadsheet. Change the column names to names that more appropriately represent the column values. For this exercise, use the following values:

    FIELD_1

    VENDOR_CODE

    FIELD_2

    VENDOR

    FIELD_3

    VENDOR_ADDRESS

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

12. Click Finish when ready.

    A message confirms that you created the table successfully. You are now ready to verify that your Data Mashup database has the correct tables.