Data Transfer from Excel File to Database
Data Transfer from excel file to Database(HANA)
In this scenario we are transferring the data from excel file to HANA database.
Here I am demonstrating the scenario in a step by step manner.
I) Creating DataStore for HANA database.
- Logon to the SAP Business Object Data Designer.
- In Local Object Library click on Datastore Tab.
3. RIght click in the Local Object Library area & select “New“
Window for creating new datastore will open.
GIve all the details.
DataStore Name:- any name you want to give.
DataStore Type:- Database.
Database Type:- HANA
Database version:- available version of the database, here its “HANA 1.x”
Data source:- Give ODBC name, hers its “HANATEST”
User name & Password:-Details about User name & password.
4. Click “OK” & the Datastore will be created & can be seen in the Local Object Library.
II) Creating Excel Workbook File Format
We are creating Excel Workbook File Format for the following file.
- Creating Excel Workbook File Format.
- Select Format tab from Local Object Library, right click on “Excel Workbooks” & select “New“.
- “Import Excel Workbook” window appears
Give all required details ie. Directory & File name
Mark “Worksheet” option & select the appropriate sheet name.(here it is Article)
Range:– Select “All fields” to take all fields into consideration.
Mark “Use first row values as column names”
- Click on “Import Schema”
Excel file schema gets imported & can be seen in the window.
- Click “OK“.
Now the excel workbook will be created & can be seen in the Local Object Library.
III) Creating a job in Data Service Designer.
- Create a project.
- Create a Batch Job.
- Right click on the project & click on “New Batch job“.
- Give appropriate name to the job.
3. Add a dataflow into the job.
- Select the job, drag dataflow from palette into it & name it.
4. Build the scenario in the dataflow.
- Double click on the dataflow.
- Drag a excel workbook format created by us earlier into the dataflow.
- Drag a query into the dataflow & connect it to Excel file format.
5. Open the query & do Mapping.
- Select all the fields on LHS, right click & select “Map to Output“.
- Right click on the “DATAID” field & mark it as a Primary Key.
6. Inserting target table.
We can either import the already created table or we can use template table which afterwards will actually be created in the database.
We are using template table here.
- Drag a template table from palette into the dataflow.
- Give name to the table & select appropriate datastore.(here HANA_TEST)
- Click on “OK”.
- Template table can be seen in the dataflow.
- Connect Template table to Query.
7. Save the Job & Validate it.
- Click “Validate All” icon from the tool bar.
- Following message is displayed if no error found in the job.’
8. Execute the Job.
- Select the job, right click & press “Execute“.
9. Check the output in the table.
- Click on the Magnifying Glass icon of the table.