Centralizing File Excel metadata

If you often need to read data from and/or write data to a certain Excel spreadsheet file, you may want to centralize the connection to the file, along with its data structure, in the Repository for easy reuse. This will save you much effort because you will not have to define the metadata details manually in the relevant components each time you use the file.

You can centralize an Excel file connection either from an existing Excel file, or from Excel file property settings defined in a Job.

To centralize a File Excel connection and its schema from an Excel file, expand Metadata in the Repository tree view, right-click File Excel and select Create file Excel from the contextual menu to open the file metadata setup wizard.

To centralize a file connection and its schema you have already defined in a Job, click the icon in the Basic settings view of the relevant component, with its Property Type set to Built-in, to open the file metadata setup wizard.

Then complete these tasks step by step following the wizard:

• Define the general information that will identify the file connection. See Defining the general properties of the File Excel connection.

• Load the file of interest. See Loading the file.

• Parse the file to retrieve the file schema. See Parsing the file.

• Finalize the file schema. See Finalizing the end schema of your Excel file.

Now you can drag and drop the file connection or the schema of it from the Repository tree view onto the design workspace as a new component or onto an existing component to reuse the metadata. For further information about how to use the centralized metadata in a Job, see Using centralized metadata in a Job and Setting a repository schema in a Job.

To modify an existing file connection, right-click it from the Repository tree view, and select Edit file Excel to open the file metadata setup wizard.

To add a new schema to an existing file connection, right-click the connection from the Repository tree view and select Retrieve Schema from the contextual menu.

To edit an existing file schema, right-click the schema from the Repository tree view and select Edit Schema from the contextual menu.