Read a text file with JQuery and store in DataBase

Question

I have a file containing

1 : "Benz"
2 : "Bmw"
3 : "Porche"
4 : "Ferrari"

And I would like to read it with Jquery and store them in Local Database in OpenDataBase, with 1 will be a number of step and Benz will be the other field in data base.

my code for reading the file

jQuery.get('file/words.txt', function(data) {
    alert(data)
});

and my code for creating database

var db = openDatabase( ’mydb’, ’1.0’,
                   ’database for test’,
                   2 * 1024 * 1024
                 );
db.transaction(function (tx) {
tx.executeSql(’CREATE TABLE IF NOT EXISTS Car
                (number INT, name VARCHAR(100))’);

});

I don't know how can i separate the data and put them in database with javascript.

Thanks.

Answer 1

Here's the code do you what you want:

// assume this is your data (I've added the newline as well)
var textData = '1 : "Benz" \n2 : "Bmw" \n3 : "Porche"\n4 : "Ferrari" ';

// turn data into array
var ary = textData.split('\n').map(function(v) {
    return v.split(':').map(function(v2) {
        // make sure we remove the quotes and spaces
        return v2.replace(/"/g, '').trim();
    });
})

// function to escape double quotes to prevent sql injection
function escapeSql(str) {
    return (typeof str === 'number' ? str : str.replace(/"/g, '"'));
}

// open database
var db = openDatabase('mydb', '1.0', 'database for test', 2 * 1024 * 1024);

// create table
db.transaction(function(tx) {
    tx.executeSql('create table if not exists Car(step, make)');
});

// insert data
db.transaction(function(tx) {
    // loop through each item and insert the data, notice how we call escapeSql to prevent sql injection
    ary.forEach(function(item) {
        var sql = 'insert into Car(step, make) values(' + escapeSql(item[0]) + ', "' + escapeSql(item[1]) + '")';
        tx.executeSql(sql);
    });
});

var sql,
    cars = [];

// read data from table
db.transaction(function(tx) {
    tx.executeSql('select * from Car', [], function(tx, results) {
        var len = results.rows.length;
        for (var i = 0; i < len; i++) {
            cars.push({
                step: results.rows.item(i).step,
                make: results.rows.item(i).make
            });
        }

        console.log(cars);
    }, null);
});

Output:

Answer 2

Get rid of the numbers and quotes in your text file if you can, they should not be needed (your counter will do this for you).

Change your code for reading the text file to this:

    var file = "file/words.txt";
    function getFile(){
       $.get(file, function(txt){
           var lines = txt.responseText.split("\n");
           for (var i = 0, len = lines.length; i < len; i++) {
               save(lines[i]);
           }
       }); 
    }

Now you have an array with each line of your file; if you removed the numbers and quotation marks, it should just be the car names.

for (var i = 0; i < lines.length; i++) {
   tx.executeSql('INSERT INTO Car (id, name) VALUES (i, lines[i]);
}

If you want to keep the file as is, change the below line as shown:

 var lines = txt.responseText.split(":");

Now the array contains the number and the car name (odd is the number, even is the car name). We may want to get rid of double quotes (SQL might throw error on those):

lines.replace(/"/g, '').trim();

 for (var i = 0; i < lines.length; i++) {
       tx.executeSql('INSERT INTO Car (id, name) VALUES (i, lines[i+1])');
       i++; // we iterate again because we want an odd number 
            // (we skip over one iteration as that'd be the car, and we want the next # instead). 
    }