Using Excel as data source for Transformation at Business Objects Data Services

[Former Member]

Using Excel as data source for Transformation at Business Objects Data Services

Business Requirement: - Making use of the Excel file as source for data transformation using Business Objects Data Services and saving the transformed data into one of the Temporary Tables configured as a Data store at Data Services.

Steps:-

     1)      Create a project say Prj_Test at Business Objects Data Services.

     2)      Create a Data Store by making a connection to a database sat SQL SERVER 2008 in this case.

     3)      Now open an excel file and save the required data at Excel file in the local drive.

     4)      Go to Business Objects Data Services and click on the Formats option tab available at the Local Object Library.

     5)      At the Formats option go to the Excel Workbooks option and do a right click and select New option.

     6)      A new Import Excel Workbook window opens up.

     7)      At this new window go to the Format Tab and provide the below details

i) Format name:- Give the customize name of the Excel file which you want to use as the data source.

ii) Directory:- Select the folder and the path where the source Excel is been saved.

iii) File name: - Select the actual Excel file from the Directory.

iv) Access method:-Click on the Worksheet radio button and select All fields range option .

v) Code Page: - Select default option.

vi) Use first row values as column names:-check on the check box (This will display the actually column data instead of default field representation like f1, f2………).

vii) Finally click on the Import schema button which will import the data schema to your file and click OK.

     😎      Now create a Work flow say WF under Prj_Test.

     9)      Create a Data flow sat DF under WF.

     10)   At DF drag the Excel file from the format option under Excel Workbook to the Data Services Designer and name it as source.

     11)   Connect this Excel source to a Query Transform and perform any data transformation as per the business requirement at this Query Transform by dragging the required input fields to the output columns side.

     12)   Finally take a Template Table transform from the Data store configured at step 2.

     13)   Connect the Query Transform out put to this Template Table.

     14)   Finally the resultant data saved at the Template Table would be the Transformed data from the Excel Source file.