In previous article Overview of Data Provisioning in SAP HANA
, we explained various data provisioning technique in HANA.
In this article we will learn how to upload data from a flat file (.csv or excel) to SAP HANA using HANA Studio.
One of the advantages we have with SAP HANA studio is, we have an option to load data from either .csv or excel files to SAP HANA database directly without using any of the ETL tools.
In this article, we will see the steps involved in loading data from flat files (.csv or excel) to SAP HANA database using SAP HANA Studio. Below are the basic five steps involved during this activity.
Step 1: Create table
We need to have tables structure available in HANA before we start loading the data. This is an optional activity as we can ask system to create table and load data based on the fields available in the source file.
Let us create a table for this example. Copy the below script and execute in HANA Studio SQL Console.
SET SCHEMA <YOUR_SCHEMA>;
CREATE COLUMN TABLE LOAD_DATA_FROM_FILE_EXAMPLE (
ID INTEGER PRIMARY KEY,
Step 2: Download the file with data to be uploaded
The supported file types are for data upload are .csv, .xls, and .xlsx
Download the excel file from here
; and save in your local system.
Step 3: Import file using HANA Studio
- Go to File à Import. Select “Data from local file”.
- In the next screen, you can select the excel file and provide other details. This screen give us most of the options that we can define for loading data from local system to SAP HANA database.
Click on browse and select the file downloaded in above step. Select the options as shown in below image.
We can choose whether to load the data to an existing table in the system or create new table based on the fields available in source file.
In this case, select the table (LOAD_DATA_FROM_FILE_EXAMPLE) that we created in above step.
Step 4: Manage Data Mapping
Now, we need to perform the mapping between source and target columns. We have two different types of mapping available here, they are
One to One: This option will map column to column based on the sequence. This should be used if we know all the columns are in sequence.
Map by Name: This option will map the source and target columns based on the name. This should be used if we know that the columns names are same.
In this case select Map by Name.
Step 5: Load the Data
This is the final screen before we start the loading process. This screen show the data that already exist in the table and also shows the information about the schema and table to which we are going to load the data.
Click on Finish to load the data.
Congratulation! Data is uploaded to HANA table.
To check the data, right click on table and select Data Preview.
Few important facts about uploading data from Flat Files
- If the required table for loading the data does not exist in SAP HANA database, it’s necessary to create a table structure that is based on the flat files
- The application suggests the column names and data types for the new tables and it’s possible to edit them
- The new table always has a 1:1 mapping between the file and table columns
- When loading new data in the table, it gets appended to the existing data
- The application does not allow to overwrite any column or change the data type of existing data
- The supported file types are: .csv, .xls, and .xlsx