Skip to Content
Author's profile photo Paul Aschmann

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

Assigned tags

      21 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Sergio Guerrero
      Sergio Guerrero

      awesome post Paul

      Author's profile photo Stefan Koster
      Stefan Koster

      Nice work! Have been searching for a flexible solution to upload data for some time and this seems to fit perfectly with a few minor changes.

      Author's profile photo Jeremy Ma
      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

      Author's profile photo Paul Aschmann
      Paul Aschmann
      Blog Post Author

      Thanks Jeremy - I have update the source on Github 🙂

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Paul Aschmann
      Paul Aschmann
      Blog Post Author

      Hi Deep, the easiest way to get this into your system would be to download the files from github and simply import them into a new project in either the Web Developer Workbench or in Eclipse. Another option is I host the delivery unit here: metric² | Real-time operational intelligence for SAP HANA(newsletter signup required)

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Benedict Venmani Felix
      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

      Author's profile photo Former Member
      Former Member

      Thank You, That helped

      Author's profile photo Wolfgang Röckelein
      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

      Author's profile photo Wolfgang Röckelein
      Wolfgang Röckelein

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

      Has anybody solved this already?

      Author's profile photo Paul Aschmann
      Paul Aschmann
      Blog Post Author

      Hi Wolfgang - could you elaborate on what is not working in the trial account? I would be happy to try and help. Wolfgang Roeckelein

      Paul

      Author's profile photo Wolfgang Röckelein
      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.

      Author's profile photo Benedict Venmani Felix
      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.

      Author's profile photo Wolfgang Röckelein
      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?

      Author's profile photo Benedict Venmani Felix
      Benedict Venmani Felix

      I see those extra NEO schemas too. Some user must have used the wrong grant statement and granted everyone in HCP access to their NEO schema. This explains,

      access unknown schema | SCN

      Author's profile photo Wolfgang Röckelein
      Wolfgang Röckelein

      But eg SAP HANA XS Web IDE Tutorial or Develop Your First SAP HANA Native Application ... | SCN suggests to use the schema _SYS_BIC, eg in step 8, which I followed.

      Author's profile photo Former Member
      Former Member

      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?

      Author's profile photo Benedict Venmani Felix
      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.

      Author's profile photo Former Member
      Former Member

      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?

      Author's profile photo Andrea Botto
      Andrea Botto

      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