Insert data into custom tables – Flat file loads to SAP HANA

Welcome to the next tutorial on SAP HANA. In this tutorial, we again start a three part series where we understand the different ways of loading data into tables created in SAP HANA. In our previous tutorials, we created a custom table using three methods – Graphically, SQL script and HDB Table method. I refer to them as custom tables here as they were created in HANA and not brought in to HANA from any external database. Now that we have these tables, we have a greater question at hand – How do we load data to it? This tutorial explains how to load a flat file into SAP HANA without any coding whatsoever.

Let’s create our flat file first – A CSV file

First step would be to create a flat file – A comma separated value file (CSV).

Easiest way to do this is with MS Excel as we enter some data as shown below.

SAP HANA FLAT FILE UPLOAD CSV UPLOAD TO SAP HANA

We proceed and save this file and make sure to choose the file type as CSV(Comma Delimited)

SAP HANA FLAT FILE UPLOAD CSV UPLOAD TO SAP HANA

AS seen below, I have given it a file name and a file type

SAP HANA FLAT FILE UPLOAD CSV UPLOAD TO SAP HANA

You would get the below message. Press Ok.

SAP HANA FLAT FILE UPLOAD CSV UPLOAD TO SAP HANA

Then the below pops up. Press Yes.

SAP HANA FLAT FILE UPLOAD CSV UPLOAD TO SAP HANA

Exit MS Excel. When trying to do so, a pop-up will ask you if you want to save this file. Press “Don’t Save” in this case. Trust me – Your file is already saved. If you save at this point, excel sometimes corrupts the CSV format.

SAP HANA FLAT FILE UPLOAD CSV UPLOAD TO SAP HANA

Now after you are done with this, let me explain why this file type is called CSV or Comma separated value.

Checking the CSV file delimiter

To understand this, go to your file and open it in notepad. It is quite easy to accomplish. Right click on the file you created and then select “Open With” from the context menu.

SAP HANA FLAT FILE UPLOAD CSV UPLOAD TO SAP HANA

Choose program as Notepad and press Ok.

SAP HANA FLAT FILE UPLOAD CSV UPLOAD TO SAP HANA

You would be able to see the file open in notepad now. As you see, each column of data is separated by commas and hence the name CSV is appropriate. Please always check the file in notepad as some of the newer versions of Excel save CSVs that are separated by semicolons (;) instead by default. It is not a problem, but you should be aware of what the separator is.

SAP HANA FLAT FILE UPLOAD CSV UPLOAD TO SAP HANA

Now, make sure you close this file before proceeding further. Open files can cause errors in upload. Once closed, Open your HANA studio.

Uploading this CSV flat file to SAP HANA

To start the flat file import, go to File -> Import as shown below.

SAP HANA FLAT FILE UPLOAD CSV UPLOAD TO SAP HANA

Select “Data from Local file” from the SAP HANA Content folder and click next.

SAP HANA FLAT FILE UPLOAD CSV UPLOAD TO SAP HANA

Select the target HANA system. In our case, the name is HDB but you might have multiple HANA systems added in your HANA studio and in that case, you would choose the system where you are importing this data to. Press next when the system has been selected.

SAP HANA FLAT FILE UPLOAD CSV UPLOAD TO SAP HANA

This will open up the below pop-up. Firstly, click on browse to select the file you wish to upload.

SAP HANA FLAT FILE UPLOAD CSV UPLOAD TO SAP HANA



1

1

Once this is done, many new options become active. I have numbered them to be explain in a better way.

Mark 1 is the delimiter used in our CSV file. As we saw that in our case this was a comma but if it was something else like a semi colon for you, drop down and choose it.

Mark 2 needs to be checked if there is a header row in our data. Header rows are the rows containing names of the columns. These should not be considered as table data and to have them ignored, this check box needs to be checked.

Mark 3 needs to be checked if you want all the data from this file to be imported. If you only want partial data to be imported, uncheck this and provide the start line and end line numbers between which the data will be imported from.

Mark 4 indicates the target table section. Here you tell HANA whether you want this data imported to a new table or an existing table. In our case, let’s import this data to our table created using the graphical method – CUST_REV_GRAPH. To do this, click on the select table button.

SAP HANA FLAT FILE UPLOAD CSV UPLOAD TO SAP HANA

Find the table under your schema and press OK.

SAP HANA FLAT FILE UPLOAD CSV UPLOAD TO SAP HANA

The overall settings should now look like the below.  Press Next.

SAP HANA FLAT FILE UPLOAD CSV UPLOAD TO SAP HANA

This takes you to the mapping window where you tell HANA as to what field from the CSV file goes where in the corresponding HANA table.

SAP HANA FLAT FILE UPLOAD CSV UPLOAD TO SAP HANA

Drag and drop the source field to its corresponding target field to get the mappings as below.

SAP HANA FLAT FILE UPLOAD CSV UPLOAD TO SAP HANA

This screen below now shows the data preview of this import that we are trying to make. Just to be sure that you have not mapped the wrong source field to the target.

SAP HANA FLAT FILE UPLOAD CSV UPLOAD TO SAP HANA

The log is not read and hence the import is successful and now this data should have loaded to the table.

SAP HANA FLAT FILE UPLOAD CSV UPLOAD TO SAP HANA

Right click on the table and click “Open Data preview” to check the data.

SAP HANA FLAT FILE UPLOAD CSV UPLOAD TO SAP HANA

Below data confirms that we have correctly imported this table.

SAP HANA FLAT FILE UPLOAD CSV UPLOAD TO SAP HANA



There are two other ways to fill in data to tables but since they include some coding, I will be posting them in the SQL section. These methods are:

  1. Adding data to a SAP HANA Table using HANA SQL script INSERT statement.
  2. Linking a CSV file to a HDBTable using HDBTI Configuration file (Recommended method)

This method and the SQL INSERT method are usually used for quick analysis but anything that you do for a client that involves customized tables should be done on HDB tables and with HDBTI config files explained in the above linked tutorial. Be sure to check them out too.

Help this website grow by sharing this document on social media by using the icons below.

Happy Learning!

 

<<Previous Tutorial                                                                                                              Next Tutorial>>

Content Protection by DMCA.com
Tagged , , , , , , , , , , , , .

7 Comments

  1. Hello,

    I am not able to load Date (DOB) column in to HANA DB

    Cust_Id,F_Name,L_Name,Sex,Revenue,DOB
    1,Ram,Kumar,M,20,2016-02-16
    2,Shyam,Kumar,M,21,2016-02-17
    3,Rani,Mishra,F,35,2016-02-18
    4,Soha,Khan,F,23,2016-02-19

    Please guide….

    • Hi,
      Its usually a case of datatype mismatch.
      The simplest way to fix this is to keep the date in SAP format – YYYYMMDD.
      This means that you keep the date as 20160216 for your first row of data.

      Let me know,

      Shyam

  2. Hi Shyam,

    I am trying to load a flat file “fctcustomerorder” . It has the fields like below.
    Orderid, productid, customerid, employeeid,promotionid, territoryid..
    0,1,1,1, , ,

    Promotionid and territoryid has blank values. I need to fill the fctcustomerorder table with spaces, but i get an error while loading it. How can i load spaces to the fields in the table?

    Regards,
    FM.

    • Ok.
      1. Are you trying to load blanks into a field which is marked as a key field?
      If yes, keys can not be blank. That would be your issue.
      2. If not, please send me the excel you are trying to upload (assuming it’s not confidential) to shyam.uthaman@teachmehana.com.
      Also, open your table definition, right click somewhere in between the definition and click export sql which would give you the sql to create this table.
      Copy and send that to me too.
      So I would create and try to load this file and replicate the error.
      It’s hard to fix issues without looking at them.. we’ll see what we can do.
      Cheers.

  3. HI Shyam,

    when i am trying to open this create-sap-hana-table-graphical-method and it is throwing some webpage error

Leave a Reply