Upload data to HANA table with SAPUI5 FileLoader and HANA Extended Services
Hi All, there could be many instances where end users would require the ability to upload a file of data to a table in HANA on a day to day basis where the Table Import functionality is not sufficient. In this blog I demonstrate the File upload capability using SAPUI5 and HANA Extended Services. Note: this is on SPS06.
- Create a new SAPUI5 Application Project
- Create a hdbtable file which creates the table to upload the data to. Note Batch_id will be a combination of the file name and timestamp. Col1 will store the actual uploaded data.
- Add the following code to WebContent/index.html file
<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<script src="/sap/ui5/1/resources/sap-ui-core.js"
id="sap-ui-bootstrap"
data-sap-ui-libs="sap.ui.commons,sap.ui.table"
data-sap-ui-theme="sap_goldreflection" >
</script>
<!-- add sap.ui.table,sap.ui.ux3 and/or other libraries to 'data-sap-ui-libs' if required -->
<script>
sap.ui.localResources("demofileupload");
var view = sap.ui.view({id:"idFileUpload1", viewName:"demofileupload.FileUpload", type:sap.ui.core.mvc.ViewType.JS});
view.placeAt("content");
</script>
<script type="text/javascript" src="/sap/ui5/1/resources/jquery-sap.js"></script>
</head>
<body class="sapUiBody" role="application">
<div id="content"></div>
</body>
</html>- Add the following code to the view file FileUploader.view.js. This contains a FileLoader control, an Upload button and a table view to display the uploaded data. You will notice I have added a second Upload button. I will explain this further later in the blog but this is to demo how to address the cross-site request forgery security concern.
sap.ui.jsview("demofileupload.FileUpload", {
getControllerName : function() {
return "demofileupload.FileUpload";
},
createContent : function(oController) {
jQuery.sap.require("jquery.sap.resources");
var oPanel = new sap.ui.commons.Panel("Panel",
{text: "File Upload",
height: "750px"
});
var oSplitter = new sap.ui.commons.Splitter("ScreenSplitter",
{splitterOrientation: "Horizontal"});
var oVertLayout = new sap.ui.commons.layout.VerticalLayout("VertLayout");
oSplitter.addFirstPaneContent(oVertLayout);
oPanel.addContent(oSplitter);
/*************************************************************************
* File Uploader Browse
*************************************************************************/
var oFLTxt = new sap.ui.commons.TextView("FileLoaderText", {text:"Please choose file for upload."});
oVertLayout.addContent(oFLTxt);
var oFileUploader = new sap.ui.commons.FileUploader("FileLoader");
oFileUploader.attachUploadComplete(oController.doFileLoadComplete);
oVertLayout.addContent(oFileUploader);
/*************************************************************************
* Upload button
*************************************************************************/
var oButton = new sap.ui.commons.Button({
id : this.createId("UploadButton"),
text : "Upload"
});
oButton.attachPress(oController.doFileUpload);
oVertLayout.addContent(oButton);
//To address cross-site request forgery security concern
var oButton2 = new sap.ui.commons.Button({
id : this.createId("UploadButton2"),
text : "Upload 2"
});
oButton2.attachPress(oController.doFileUpload2);
oVertLayout.addContent(oButton2);
/*************************************************************************
* Batch table
*************************************************************************/
var oModel = new sap.ui.model.odata.ODataModel("../Services/MY_FILE_UPLOAD_TABLE.xsodata",false);
var oControl;
var oTable = new sap.ui.table.Table("BatchTable", {tableId: "BatchTableId",visibleRowCount: 10});
oTable.setTitle("Batch file data");
oControl = new sap.ui.commons.TextField().bindProperty("value","BATCH_ID");
oTable.addColumn(new sap.ui.table.Column({label:new sap.ui.commons.Label({text: "Batch ID"}),
template: oControl,
sortProperty: "BATCH_ID",
filterProperty: "BATCH_ID"
}));
oControl = new sap.ui.commons.TextField().bindProperty("value","COL1");
oTable.addColumn(new sap.ui.table.Column({label:new sap.ui.commons.Label({text: "Column 1"}),
template: oControl,
sortProperty: "COL1",
filterProperty: "COL1"
}));
oTable.setModel(oModel);
oTable.bindRows("/FILE_UPLOAD_TABLE");
oSplitter.addSecondPaneContent(oTable);
return oPanel;
}
});- Add the following code to the controller file FileUploader.controller.js. The functions doFileUpload and doFileUploadComplete are called when the first “Upload” button is pressed. The function doFileUpload2 is called when the second button “Upload2” is pressed. This is to demo how Cross-site Request Forgery concern can be addressed.
sap.ui.controller("demofileupload.FileUpload", {
/**
* Called when a controller is instantiated and its View controls (if available) are already created.
* Can be used to modify the View before it is displayed, to bind event handlers and do other one-time initialization.
*/
// onInit: function() {
//
// },
/**
* Similar to onAfterRendering, but this hook is invoked before the controller's View is re-rendered
* (NOT before the first rendering! onInit() is used for that one!).
*/
// onBeforeRendering: function() {
//
// },
/**
* Called when the View has been rendered (so its HTML is part of the document). Post-rendering manipulations of the HTML could be done here.
* This hook is the same one that SAPUI5 controls get after being rendered.
*/
// onAfterRendering: function() {
//
// },
/**
* Called when the Controller is destroyed. Use this one to free resources and finalize activities.
*/
// onExit: function() {
//
// }
doFileUpload : function(oEvent)
{
var url = "../Services/BatchFileUpload.xsjs";
var fileLoader = sap.ui.getCore().byId("FileLoader");
var fileName = fileLoader.getValue();
if (fileName == "" )
{
jQuery.sap.require("sap.ui.commons.MessageBox");
sap.ui.commons.MessageBox.show("Please choose File.", sap.ui.commons.MessageBox.Icon.INFORMATION, "Information");
}
else
{
url = url+"?file_name="+fileName;
fileLoader.setUploadUrl(url);
fileLoader.upload();
}
},
doFileLoadComplete : function(oEvent)
{
jQuery.sap.require("sap.ui.commons.MessageBox");
var sResponse = oEvent.getParameter("response");
sap.ui.commons.MessageBox.show(sResponse, sap.ui.commons.MessageBox.Icon.INFORMATION, "Information");
sap.ui.getCore().byId("BatchTable").getModel().refresh();
},
doFileUpload2 : function(oEvent)
{
var fileLoader = sap.ui.getCore().byId("FileLoader");
var fileName = fileLoader.getValue();
jQuery.sap.require("sap.ui.commons.MessageBox");
if (fileName == "" )
{
sap.ui.commons.MessageBox.show("Please choose File.", sap.ui.commons.MessageBox.Icon.INFORMATION, "Information");
}
else
{
var uploadUrl = "../Services/BatchFileUpload.xsjs?file_name="+fileName;
var formEle = jQuery.sap.domById("FileLoader");
var form = $(formEle).find("form")[0] ;
var fd = new FormData(form);
$.ajax({
url: uploadUrl,
type: "GET",
beforeSend: function(xhr)
{
xhr.setRequestHeader("X-CSRF-Token", "Fetch");
},
success: function(data, textStatus, XMLHttpRequest) {
var token = XMLHttpRequest.getResponseHeader('X-CSRF-Token');
$.ajax({
url: uploadUrl,
type: "POST",
processData :false ,
contentType: false ,
data:fd,
beforeSend: function(xhr)
{
xhr.setRequestHeader("X-CSRF-Token", token);
},
success: function(data, textStatus, XMLHttpRequest)
{
var resptext = XMLHttpRequest.responseText;
jQuery.sap.require("sap.ui.commons.MessageBox");
sap.ui.commons.MessageBox.show(resptext, sap.ui.commons.MessageBox.Icon.INFORMATION, "Information");
sap.ui.getCore().byId("BatchTable").getModel().refresh();
},
error: function(data, textStatus, XMLHttpRequest)
{
sap.ui.commons.MessageBox.show("File could not be uploaded.", sap.ui.commons.MessageBox.Icon.ERROR, "Error");
}
});
}} ) ;
}
}
});- Create server side javascript file ..Services/BatchFileUpload.xsjs to handle the extract of the data from the file and use batch insert to load the data into the table.
function escape(v1)
{
var v2 = v1.replace(/&/g,'&').replace(/</g,'<').replace(/>/g,'>');
return v2;
}
$.response.contentType = "text/html";
try
{
var conn = $.db.getConnection();
var filename = $.request.parameters.get("file_name");
var pstmtTime = conn.prepareStatement( "select UTCTOLOCAL(CURRENT_UTCTIMESTAMP,'EST') from dummy");
var rs = pstmtTime.executeQuery();
var batchTimestamp;
if (rs.next())
{
batchTimestamp = rs.getTimestamp(1);
}
var batchId = filename+"_"+batchTimestamp;
var pstmt = conn.prepareStatement( "insert into MURPHP11.\"pm.demo.fileupload2.Tables::MY_FILE_UPLOAD_TABLE\" (batch_id,col1) " +
"values(?,?)" );
if($.request.entities.length>0){
var file_body = $.request.entities[0].body.asString();
var allTextLines = file_body.split(/\r\n|\n/);
var lines;
var entries;
var col;
pstmt.setBatchSize(allTextLines.length-1);
for (lines=0; lines<allTextLines.length; lines++)
{
entries = allTextLines[lines].split(',');
col = entries.splice(0,allTextLines.length);
if ( col[0].length > 0 )
{
col[0] = escape(col[0]);
pstmt.setString(1,batchId);
pstmt.setString(2,col[0]);
pstmt.addBatch();
}
}
pstmt.executeBatch();
}
else
{
$.response.setBody("No Entries");
}
pstmt.close();
conn.commit();
conn.close();
$.response.setBody("[200]:Upload successful!");
}
catch(err)
{
if (pstmt !== null)
{
pstmt.close();
}
if (conn !== null)
{
conn.close();
}
$.response.setBody(err.message);
}- Create an xsodata file MY_FILE_UPLOAD_TABLE.xsodata to expose the table in odata. This will be used in our view to display the uploaded data to the user.
- Add .xsaccess and .xsapp files.
- Share the project. Right Click on the project and go Team->Share
- Commit the project. Team -> Commit
- Activate the project. Team -> Activate
- Test the application (note: used Chrome as my browser):
- Enter HANA credentials:
- Choose file to upload. (note: file just has 1 column of data – see below)
- Press the “Upload” button
- There are a number of security concerns that are addressed in the SAP HANA Development Guide. One of these is Cross-site Request Forgery.
“To protect SAP HANA XS applications from cross-site request-forgery (XSRF) attacks, make sure you always set the prevent_xsrf keyword in the application-acess (.xsaccess) file to true, as illustrated in the following example:
{ “prevent_xsrf” : true }
The prevent_xsrf keyword prevents the XSRF attacks by ensuring that checks are performed to establish that a valid security token is available for given Browser session. The existence of a valid security token determines if an application responds to the client’s request to display content. A security token is considered to be valid if it matches the token that SAP HANA XS generates in the backend for the corresponding session.”
To address this concern I added the { “prevent_xsrf” : true } to the .xsaccess file and the following code to the FileUpload.controller.js file as the standard FileLoader.upload() method does not seem to fetch and check the existence of the XSRF security token.
.
doFileUpload2 : function(oEvent)
{
var fileLoader = sap.ui.getCore().byId("FileLoader");
var fileName = fileLoader.getValue();
jQuery.sap.require("sap.ui.commons.MessageBox");
if (fileName == "" )
{
sap.ui.commons.MessageBox.show("Please choose File.", sap.ui.commons.MessageBox.Icon.INFORMATION, "Information");
}
else
{
var uploadUrl = "../Services/BatchFileUpload.xsjs?file_name="+fileName;
var formEle = jQuery.sap.domById("FileLoader");
var form = $(formEle).find("form")[0] ;
var fd = new FormData(form);
$.ajax({
url: uploadUrl,
type: "GET",
beforeSend: function(xhr)
{
xhr.setRequestHeader("X-CSRF-Token", "Fetch");
},
success: function(data, textStatus, XMLHttpRequest) {
var token = XMLHttpRequest.getResponseHeader('X-CSRF-Token');
$.ajax({
url: uploadUrl,
type: "POST",
processData :false ,
contentType: false ,
data:fd,
beforeSend: function(xhr)
{
xhr.setRequestHeader("X-CSRF-Token", token);
},
success: function(data, textStatus, XMLHttpRequest)
{
var resptext = XMLHttpRequest.responseText;
jQuery.sap.require("sap.ui.commons.MessageBox");
sap.ui.commons.MessageBox.show(resptext, sap.ui.commons.MessageBox.Icon.INFORMATION, "Information");
sap.ui.getCore().byId("BatchTable").getModel().refresh();
},
error: function(data, textStatus, XMLHttpRequest)
{
sap.ui.commons.MessageBox.show("File could not be uploaded.", sap.ui.commons.MessageBox.Icon.ERROR, "Error");
}
});
}} ) ;
}- The “Upload 2” button calls the above code.
Note if you try to use the first Upload button it will return an error as we have added the prevent_xsrf : true to the .xsaccess file.
Nice work Peter, very insightful and detailed. Great working example using ODATA services.
Great work Peter,
Can we load .xls or .xlsx file in a similar way?
I was only able to load .txt or .csv files, complex files like .doc, .xls give an error. Is there a workaround for these filetypes?
Thanks and Regards,
Shreepad
Hi Shreepad,
I was able to load an .xls file successfully but like you I am also receiving an error with .xlsx. Seems the error is in the server side BatchFileUpload.xsjs with how it's reading the data in the xlsx. I will try and look into it at some stage but in the meantime I would suggest debugging the BatchFileUpload.xsjs and see how it's behaving when reading the xlsx. May require a tweaking for xlsx.
Thanks
Peter
hi peter,
i am able to upload csv file but when uploading xlsx file getting error like it display binary format message. could you please help me out how to upload excel file.
hi:Peter
I have a similar problem,I want to upload excel file and display content in sapui5 table in
Eclipse.
please tell what should I do
best regard!
Hi Peter,
very nice work! It was exactly what I needed. However I found two typos. So in case someone experience some problems with your code and looks down here:
FileUpload.view.js
line 19: Missing semicolon
line 48: Missing }) after "Batch_ID"
Regards,
Steve
Thanks Steve, must have been a copy and paste issue, I've corrected those lines there now.
hi:Peter
In Eclipse how to upload excel files and display its contents in the view of sapui5 table ,then click button 'create ' these data into the abap database using odataservice.
Do you have any solutions ? I appreciate for your help.
Hi Huang, not sure I understand your question. This solution is focused on direct upload to HANA. You will probably find something similar for ABAP in the ABAP or Netweaver Gateway forums.
Peter
Hi Peter,
CSV file importing is fine but when we use excel file getting error i think error problem with
var file_body = $.request.entities[0].body.asString(); when display file_body getting binary format information.
could you please help me out.
thanks,
badari
Hi Badari, yes for xlsx you will need to do some decoding. Here are a couple of links that may help with this:
http://codetheory.in/parse-read-excel-files-xls-xlsx-javascript/
http://blog.innovatejs.com/?tag=xlsx-js
I haven't done this yet so let me know if this works for you as a couple of other folks have enquired about this also.
Thanks
Peter
Hi Peter,
nice tutorial. Worked out well. I just came a cross with an issue regarding the bulk mode. With a file containing only one row I got the error msg. "bulk insert not enabled ". Due to this post HANA XS js problem this is a common error, when using a csv. with only one line. With a file containing more lines, it worked out well.
Another point:
Do you have any advice how I could implement a picture upload into hana and how I can fetch the pictures later on for displaying on a SAP UI5 page? Or should pictures generally be stored on a separate file-system?
Thanks and best regards
Tom
Glad it's helped Tom. Yeah for single row you'd need to add in some logic on the server side code to do single insert instead of batch.
Regarding images, I haven't done anything in this area but on upload I guess you could insert it into a BLOB table field to store it there.
Alternatively, if it is not a requirement for an end user as such to be able to do the upload then I'm thinking you could just include the image as part of your XS project and upload to the HANA repository which would then be accessible to your SAPUI5 app.
Maybe Thomas Jung might have some recommendations here.
Thanks
Peter
Hello Peter,
Thanks much for the solution.
I have tried to implement the same. Upon executing it shows the XSJS file content instead of the local file whose path is provide to uploader to upload.
var uploadUrl = "../Services/BatchFileUpload.xsjs?file_name="+fileName;
The pop shows the XSJS file content.
Can you please help with this.
Best regards,
Pavan
Hi Tom,
Even I faced the same issue while uploading single row.
Anyways, thanks for highlighting 🙂
Regards,
Rauf
Hello Shaikh,
Were you able to resolve this issue.
I mean Uploading single row.
Regards,
Chandu
CSV UPLOAD USING SPLIT APP SAPUI5
Thanks for the above method, it works perfetcly well for .JS Views, when working with XML Views, there are a few things to change, here are my observations
var formEle = jQuery.sap.domById("FileLoader"); //JS Views
var formEle = jQuery.sap.domById("FileUploader--FileLoader"); //XML Views
ensure to investigate your DOM to get the right id, else you will not be able to pass to file content to the;XSJS file
Hi could you please help me with XML Views code
Hi Peter,
Kudos!! Thank you sharing this. It worked flawlessly. Keep sharing your knowledge 🙂 .
--
Regards,
Rauf
Hi Peter,
I am getting 405 Method not allowed error, Could you please tell me how to resolve the issue.
Thank you....
Ebinezar
Hi Peter,
Thanks for your example, it works perfectly 🙂 ...but unfortunately only with files up to 50-100 MB. 🙁 It fails with >100 MB files. Apparently the size of some objects exceeds a size limit ("allocation size overflow").
I'm working on a small tool which uploads huge csv files into HANA. Therefore I'm looking for a way how to read the csv file in a stream line-by-line to process the data in smaller chunks. Any ideas would be greatly appreciated!
Thanks for your help!
Kind regards,
Jan
Hi Jan,
if you're using a browser to do this, then you are limited by two factors - one being the manner in which a browser reads a local file, and the POST limit of the browser/any HANA xsjs/xsodata services. Typically, any implementation of the client-side File API (native implementation or JS) will read a local file in its entirety - you have no control over how much of the file you'd like to read. Likewise, HANA XS has a hard POST limit, although I can't tell you where to find it!
Caveat: the current working draft of the JS File API includes a Blob type file, which will serve your needs perfectly (breaking large, local files up into chunks); but it is only a recent working draft and not yet fully implemented/supported. /sigh
You might consider VB Scripting as a mechanism for reading chunks of files and uploading these, but then of course, you'd need the client to be IE and for the user to give the control approval to run
You didn't mention the implementation language of the tool (I'm assuming UI5), but if it's not, FTP would be your fall back. Provided you have an IIS server available in the back-end, you can use C# to perform client-side FTP with code alone...
Interesting problem - please let me know how you get on 🙂
Cheers,
Hagen
Hi Jan Pur,
Where you able to develop the tool for bulk load? It would be great if you could share across the same..
Thank you
I am trying to implement the above functionality. But it says cannot find the xsodata file. Can someone help me here.
What I am trying to do is this:
1. Create a local HANA table.
2. Upload csv data in to that table.
Nice post. Has anyone done the reverse: export HANA table into csv file into HANA server and publish out / map file locations to URL links for end users to be able to download the csv files?
Hi Peter,
Is the XLSX solution already fixed by someone? I am very new into this.
Appreciated if someone can share the solution.
Thanks in advance,
Cheers,
Rabie
Hi Experts,
Could you please send me this project to my email id .
amar82158@gmail.com
It would be great help.
Regards,
Amar
no front end display of the file uploader
all other files are active.
Hi Peter,
Could you/anyone provided resolution asap for this issue.
I getting error while uploading the data from .csv file into HANA DB Table . Please finc below the screenshot of screen while trying to upload file data (using upload2 button).
I have doubt in following code in BatchFileUpload.xsjs file :
var batchId = filename+“_”+batchTimestamp; var pstmt = conn.prepareStatement( “insert into MURPHP11.\”pm.demo.fileupload2.Tables::MY_FILE_UPLOAD_TABLE\” (batch_id,col1) “ + “values(?,?)” );
what is MURPHP11 here (Your’s schema name or user name ?),
why used (batch_id ,col1) where it is define ?, as it is defined like BATCH_ID and COL1 in table.
Yes MURPHP11 was my schema, You should use whatever schema you are working in.
Hi Peter - Nice blog! I am trying to use this example to send an image and process this and store in the HANA DB as a BLOB.
I can debug and see that there is looping over the posted content. However, it is not storing correctly in the database. Is there something different I need to do with image data?
Regards,
Jay
I figured out how to send an image and store it.
Nice one.
Hello,
i have a problem with a part that in your demonstration is hidden with a blue mark -> during the process of activation i am getting an error "A SQL error occurred while creating the table in the schema "MY_SCHEMA". Do you know what can be the reason? Which schema should i use?
Best regards,
Emilia Olszak
Hi Emilia, your best bet is to look at the log files and see if there is further information there. Also there have been many revisions of HANA since this blog was written so would suggest looking at more recent documentation relevant to the revision of HANA you are working on for creating tables etc...
Thanks
Peter
I am pretty new to xsjs i am trying to perform the same example.But i am getting below error
Can you please help me in understanding this statment in XSJS Script
var pstmt = conn.prepareStatement(“insert into MURPHP11.\”pm.demo.fileupload2.Tables::MY_FILE_UPLOAD_TABLE\” (batch_id,col1) “+”values(?,?)”);
MURPHP11-This is the schema name
\”pm.demo.fileupload2.Tables- What is this path?
MY_FILE_UPLOAD_TABLE-This is table Name
This is my XSJS Statment.
function escape(v1)
{
var v2 = v1.replace(/&/g,'&').replace(/</g,'<').replace(/>/g,'>');
return v2;
}
$.response.contentType = "text/html";
try
{
var conn = $.db.getConnection();
var filename = $.request.parameters.get("file_name");
var pstmtTime = conn.prepareStatement( "select UTCTOLOCAL(CURRENT_UTCTIMESTAMP,'EST') from dummy");
var rs = pstmtTime.executeQuery();
var batchTimestamp;
if (rs.next())
{
batchTimestamp = rs.getTimestamp(1);
}
var batchId = filename+"_"+batchTimestamp;
var pstmt = conn.prepareStatement( "insert into \TRAIN05.Tables::MY_FILE_UPLOAD_TABLE\"(batch_id,col1) " +"values(?,?)" );
if($.request.entities.length>0){
var file_body = $.request.entities[0].body.asString();
var allTextLines = file_body.split(/\r\n|\n/);
var lines;
var entries;
var col;
pstmt.setBatchSize(allTextLines.length-1);
for (lines=0; lines<allTextLines.length; lines++)
{
entries = allTextLines[lines].split(',');
col = entries.splice(0,allTextLines.length);
if ( col[0].length > 0 )
{
col[0] = escape(col[0]);
pstmt.setString(1,batchId);
pstmt.setString(2,col[0]);
pstmt.addBatch();
}
}
pstmt.executeBatch();
}
else
{
$.response.setBody("No Entries");
}
pstmt.close();
conn.commit();
conn.close();
$.response.setBody("[200]:Upload successful!");
}
catch(err)
{
if (pstmt !== null)
{
pstmt.close();
}
if (conn !== null)
{
conn.close();
}
$.response.setBody(err.message);
}
Hello Peter,
thank you for this great Blog.
Question:
Is it thinkable that you could load the image from the Database where it is stored as BLOB back into
the SAPUI5 application as an image ?
me and my coworker are stuck at this : https://answers.sap.com/questions/298420/load-blob-into-sapui5.html
Hello Peter,
Thank you for this wonderful Blog. This is what i was looking for.
Thanks,
Jenish
Hi Peter,
Very nice Blog!! Even After years it does this trick with few tweaks. This updates only this first column to the DB, what if i have a second or multiple columns which need to updated??
Can you throw some light on it ??
Thanks,
Ashwin