Skip to Content

XSImport – a HANA app for importing CSV’s

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

21 Comments
You must be Logged on to comment or reply to a post.
  • 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

  • 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

  • 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

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

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

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

  • Hi Paul,

    nice job! I found an error importing table with TIMESTAMP field

    Even trying importing null date doesn’t work.

    Can you hel me with this.

     

    Thank you