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:
Defining the general properties
In the file metadata setup wizard, fill in the Name field, which is mandatory, and the Purpose and Description fields if needed. The information you provide in the Description field will appear as a tooltip when you move your mouse pointer over the file connection.
If needed, set the version and status in the Version and Status fields respectively. You can also manage the version and status of a repository item in the [Project Settings] dialog box. For more information, see Version management and Status management respectively.
If needed, click the Select button next to the Path field to select a folder under the File Excel node to hold your newly created file connection.
Click Next to proceed with file settings.
Loading the file
Click the Browse... button to browse to the file and fill out the File field.
Skip this step if you are saving an Excel file connection defined in a component because the file path is already filled in the File field.
If the uploaded file is an Excel 2007 file, make sure that the Read excel2007 file format(xlsx) check box is selected.
By default, user mode is selected. If the uploaded xlsx file is extremely large, select Less memory consumed for large excel(Event mode) from the Generation mode list to prevent out-of-memory errors.
In the File viewer and sheets setting area, view the file content and the select the sheet or sheets of interest.
From the Please select sheet drop-down list, select the sheet you want to view. The preview table displays the content of the selected sheet.
By default the file preview table displays the first sheet of the file.
From the Set sheets parameters list, select the check box next to the sheet or sheets you want to upload.
If you select more than one sheet, the result schema will be the combination of the structures of all the selected sheets.
Click Next to continue.
Parsing the file
In this step of the wizard, you can define the various settings of your file so that the file schema can be properly retrieved.
Specify the encoding, advanced separator for numbers, and the rows that should be skipped as they are header or footer, according to your Excel file.
If needed, fill the First column and Last column fields with integers to set precisely the columns to be read in the file. For example, if you want to skip the first column as it may not contain proper data to be processed, fill the First column field with 2 to set the second column of the file as the first column of the schema.
To retrieve the schema of an Excel file you do not need to parse all the rows of the file, especially when you have uploaded a large file. To limit the number of rows to parse, select the Limit check box in the Limit Of Rows area and set or select the desired number of rows.
If your Excel file has a header row, select the Set heading row as column names check box to take into account the heading names. Click Refresh to view the result of all the previous changes in the preview table.
Then click Next to continue.
Finalizing the end schema
The last step of the wizard shows the end schema generated and allows you to customize the schema according to your needs.
Note that any character which could be misinterpreted by the program is replaced by neutral characters. For example, asterisks are replaced with underscores.
If needed, rename the schema (by default, metadata) and leave a comment.
Customize the schema if needed: add, remove or move schema columns, export the schema to an XML file, or replace the schema by importing an schema definition XML file using the tool bar.
If the Excel file which the schema is based on has been changed, click the Guess button to generate the schema again. Note that if you have customized the schema, the Guess feature does not retain these changes.
Click Finish. The new schema is displayed under the relevant File Excel connection node in the Repository tree view.
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 How to use centralized metadata in a Joband How to set a repository schema.
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.