Author: Hussain Mir Ali

I am interested in web and mobile technologies.

If you have any questions or feedback then message me at

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 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, 

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
extended: false

// parse application/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");

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

if (!err) {
console.log("Database is connected ... \n\n");
} else {
console.log("Error connecting database ... \n\n");
});"/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]];


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

if (!err) {
console.log('\nSuccessfully updated database: ', rows);
} else {
console.log('\nError while performing Query: ', err);

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';


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

var headers = new Headers();
return'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');

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

export class HomeComponent {

listExpenses: string [];
hashmap:{} = {};

this.apiserv = apiserv;



let self = this;

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

self.hashmap[self.data_list[i][0].split('/')[0]] += Number(self.data_list[i][5].replace(",","")) + Number(self.data_list[i][5].replace(",",""));
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;

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

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

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

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;

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;

background-color: lightgrey;

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;

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;

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: