Insert data from a csv file in a sqlite


#1

I would like to insert data from a csv file in a sqlite for android. I added this code into the \ www \ js \app.js
I tried the csv located in different directories: \ www \ js and \ www

After creating the database, I can´t get to load the data from csv.

(.csv Example: firstName111, lastName111, telephone11, email11, note111)

angular.module('starter', ['ionic','ngIOS9UIWebViewPatch', 'starter.controllers', 'starter.services', 'ngMaterial', 'ngMessages', 'ngCordova'])
.run(function ($ionicPlatform, $cordovaSQLite, $rootScope, $ionicHistory, $state, $mdDialog, $mdBottomSheet) {

    //Create database table of contracts by using sqlite database.
    //Table schema :
    //Column	   Type	     Primary key
    //  id	        Integer	    Yes
    //  firstName	Text	    No
    //  lastName	Text	    No
    //  telephone	Text	    No
    //  email	    Text	    No
    //  note	    Text	    No
    //  createDate	DateTime	No
    //  age	        Integer	    No
    //  isEnable	Boolean	    No

    function initialSQLite() {
        db = window.cordova ? $cordovaSQLite.openDB("contract.db") : window.openDatabase("contract.db", "1.0", "GuiaVlaDB", -1);
        $cordovaSQLite.execute(db, "CREATE TABLE IF NOT EXISTS contracts " +
            "( id           integer primary key   , " +
            "  firstName    text                  , " +
            "  lastName     text                  , " +
            "  telephone    text                  , " +
            "  email        text                  , " +
            "  note         text                  , " +
            "  createDate   dateTime              , " +
            "  age          integer               , " +
            "  isEnable     Boolean)                ");
			
			

			// Variable for prepare query statement to insert contracts.
			var query = "INSERT INTO contracts (       "    +
						"  firstName      ,            "    +
						"  lastName       ,            "    +
						"  telephone      ,            "    +
						"  email          ,            "    +
						"  note          ,            "    +
						"  createDate     ,            "    +
						"  age            ,            "    +
						"  isEnable)                   "    +
						"  VALUES (?,?,?,?,?,?,?,?)      ";						
													   
			window.requestFileSystem(LocalFileSystem.PERSISTENT, 0, function(obtenerFS){
			obtenerFS.root.getFile("guia.csv", null,function(entrada){
			  entrada.file(function (fichero){
				  lector = new FileReader();
				  lector.readAsText(fichero)
				  lector.onload = function (evt){
				  var csv = evt.target.result
				  var data = $.csv.toArrays(csv);
				  
				   for (var i=1; i <data.length; i++){
		   

					   // Execute query statement from query variable.
						$cordovaSQLite.execute(db, query,
							[data[i][0]          ,
								data[i][1]        ,
								data[i][2]       ,
								data[i][3]           ,
								data[i][4]      ,
								  ,
								  ,
								true       
							]);						   
									   
						//$cordovaSQLite.execute(db, 'INSERT INTO contracts (firstName, lastName, telephone, email, note, createDate, age) VALUES ( "' + data[i][0] + '", "' + data[i][1] + '","' + data[i][2] + '", "' + data[i][3] + '", "' + data[i][4] + '", ' + data[i][5] + ', ' + data[i][6]  + ')');
				   }	
			   
				  
				  }
			  });
					  
			});
		  });				
	
    };
    // End creating SQLite database table.

`


#2

Hi,

I think here csv file is not loading when you are trying to insert data into table.

Instead of doing this you can put this csv code in a separate .csv file and then load it using $http call, convert it into a JSON array and then insert it into your DB.
Hope it will work for you.


#3

Hi Sorb,

I’m planning to create a app in iOS, i will save user data in sqlite. I want to save the data in sqlite to CSV / CSV to sqlite. Could you please help me out.