SAP HANA Flat File Upload

In this post, I would like to explain data upload to SAP HANA. SAP HANA support uploading data from a file. SAP HANA Support the following type of files,

.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 the table is not present then table need to be created.

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

Steps to be followed for upload data from flat file to SAP HANA –

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

Create table in SAP HANA

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

Use “NEW” option for creating a new table.

Create file with data in our local System
To upload master data, create a .csv file or .xls file on local system as below,

Name Currency CoCode Address Country Sorg
ABC Inc USD 1000 NJ USA 1000
XYZ Inc USD 2000 PA USA 2000

Select the file

Step 1) Open modeler perspective – ‘Main Menu’ – ‘Help’ – ‘Quick View’

Open A Quick View

Select ‘Import’ option from Quick View. A pop-up for import option will be 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 selection of file.

Select ComCode.xls file.

Click on “Open” button.

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

Available options can be categorized into three main areas,

Source File Section

File Details Section

Target Table

Step 2) In this step, enter following details –

Select File – Selected file path will be displayed.

Header Row Exits – If ComCode.xls file has a header (column Name), select it.

Import All Data – Select this option, to import all data from a file, else mention Start Line and End line for specific data load from file.

Ignore leading and trailing white space – Select this option for ignoring leading and trailing white space in the file.

Target Table – In this section, there are two options –

New – If a table is not present in SAP HANA, then choose this option, and provide exiting schema name and table name to be created.

Exiting – If a 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 a Mappings:

A mapping screen use for performing the mapping between source and target columns.

There are two different types of mapping available. When click on SAP HANA Flat File Upload Tutorial: CSV, XLS & XLSX, we get two options as follows –

One to One: By using this option, we can map column to column based on the sequence. This option can be used if 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 the columns name are same.

Mapping of Source to Target – Map here Source File column to Target Table 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 should select 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.

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 ComCode file.

Click on “Next” file.

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

Load the data: It is final screen before 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.

Once verify displayed data in Data from file section, click ‘Finish’ to Start loading the data to the table.

After successful import option completing, check the job log view status ‘Completed Successfully.’

Once the data importing job is successful, check the data in table by selecting the table of respective schema and right click on Table – ‘Data Preview’. Data from table will display on Data preview screen.

This is the easiest way of loading the data from local flat file into SAP HANA.

Hope this article will help for uploading the data to SAP HAHA.