SapHanaTutorial.Com HOME     Learning-Materials Interview-Q&A Certifications Quiz Online-Courses Forum Jobs Trendz FAQs  
     Explore The World of Hana With Us     
About Us
Contact Us
 Apps
X
HANA App
>>>
Hadoop App
>>>
Tutorial App on SAP HANA
This app is an All-In-One package to provide everything to HANA Lovers.

It contains
1. Courses on SAP HANA - Basics, Modeling and Administration
2. Multiple Quizzes on Overview, Modelling, Architeture, and Administration
3. Most popular articles on SAP HANA
4. Series of Interview questions to brushup your HANA skills
Tutorial App on Hadoop
This app is an All-In-One package to provide everything to Hadoop Lovers.

It contains
1. Courses on Hadoop - Basics and Advanced
2. Multiple Quizzes on Basics, MapReduce and HDFS
3. Most popular articles on Hadoop
4. Series of Interview questions to brushup your skills
Apps
HANA App
Hadoop App
';
Search
Stay Connected
Search Topics
Topic Index
+
-
SAP HANA Administration
+
-
Data Provisioning

Loading data from flat file into SAP HANA

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.

SAPSAP HANA Flat File Upload

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,
PRODUCT_NAME VARCHAR(20),
COUNTRY VARCHAR(20)
);

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

  1. Go to File à Import. Select “Data from local file”.
SAPSAP HANA Flat File Upload
  1. 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.

SAPSAP HANA Flat File Upload

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.
SAPSAP HANA Flat File Upload

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.
SAPSAP HANA Flat File Upload

Congratulation! Data is uploaded to HANA table.

To check the data, right click on table and select Data Preview.
SAPSAP HANA Flat File Upload

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 



Support us by sharing this article.



Explore More
Close X
Close X

6 thoughts on “Loading data from flat file into SAP HANA

  1. Shekar says:

    Hi Iam following your tutorial to upload data through local file.

    I am getting below error.
    Message :
    Batch from record 2 to 39983 failed: SAP DBTech JDBC: [258]: insufficient privilege: Not authorized: SAP DBTech JDBC: [258]: insufficient privilege: Not authorized
    com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [258]: insufficient privilege: Not authorized
    at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.createException(SQLExceptionSapDB.java:357)
    at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.generateDatabaseException(SQLExceptionSapDB.java:197)
    at com.sap.db.jdbc.packet.ReplyPacket.buildExceptionChain(ReplyPacket.java:110)
    at com.sap.db.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:934)
    at com.sap.db.jdbc.CallableStatementSapDB.sendCommand(CallableStatementSapDB.java:1859)
    at com.sap.db.jdbc.StatementSapDB.sendSQL(StatementSapDB.java:1007)
    at com.sap.db.jdbc.CallableStatementSapDB.doParse(CallableStatementSapDB.java:255)
    at com.sap.db.jdbc.CallableStatementSapDB.constructor(CallableStatementSapDB.java:207)
    at com.sap.db.jdbc.CallableStatementSapDB.(CallableStatementSapDB.java:151)
    at com.sap.db.jdbc.CallableStatementSapDBFinalize.(CallableStatementSapDBFinalize.java:16)
    at com.sap.db.jdbc.ConnectionSapDB._prepareStatement(ConnectionSapDB.java:1601)
    at com.sap.db.jdbc.ConnectionSapDB.prepareStatement(ConnectionSapDB.java:188)
    at com.sap.db.jdbc.trace.Connection.prepareStatement(Connection.java:421)
    at com.sap.ndb.studio.jdbc.JDBCConnection.prepareStatement(JDBCConnection.java:565)
    at com.sap.ndb.studio.bi.filedataupload.deploy.populate.PopulateSQLTable.populateTable(PopulateSQLTable.java:45)
    at com.sap.ndb.studio.bi.filedataupload.ui.job.FileUploaderJob.uploadFlatFile(FileUploaderJob.java:201)
    at com.sap.ndb.studio.bi.filedataupload.ui.job.FileUploaderJob.run(FileUploaderJob.java:61)
    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:54)

    Could you please assist me to get rid of that error.

    • Admin says:

      Hi Shekar,
      Are you creating a new table or loading a data to existing table?
      In case of new table, check if you have authorization to create a table in that schema or not.
      In case you are loading data to an existing table, most probably you do not have insert privilege to that table.

      Regards,
      Admin

  2. Priyanka says:

    Hi ,
    By reading this article what I understand is this is one time activity. But what If I want to load the data on regular basis in the same table?
    Does I need to import the file everyday or is there any automation or schedule job that could be done ?

  3. Roshan Jha says:

    Importing date value from excel into HANA MDC table ?

    I am not able to import records of type date into table. I am not getting any error but there are no values being imported or shown when I execute select query on the table.

    I am attaching few screen shots on how I am doing it, if there is any formatting error related to date format then please let me know which is the correct or right way to do it.

    Excel file: No headings, only Date values in YYYY-MM-DD format (language – UK using format cell option)

    Note: Further I want to use time stamp data type in my table and import the same, what different do I need to do in this case.

  4. Admin says:

    Hi Roshan,
    Could you please send the screenshots to admin@saphanatutorial.com. Also send the sample excel and SQL Query to create table.

    Regards,
    Admin

Leave a Reply

Your email address will not be published. Required fields are marked *

Current day month ye@r *

 © 2017 : saphanatutorial.com, All rights reserved.  Privacy Policy