Softnami
Author: Hussain Mir Ali

I am interested in web and mobile technologies.

If you any questions or feedback then message me at devtips@softnami.com.

Announcements
Ads

Call predictor: AI based call prediction

Download

2020

Feb
Jan

2019

Dec
Nov
Jul
May
Mar
Jan

2018

Nov
Sep
Jul
Jun
Apr
Feb
Jan

2017

Dec
Oct
Sep
Aug
Jul
Jun
May
Apr
Mar
Jan

2016

Dec
Oct
Sep
Aug
Jul

Upload CSV to database with Angular-2

The integration of TypeScript(super-set of ES6) into the Angular ecosystem has enabled developers to write more structured code similar to other widely used programming languages such as Java and C#. Also because components replaced the scope and controllers in Angular-2 the learning curve is much easier and using the ES6 import functionality developers can maintain modularity.

In this post I will be building a simple Angular-2 application which allows users to upload a CSV file to  MySQL database using  expressjs.


Building the Database


Step 1: Install and setup MySQL on your machine(preferably linux or Mac) and start the MySQL server.

Step 2: Setup database and table. Use the following code in the MySQL command line:


CREATE DATABASE CSVINFO;
USE CSVINFO;


CREATE TABLE example_data(
date_mark DATE NOT NULL,
employee_name VARCHAR(25) NOT NULL,
employee_address VARCHAR(25) NOT NULL, 
expense_description VARCHAR(500) NOT NULL, 
pre_tax_amount DOUBLE NOT NULL DEFAULT 0.0, 
tax_name VARCHAR(25) NOT NULL, 
tax_amount DOUBLE NOT NULL DEFAULT 0.0,
id INT PRIMARY KEY AUTO_INCREMENT);


Building the Server

Step 1: Create a folder called 'Backend' and under this folder create a file called 'server.js'. Copy and paste the implementation code from down below to the 'server.js' file.

var express = require("express");
var mysql = require('mysql');
var bodyParser = require('body-parser');



var connection = mysql.createConnection({ // Change parameter values for 'user' and 'password' for your MySQL setup.
host: 'localhost',
user: 'root',
password: 'xyz',
database: 'CSVINFO'
});
var app = express();

// parse application/x-www-form-urlencoded
app.use(bodyParser.urlencoded({
extended: false
}));

// parse application/json
app.use(bodyParser.json());

app.use(function(req, res, next) {
res.header("Access-Control-Allow-Origin", "*");
res.header("Access-Control-Allow-Headers", "Origin, X-Requested-With, Content-Type, Accept");
next();
});


connection.connect(function(err) { // Connect to database.


if (!err) {
console.log("Database is connected ... \n\n");
} else {
console.log("Error connecting database ... \n\n");
}
});


app.post("/upload_data", function(req, res) { // REST Endpoint to upload the data from CSV file to database.
var values = [];
var i = 1;
var body = req.body;

for (i; i < body.length; i++) {
values[i - 1] = [body[i][2], body[i][3], body[i][4], body[i][5], body[i][6], body[i][7], body[i][0], body[i][1]];
}

console.log(values);

connection.query('INSERT INTO example_data VALUES ?', [values], function(err, rows, fields) { //Inserting values in bulk.

if (!err) {
console.log('\nSuccessfully updated database: ', rows);
res.sendStatus(200);
} else {
console.log('\nError while performing Query: ', err);
res.sendStatus(500);
}
});
});
app.listen(8000);

Note: Make sure to change the 'user' and 'password' parameters according to the configuration of your MySQL setup.


Step 2: Create 'package.json' file under the same folder.


{
"name": "node-mysql",
"version": "0.0.1",
"dependencies": {
"body": "^5.1.0",
"body-parser": "^1.15.2",
"express": "^4.10.6",
"mysql": "^2.5.4",
"parsers": "^0.1.1"
}
}


Step 3: Install all dependencies using 'npm install' in your terminal.


sudo npm install

Step 4: Start the server using the following command. Make sure you get the 'Database is Connected' message. 



$ node server.js
Database is connected ...


Building the Application

Step 1: .First install the specific generator for yeoman. Generate the boilerplate code for Angular-2 with typescript files

npm install yo generator-angular2-typescript -g

yo angular2-typescript application

Step 2: Create services folder under the app folder and create a file called 'api.service.ts'. This file will enable the application to make HTTP requests. Copy the following code to the file:

import {Injectable} from '@angular/core';
import {Http, Headers} from '@angular/http';
import 'rxjs/add/operator/map';

@Injectable()

export class ApiService{
constructor(private http: Http){
console.log('PostService Initialized...');
}

updateDatabase(params:any){
var headers = new Headers();
headers.append('Content-Type','application/json');
return this.http.post('http://localhost:8000/upload_data',params,{headers: headers});
}

}

Step 3: Under the folder app->home change the 'home.component.ts' file to the following:

import {Component} from '@angular/core';
import { ApiService } from '../services/api.service';

var $ = require('jquery');
require('../../../node_modules/jquery-csv/src/jquery.csv.js');

@Component({
selector: 'home',
template: require('./home.component.html'),
providers: [ApiService]
})

export class HomeComponent {

apiserv:ApiService;
showlist:boolean;
listExpenses: string [];
data_list:any;
hashmap:{} = {};

constructor(apiserv:ApiService){
this.apiserv = apiserv;
}

fileUploadListener($event:any):void{
this.parseCSV($event.target);
}

calculateTotalPerMonth():void{

let self = this;

for(let i =1; i<self.data_list.length; i++){

if(self.hashmap[self.data_list[i][0].split('/')[0]]!==undefined){
self.hashmap[self.data_list[i][0].split('/')[0]] += Number(self.data_list[i][5].replace(",","")) + Number(self.data_list[i][5].replace(",",""));
}else{
self.hashmap[self.data_list[i][0].split('/')[0]] = Number(self.data_list[i][5].replace(",","")) + Number(self.data_list[i][7].replace(",",""));
}
}

self.showlist = true;

console.log(self.hashmap);
self.data_list = Object.keys(self.hashmap).map(function (key) { return " Month: "+key+" Total Cost: "+self.hashmap[key].toFixed(2); });
console.log(self.data_list);
}

parseCSV(csv: any):void{
var file:File = csv.files[0];
var self = this;
var reader:FileReader = new FileReader();

reader.readAsText(file);
reader.onloadend = function (e) {
var csvData = reader.result;
var data = $.csv.toArrays(csvData);

if (data && data.length > 0) {
self.data_list = data;
console.log('Imported -' + data.length + '- rows successfully!');
self.apiserv.createDatabase(data).subscribe(
data => {
console.log(self.data_list);
self.calculateTotalPerMonth();
console.log("DATA block: " + JSON.stringify(data));
},
error => console.log("ERROR block: " + JSON.stringify(error)),
() => console.log('finished')
);
} else {
console.log('No data to import!');
}
};

reader.onerror = function () {
console.log('Unable to read ' + file);
};
}


}

This file contains the logic for parsing the CSV file data using 'jquery-csv' within the 'parseCSV' method. Also after successfully uploading data to database the 'calculateTotalPerMonth' method calculates the total cost per month.

Step 4: Under the folder app->home change the 'home.component.html' file to the following:

<div>
<div class="form-container" [hidden]= showlist>
<div class="heading-container">
<h3>Please select a csv file from your computer.</h3>
</div>
<form>
<div class="upload">
<label><input type="file" accept=".csv" (change) = "fileUploadListener($event)"/></label></div>
</form>
</div>
<div class="monthly-expenses" [hidden]= !showlist>
<div class="heading-container">
<h3>Total expenses per-month.</h3>
</div>
<ul>
<li *ngFor="let item of data_list;">
<a> {{item}}</a>
</li>
</ul>
</div>
</div>


This file contains the main view with two sub-views. One view to show the folder icon and another view which is hidden using '[hidden] !=showlist'  to show the monthly cost list.

Step 4: Under the folder app->css change the 'main.css' file to the following:


body {
font-family: Cambria, Georgia;
background-color: #0080FF;
}

h1 {
color: white;
font-family: Arial, Helvetica, sans-serif;
font-size: 250%;
font-weight: bold;
margin-top: 30px;
margin-bottom: 22px;
}

h3{
font-family: sans-serif;
}

a {
text-decoration: none;
color: white;
}

a:hover {
text-decoration: underline;
}

.header-container, .main-container, .footer-container {
width: 700px;
margin: 0 auto;
}

.heading-container{
background-color: lightgrey;
}

.form-container{
background-color: lightgrey;
height: 500px;
color: grey;
border: 5px solid lightgrey;
border-radius: 5px;
}

.main-container {
margin-bottom: 20px;
box-shadow: 0 1px 4px 0 rgba(0,0,0,0.37);
}

.main-container .resource, .main-container h3 {
padding: 16px 23px;
}

.main-container h3 {
margin: 0;
color: grey;
text-align: center;
font-size: 20px;
font-weight: bold;
background-color: lightgrey;
}

.main-container h4 {
margin: 0;
font-size: 16px;
font-weight: bold;
line-height: 24px;
}

.main-container p {
margin: 0;
}

.upload{
background-image:url('../app/assets/images/folder.png');
background-repeat: no-repeat;
height: 140px;
width: 140px;
border-radius: 50%;
margin: 0 auto;
margin-top: 100px;
}

.upload label{
height: 140px;
width: 140px;
cursor: pointer;
}

.upload input{
display: none;
}

.monthly-expenses{
background-color: lightgrey;
}

ul {
list-style-type: none;
margin: 0;
padding: 0;
}

li {
font: 200 20px/1.5 Helvetica, Verdana, sans-serif;
border-bottom: 1px solid #ccc;
}

li:last-child {
border: none;
}

li a {
text-decoration: none;
color: black;
display: block;
width: 100%;
height: 50px;
padding-left: 12px;
-webkit-transition: font-size 0.3s ease, background-color 0.3s ease;
-moz-transition: font-size 0.3s ease, background-color 0.3s ease;
-o-transition: font-size 0.3s ease, background-color 0.3s ease;
-ms-transition: font-size 0.3s ease, background-color 0.3s ease;
transition: font-size 0.3s ease, background-color 0.3s ease;
}

li a:hover {
font-size: 30px;
background: #f6f6f6;
}


Step 5: Under the app folder create assets folder and save the following image in there:




This image is used in the user interface of the main view.

Step 6: Install the 'jquery' using typings by entering the following command in your terminal:


typings install dt~jquery --global --save

Step 7: Start the application using npm start and you will find the application running on 'http://localhost:3000' in your browser.


npm start

App Running:



Select the 'data_example.csv' :



Monthly expenses after uploading to the database:



Server logs:




Sample CSV used for this demo can be downloaded from:
https://github.com/husenxce/samplefilecsv/blob/master/data_example.csv