Skip to Content

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.


     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.

     8)      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.

To report this post you need to login first.


You must be Logged on to comment or reply to a post.

  1. Former Member

    Thank you so much for such a easy to follow instruction on this.Appreciate it much.


    Question: After loading excel into BODS like this how do we create an XML schema from it and output it as a web service call?


    Thank you

  2. Former Member


    My excel workbook has only one worksheet. The worksheet name may change, hence i want to specify the worksheet number rather than the name.i.e. to pick worksheet number 1 only.

    There is a check box for number, not sure of its purpose but it does not serve the purpose stated above.

  3. Former Member



    I tried the above process and the following error occurred please have a look and help me out…/wp-content/uploads/2014/12/e2_604507.png


    Details are: Data services 4.2 SP 1 and target is Sybase.

    I even tried uploading the excel file in the location where Job server is but again the error persists as below: /wp-content/uploads/2014/12/e1_604506.png


    I have a doubt about the version of Ms Office, which is 32 bit in my local and do i need to use 64 bit version on  my server to upload this kind of excel files ???


    Much appreciate if anyone comes up with immediate solution.




Leave a Reply