How do I load initial data via javascript?

Question

I want to preload 9000 records via either a file or a whatever is best. The idea is I want to have there 9000 records somewhere and I want to load them into a sqlite DB via phonegap. I have all this working other then loading the data from somewhere. Here is what i have so far

I have a file records.csv file and here is my code

function populate_events_from_csv(db){
  var event_data; 
  $.ajax({
      type: "GET",
      url: "records.csv",
      dataType: "text/csv",
      error: function (request, status, error) {
         console.log('fail'); 
        console.log(request.responseText);
      },
      success: function(data) { 
               console.log('success'); 
               event_data = $.csv.toObjects(data); }
   });
   console.log(event_data);
    db.transaction(function(tx) {
    var q = 'INSERT INTO Events (id, name) VALUES (?, ?)';
      _(event_data).each(function(row) {
        tx.executeSql(q, [row.id, row.name]);
      });
    });
}

This approach would work but it fails because of the double and single quotes in the records csv

If anyone sees what i am doing wrong or another solution to initially inserting these 9000 records

try to replace quotes in csv file like `var expr = new RegExp('"', 'g'); data = data.replace(expr, '\\"');`. — Victor, Apr 17 '13 at 05:19
Why do you think there's something wrong with the quotes? Does `executeSql` or `csv.toObjects` not get them correct? If so, provide the function's code and a CSV sample — Bergi, Apr 17 '13 at 16:01
your code is synchronous (it depends on the ajax call being completed before trying to access `event_data`). Copy and paste the database access part into the success function, or specify `async: false` in your ajax options... — tucuxi, Apr 17 '13 at 18:35

score 1 · Answer 1 · answered Apr 17 '13 at 17:30

1

function populate_events_from_csv(db) {
    var event_data;
    $.ajax({
        type: "GET",
        url: "records.csv",
        dataType: "text/csv",
        error: function (request, status, error) {
            console.log('fail');
            console.log(request.responseText);
        },
        success: function (data) {
            console.log('success');
            event_data = $.csv.toObjects(data);
            db.transaction(function (tx) {
                var q = 'INSERT INTO Events (id, name) VALUES (?, ?)';
                _(event_data).each(function (row) {
                    tx.executeSql(q, [row.id, row.name]);
                });
            });
        }
    });

}

answered Apr 17 '13 at 17:30

jgillich

71,459
6
57
85

should the `_(` not be a `$(`? – tucuxi Apr 17 '13 at 18:37
@tucuxi He is probably using Underscore.js: http://underscorejs.org/#each – jgillich Apr 17 '13 at 18:39
thanks; similar functionality under same name with `$(something).each(function(index,object){})` threw me off – tucuxi Apr 17 '13 at 19:07

tucuxi · Answer 2 · 2013-04-17T19:08:25.690

Why not convert the data into JSON, which will make it load faster into your javascript (minimal parsing), and simplify your code at the same time?

Once JSONized, you will be able to write

function populate_events(db, url) {
    $.getJSON(url).done(function(data) {
        db.transaction(function (tx) {
            var q = 'INSERT INTO Events (id, name) VALUES (?, ?)';
            $(data).each(function (index, row) {
                tx.executeSql(q, [row.id, row.name]);
            });
        });
    }).fail(function(request, status, error) {
        console.log('fail');
        console.log(request.responseText);
    });
}

Converting CSV to JSON is left as an exercise, but there are many tools out there that can handle the task. I would use a Python script (csv + json libraries).

Also, this code solves the **actual** problem in your original code: you are trying to use `event_data` before assigning to it. If you want your original code to work, make the AJAX call `async: false`. — tucuxi, Apr 17 '13 at 18:33

How do I load initial data via javascript?

2 Answers2