How to Import CSV File into SAP HANA Database

In this article, we are going to share you on how do we import CSV files into SAP HANA database. There are actually multiple ways of doing it and we are going to show you all the necessary steps for you to follow.

Method 1: Using Command Line

It is recommended to use this method when you are dealing with huge size of CSV files. We have tested it with more than 150GB size of CSV and it took us only 15 minutes. Before you execute the import command line, you have to ensure the table is exist in the HANA database. Below are the example command line that we used to import CSV file.

IMPORT FROM CSV FILE 'PATH-OF-YOUR-CSV-FILE' 
INTO SCHEMA-HANA-DB."TABLE-NAME"
WITH
RECORD DELIMITED BY '\n'
FIELD DELIMITED BY '|'
THREADS 10
SKIP FIRST 1 ROW
BATCH 1000
ERROR LOG 'PATH-OF-ERROR-LOG-FILE'
FAIL ON INVALID DATA

Syntax

Below are the options that can be define to control the import behavior. Not all the options should be use as some options might not be applicable to your import scenario.

THREADS : Specifies the number of threads that can be used for concurrent import. The default value is 1 and maximum allowed is 256.
BATCH : Specifies the number of records to be inserted in each commit.
TABLE LOCK : Provides faster data loading for column store tables.
NO TYPE CHECK : Specifies that the record are inserted without checking the type of each field.
SKIP FIRST : Skips the specified number of rows in the import file.
COLUMN LIST IN FIRST ROW : Indicates that the column list is stored in the first row of the CSV import file.
COLUMN LIST : Specifies the column list for the data being imported.
RECORD DELIMITED BY : Specifies the record delimiter used in the CSV file being imported.
FIELD DELIMITED BY : Specifies the field delimiter of the CSV file.
OPTIONALLY ENCLOSED BY : Specifies the optional enclosure character used to delimit field data.
DATE FORMAT : Specifies the format that date strings are encoded with in the import data.
TIME FORMAT : Specifies the format that time strings are encoded with in the import data.
TIMESTAMP FORMAT : Specifies the format that timestamp strings are encoded with in the import data.
ERROR LOG : Specifies that a log file of errors generated is stored in this file. Ensure the file path you use is writable by the database.
FAIL ON INVALID DATA : Specifies that the IMPORT FROM command fails unless all the entries import successfully.

Method 2: Using HANA Studio Import

You can refer to this article, How to Export & Import HANA Table. Look for the import section.

Method 3: Using HANA Content Import

This method is is advisable to use when deals with large size of CSV files. The method involved with wizard from HANA studio.

From the top menu, click File > Import.

Select Data from Local File, under SAP HANA Content.

Select the target database.

At this stage, you need to define the following items,

Select File – Define the source file.
File Encoding – Use the default encoding.
Field Delimiter – Define the source file delimiter.
Header row exists – Check this if there is header exists and set number of header row.
Import all data – Check this to import the data and define the start line.
Target Table – Choose New if it is not exist in the database (define the schema and table name). Choose Existing if it is already exist in the database system.