In this blog, we will see how can we import a csv file in SAP UI5 application. 

This would be a bit different from importing excel file just as I explained inthe previous blog, since in this technique we wont be rquired to use a third party library.

Just like in my previous blog, lets keep the use case simple. We will have a simple view with a FileUploader to browse a csv file and a table to display the data.

<core:View xmlns:core="sap.ui.core" xmlns:mvc="sap.ui.core.mvc"
	xmlns="sap.m" xmlns:u="sap.ui.unified" xmlns:l="sap.ui.layout.form"
	controllerName="com.fileuploader.FileUpload" xmlns:html="http://www.w3.org/1999/xhtml">
	<Page title="Demo on CSV File Upload">
	<content>
		<l:SimpleForm editable="true">
		 <l:content>
		  <Label text="File Name">
		  </Label>
		<VBox>
		 <u:FileUploader id="idfileUploader" width="50%"
		    sameFilenameAllowed="false" buttonText="" fileType="CSV"
		    placeholder="Choose a CSV file" style="Emphasized">
		</u:FileUploader>
		<Button text="Upload" press="onUpload"></Button>
		</VBox>
		</l:content>
		</l:SimpleForm>

		<Table id="idTable" items="{/}">
		<items>
		    <ColumnListItem>
			<cells>
			<Text text="{VBELN}"></Text>
			<Text text="{ERDAT}"></Text>
			<Text text="{VBTYP}"></Text>
			<Text text="{TRVOG}"></Text>
			<Text text="{AUART}"></Text>
			</cells>
		   </ColumnListItem>
                </items>
	        <columns>
			<Column>
			<Text text="Sales Document"></Text>
			</Column>
			<Column>
			<Text text="Date"></Text>
			</Column>
			<Column>
			<Text text="Type"></Text>
			</Column>
		        <Column>
			<Text text="Sales Org"></Text>
			</Column>
			<Column>
			<Text text="Category"></Text>
			</Column>
		</columns>
		</Table>
		</content>
	</Page>
</core:View>

Now update the code in the onUpload function in the respective controller.

onUpload : function(e) {
	
	var fU = this.getView().byId("idfileUploader");
	var domRef = fU.getFocusDomRef();
	var file = domRef.files[0];
	
	
	// Create a File Reader object
	var reader = new FileReader();
	var t = this;
	
	reader.onload = function(e) {
	    var strCSV = e.target.result;
	    var arrCSV = strCSV.match(/[\w .]+(?=,?)/g);
	    var noOfCols = 5;

	    // To ignore the first row which is header
	    var hdrRow = arrCSV.splice(0, noOfCols);

	    var data = [];
	    while (arrCSV.length > 0) {
		var obj = {};
		// extract remaining rows one by one
		var row = arrCSV.splice(0, noOfCols)
		for (var i = 0; i < row.length; i++) {
		    obj[hdrRow[i]] = row[i].trim()
		}
		// push row to an array
		data.push(obj)
	    }
	    
	    // Bind the data to the Table
	    var oModel = new sap.ui.model.json.JSONModel();
	    oModel.setData(data);
	    var oTable = t.byId("idTable");
	    oTable.setModel(oModel);
	};
	reader.readAsBinaryString(file);
    }

The above code in onUpload function will read the data in CSV file and load it to json model and bind it to the table.

Hereby, we have successfully uploaded the CSV to UI5 application and shown it on the table. Now, as per requirement user can select a particular record and perform CRUD operations.

This way we have seen both ways of importing Excel and CSV files to UI5 application. We can also add some additional validations on excel imported files by simply comparing the desired template with the imported file template. This we can see in the next blog.

There can be multiple scenarios, where user would be required to import excel, csv file to UI5 application and then perform actions on selected line Items. In this blog, let us see ways to upload an excel or CSV file to UI5 application.

Normaly to do so, we have 2 ways:

1) Use external library - Sheet.js

2) Use javascript abilities - only for CSV files

Use External Library

Let's keep the use case simple, we will have one view having a FileUploader and to display the data lets bind it to a table control.

<mvc:View controllerName="com.UploadExcel.controller.MainView" xmlns:mvc="sap.ui.core.mvc" displayBlock="true" xmlns="sap.m"
	xmlns:u="sap.ui.unified">
	<Shell id="shell">
		<App id="app">
			<pages>
				<Page id="page" title="Read From Excel">
					<customHeader>
						<Bar>
							<contentMiddle>
								<Label text="Read Data From Excel"/>
							</contentMiddle>
							<contentRight>
								<u:FileUploader id="FileUploaderId" sameFilenameAllowed="true" iconOnly="false" buttonOnly="true" fileType="XLSX,xlsx"
									icon="sap-icon://upload" iconFirst="true" style="Emphasized" change="onUpload"/>
							</contentRight>
						</Bar>
					</customHeader>
					<content>
						<Table items="{localModel>/items}">
							<columns>
								<Column>
									<Label text="Name"/>
								</Column>
								<Column>
									<Label text="Age"/>
								</Column>
								<Column>
									<Label text="Job"/>
								</Column>
								<Column>
									<Label text="Address"/>
								</Column>
							</columns>
							<items>
								<ColumnListItem>
									<cells>
										<Text text="{localModel>Name}"/>
										<Text text="{localModel>Age}"/>
										<Text text="{localModel>Job}"/>
										<Text text="{localModel>Address}"/>
									</cells>
								</ColumnListItem>
							</items>
						</Table>
					</content>
				</Page>
			</pages>
		</App>
	</Shell>
</mvc:View>

		onUpload: function (e) {
			this._import(e.getParameter("files") && e.getParameter("files")[0]);
		},

		_import: function (file) {
			var that = this;
			var excelData = {};
			if (file && window.FileReader) {
				var reader = new FileReader();
				reader.onload = function (e) {
					var data = e.target.result;
					var workbook = XLSX.read(data, {
						type: 'binary'
					});
					workbook.SheetNames.forEach(function (sheetName) {
						// Here is your object for every sheet in workbook
						excelData = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);

					});
					// Setting the data to the local model 
					that.localModel.setData({
						items: excelData
					});
					that.localModel.refresh(true);
				};
				reader.onerror = function (ex) {
					console.log(ex);
				};
				reader.readAsBinaryString(file);
			}
		}

Our main part of the code is done, we need to add the external library reference in the component.js file.

var jQueryScript = document.createElement('script'); jQueryScript.setAttribute('src', 'https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.10.0/jszip.js'); document.head.appendChild(jQueryScript); var jQueryScript = document.createElement('script'); jQueryScript.setAttribute('src', 'https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.10.0/xlsx.js'); document.head.appendChild(jQueryScript);

Now, click on the browse button and select an excel file, and click on open. After selection the data is shown in the table.

We can also see how we can validate the excel file using the header names in the next blog.