SAP HANA Flat File Upload Tutorial: CSV, XLS & XLSX

SAP HANA support uploading data from a file without ETL tools (SLT, BODS, and DXC). It is a new feature of HANA Studio Revision 28 (SPS04).

SAP HANA Support following type of files which will be available on client system as below –

.CSV (Comma Separated value files)
.XLS
.XLSX

Prior to this option, the user needs to create control file (.CTL file). To upload data in SAP HANA, table need to exist in SAP HANA. If table exits, records will be appended at the end of the table, if table not present then table need to be created.

This application suggests column name and data type for the new tables.

Steps for upload data from flat file to SAP HANA is as below-

Create the table in SAP HANA
Create file with data in our local system
Select the file
Manage the mappings
Load the data

Create the table in SAP HANA

If table are not present in SAP HANA, then we can create a table by SQL Script or by this process by selecting “NEW” option.

We will use “NEW” option for creating a new table.

Create file with data in our local System

We are going to upload Sales Organization master data. So create a .csv file and .xls file for it on local system.

We are going to upload SalesOrg.xlsx file into SAP HANA, so we have created a file SalesOrg.xlsx in the local system.

SalesOrg	Name	Currency	CoCode	Address	Country	Ref_Sorg
1000	ABC Pvt. Ltd.	USD	1000	NEW YORK	USA	1000
2000	ABC Enterprises	INR	2000	INDIA	INDIA	2000

Select the file

Step 1) Open modeler perspective ? ‘Main Menu’ ? ‘Help’ ? ‘Quick View’ as shown below.

A Quick View screen appears as below-

Select ‘Import’ option from Quick View. A pop-up for import option will be displayed.

A Pop-Up for import screen is displayed. Go to SAP HANA Content? ‘Data from Local File’.

Click Next.

A pop-up for File selection will be displayed, follow below steps for the select file.

Select SalesOrg.xls file.
Click on “Open” button.

A screen for file selection for import will be displayed ,in which we can select a file for loading data from local system to SAP HANA database.

Available options can be categorized into three main areas, they are

Source File Section
File Details Section
Target Table

Step 2) In this step we have to enter following detail –

Select File – Selected file path will be displayed here.
Header Row Exits – If SalesOrg.xls file has a header (column Name). So, we have ticked it.
Import All Data – Tick this option if you want to import all data from a file, otherwise mention Start Line and End line for specific data load from file.
Ignore leading and trailing white space Tick this option for ignoring leading and trailing white space in the file.
Target Table – In this section two option –New – If the table is not present in SAP HANA, then choose this option, and provide Exiting schema name and table name to be created.
Exiting – If the table exist in SAP HANA, then choose this option. Select Schema name and Table. Data will be appended to end of the table.
Click on “Next” button

Manage the Mappings

A mapping screen will be used for performing the mapping between source and target columns.

There are two different types of mapping available.When we click on we get two option as below –

One to One: By using this option, we can map column to column based on the sequence. This option can be used if we know all the columns are in sequence.
Map by Name: By using this option, we can map the source and target columns based on the name. This can be used if we know that the columns names are same.

Mapping of Source to Target – We will map here Source File column to Target Table, and also, we can change target table definition.

Proposed Table structure from Source File- Table column Name is supposed from Excel file Column Name (Header).
Target Table Structure: Target Table store type is selected as column store by default.
Click File name and drag to target field, File will be mapped. The field can be mapped automatically by one to one or map By Name option. we can manually do the mapping using drag and drop option If our column name could not be mapped with the above options.
In the File data section of the same screen, we can also see how the data looks in the source file for all the columns.File data section displays data of SalesOrg file.
Click on “Next” file.

A window for import data from the local file will appear.

Load the data

It is final screen before we start the loading process. By this screen data that already exist in the table will display and also the information about the schema and table to which we are going to load the data will display.

Detail Sections: In this section Selected Source File name, Target Table Name, and Target Schema Name detail will be displayed.
Data from File: It will display data extracted from the file.
If displayed data in Data from file section has been verified, click ‘Finish’ to Start loading the data to the table.

After successful import option completing, we should see the entry in the job log view with status ‘Completed Successfully.’

Once the data importing job is successful,
1. We can see the data in table of by selecting the table of respective schema and right click on Table ? ‘Data Preview’ as shown below.
2. Data of table will display in Data preview screen as below-