Read the text file using jQuery and save it to DataBase

Question

I have a file containing

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

And I would like to read it using Jquery and save them to the Local Database in OpenDataBase, where 1 will be the number of steps, and Benz will be another field in the database.

my code to read the file

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

and my code to create the 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 do not know how I can separate the data and put it in the database using javascript.

Thanks.

Answer 1

Here is the code 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 the text file, if you can, they should not be needed (your counter will do this for you).

Change the code to read the text file:

  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 have deleted numbers and quotation marks, these should only be 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 save the file as is, change the line below:

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

Now the array contains the number and name of the car (odd number, even the name of the car). We can get rid of double quotes (SQL may cause an error on them):

 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). }