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

By : Ryan Bennett Ryan Bennett
Hours Updated

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.

How to Upload Data from Flat File to SAP HANA

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.

Creating a Table in SAP HANA

A Quick View screen appears as below-

Creating a Table in SAP HANA

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

Creating a Table in SAP HANA

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

Creating a Table in SAP HANA

Click Next.

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

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

Creating a Table in SAP HANA

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 –

  1. Select File – Selected file path will be displayed here.
  2. Header Row Exits – If SalesOrg.xls file has a header (column Name). So, we have ticked it.
  3. 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.
  4. Ignore leading and trailing white space Tick this option for ignoring leading and trailing white space in the file.
  5. 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.
  6. 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.
  7. Click on “Next” button

Creating a Table in SAP HANA

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 Creating a Table in SAP HANA 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.

  1. Proposed Table structure from Source File- Table column Name is supposed from Excel file Column Name (Header).
  2. Target Table Structure: Target Table store type is selected as column store by default.
  3. 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. Manage the Mappings
  4. 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.
  5. Click on “Next” file.

Manage the Mappings

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.

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

Load the data

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

Load the data

  • 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-

    Load the data

You Might Like: