XSImport.jpg

During a recent small migration project from a HCP MaxDB DB to a HCP HANA DB I needed to import multiple CSV files, and go through a series of dry runs during the process. Since I was going to be doing the loads multiple times, I decided it would be helpful to write a small front end to make the processes of uploading failrly large CSV files into HANA simpler.

Over the past few months I have heard the question: “Whats the best way to get data into HANA from CSV?” While there are a few options if you have a HANA appliance and system level access to the system, there are not too many options with a HCP instance. After trying/testing the CSV import from Studio (size limit), a hdbti file (not great for multiple/repetitive options/file size challenges) it does not leave you with too many choices. After doing some digging it seems quite a few people have uploaded data through a server side script (XSJS) successfully and using the BATCH option while processing is pretty fast.

So I decided to put a front end together called XSImport – it provides a quick and easy way to load CSV files into HANA using a native XS application. You can find the project on Github here. It has a couple of helpful features like providing a preview of the data set, ability to delete all files already in the table, and a overview of the target table data types and dimensions. Some helpful features if you are going through a similar migration.

Below is a screenshot of the app, if anyone has any suggestions for improvement or would like to contribute to the project, please feel free.

A couple of you might point out and ask why I didn’t upload the file directly to the HANA instance? Unfortunately if you are using a HCP HANA instance you don’t have access to the file system. If you do, this method from Thomas is still a great option: Importing CSV files into SAP HANA – In-Memory Business Data Management – SCN Wiki

Features:

  • Preview data before uploading
  • Specify a CSV preview row
  • Overwrite option
  • Displays target table info (column names, types, sizes)
  • Uses BATCH for faster imports
  • Can be used for HCP HANA or dedicated HANA instances

Limitations:

  • Since files are uploaded via the browser, any browser size POST limitations come into effect
  • Network speed to HCP or HANA will effect the file upload speed

To report this post you need to login first.

20 Comments

You must be Logged on to comment or reply to a post.

  1. Jeremy Ma

    This is useful.  Thanks Paul.

    I did discover a bug here where if the number of records of file less than number of columns it errors. 

    You can correct this easily by going to the project xsjs/fileupload.xsjs Line 104

    //b4 var col = line.splice(0, arrLines.length + 1);

            var col = line.splice(0, colCount);  //after

    Cheers

    (0) 
  2. Deep PIYUSH DESAI

    Hi Paul,

    I wanted to know where did you develop this tool.

    I am trying to import your project on HANA studio but i am failing to do so.

    If you can help me in editing this project, will be helpful.

    Thanks,
    Deep Desai

    (0) 
      1. Deep PIYUSH DESAI

        I tried importing the Github project into Hana studio but it says no project found.

        Is there any specific way to import this into Hana studio?

        I am kind of new to Hana and not sure if i am doing it right

        (0) 
        1. Benedict Venmani Felix

          hi Deep,

          You can create a new XS project in eclipse(studio). Note down the location of the project in your system folders. Now copy the extracted Github folders into the project location.

          Note: when you create the XS project,uncheck the box for creating XSACCESS and XSAPP if it already exists in the extracted project.

          Benedict

          (0) 
  3. Wolfgang Röckelein

    Just as a notice for other facing this problem

    When you get on the first POST to XSImport/xsjs/fileupload.xsjs?process=init request a 403 with a body content of “Request execution failed due to missing or invalid XSRF token” reply, which makes line 72 in app.js fail since this is not JSON you have to make sure that .xsaccess contains the line

    “prevent_xsrf” : false

    (0) 
  4. Wolfgang Röckelein

    Unfortunatly this does not seem to work in a trial account.

    Has anybody solved this already?

    (0) 
      1. Wolfgang Röckelein

        Schema name and Table name dropdowns are empty. Apparently the search for changable objects runs into authorization problem. With trial all created objects are in _SYS_BIC.

        (0) 
        1. Benedict Venmani Felix

          Hi Wolfgang,

          Use this SELECT SCHEMA_NAME FROM “SYS”.”SCHEMAS” WHERE HAS_PRIVILEGES = ‘TRUE’, and see if you have your NEO_ schema.

          Also, in the trial system the drop-down for schema takes forever to load. Initially I thought this was a security issue too.

          The preview does not work properly for me. I am trying to fix that. Also, I get this error “Failed to load resource: the server responded with a status of 400 (Bad Request)” when trying to load. Maybe it because of my office network. Will try from my home PC and see what happens.

          (0) 
          1. Wolfgang Röckelein

            When doing this in a SQL console, I get four NEO_ schemas, among also mine is and also the _SYS_BIC schema. Also the SQL for the tables works. However the reponse to the init call is empty?

            (0) 
  5. Deep PIYUSH DESAI

    While testing, i found that the app is not able to identify the delimiter. I have csv files with delimiter as comma and semicolon. When i upload these csv files , all the data gets updated in the first column only.

    Is it only for me? What changes we need to do here? Or what is the delimiter that we have in the app?

    (0) 
    1. Benedict Venmani Felix

      I agree. I have the same problem too. I just started learning javascript and HTML and figuring out what is happening with the code is kind of difficult. But I am just taking this as a hands-on learning opportunity.

      (0) 
      1. Deep PIYUSH DESAI

        Okay In that case how are you debugging the application.

        I understand that the logic for all the functions is written in the server side js file which is not available for debugging. I am using Chrome developer tools to debug most of my UI5 apps.

        Also, where you able to find the delimiter used to create the CSV files?

        (0) 

Leave a Reply