XSImport – a HANA app for importing CSV’s
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
awesome post Paul
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.
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
Thanks Jeremy - I have update the source on Github 🙂
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
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)
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
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
Thank You, That helped
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
Unfortunatly this does not seem to work in a trial account.
Has anybody solved this already?
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
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.
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.
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?
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
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.
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