Upload CSV to database with Angular-2

Tuesday, January 17th, 2017

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