Populating an IndexedDB with .csv file data

How do you populate an IndexedDB with a csv file? I can't find a very simple guide anywhere online.

Here is an example of one of the csv files that I want to use, they're all pretty much like that: http://www.mediafire.com/?hlx2tpacw5dqat5

First column (row two down) is a timestamp (MS Excel), column 2 (row 2 down) is the amount of readings, all other columns are temperature readings. The 900 above indicates the time interval in seconds between readings. The final reading in a row is null and should be disgarded( or taken) away from the number of readings. Therefore, the first reading in a row is was taken at the time of the rows timestamp minus (900 seconds multiplied by the (number of readings in the row minus one)). In the case of the first value on the first row this value was taken at (40271.0625-(0.00024*900*(1024-1))).

What I'd like to have is two columns, so to speak, in my IndexedDB. On开发者_C百科e for the time of the reading (obviously these would be unique) and one for the temperatures. Any ideas? Any help would greatly be appreciated!

Thanks

You can use Alasql JavaScript SQL database library. It has special operators for loading data from TXT, CSV, TAB, XLS, and XLSX files. Also it works with IndexedDB. In this example you need to add alasql.min.js library to your project, than create or attach (if already created) IndexedDB database, then simply SELECT columns INTO IndexedDB table FROM CSV datafile with headers.

<script src='alasql.min.js'></script>
<script>
    alasql('CREATE INDEXEDDB DATABASE IF NOT EXISTS geo;\
            ATTACH INDEXEDDB DATABASE geo; \
            USE geo; \
            DROP TABLE IF EXISTS country; \
            CREATE TABLE country; \
            SELECT * INTO country FROM CSV("country.csv",{headers:true});\
            SELECT VALUE COUNT(*) FROM country',[], function(res){
        document.write('Number of records loaded into IndexedDB: ', res.pop());
    });
</script>

Try this sample at alasql.org site.

There's no native way to load a CSV into IndexedDB. It's totally possible though, and there's no reason why WebSQL would be a better option (aside from compatibility)^. You could even use the HTML5 File API to load the CSV into the browser.

Assuming you can use something like the HTML5Rocks Todo app as a reference on how to add rows to an IndexedDB database, hard part would be parsing the CSV file into an object. IndexedDB uses "object stores" that are not naturally columnar. So you'll have to turn each line of the CSV into an object before adding it to the database.

Once you've got that, you have to add the rows to the database one by one. You'll probably want to put an index on any columns you want to use a lookup values. In your case, the timestamp, readings count and temperate value.

^ WebSQL is a deprecated API so using it is not a good idea.

David Flanagan in Javascript, The Definitive Guide v6 has an example, which loads a csv file into an indexed database. It worked for me. see link on github

Assuming you have the CSV parsed into an array-of-arrays, i.e. var data = [["timestamp", ["temp1", "temp2", "temp3"], ["timestamp2", ["temp4", "temp5", "temp6"], etc..];

Then I'd map the columns to row names:

columns = ["timestamp", "temperature"];

and then use them to construct a simple dictionary:

var objectStore = db.transaction("temperatures", "readwrite").objectStore("temperatures");

for (var i = 0; i < data.length; i++) {
    var data = {};
    var row = data[i];
    for (var j = 0; j < row.length; j++) {
        data[columns[j]] = row[j];
    } 
    objectStore.put(data, i);
}

This way you can access the rows with objectStore.get(rownumber)

But to be more NoSQLish about it, I'd make one of the columns (say "timestamp") be the keypath of the object, and use multiEntry to index all the subvalues of the temperature array. Create it this way:

db.createObjectStore("temperatures", {keyPath: "timestamp"});
db.createIndex("temp", "temperature", {"multiEntry": true});

Then when you put, don't bother using the key:

objectStore.put(data);

then you can retrieve stuff based on that:

objectStore.get("2012-04-05");

But what's really great is that now you have a reverse index of temperatures back to time, allowing you to say something like "give me all records that have temperatures between 20 and 30 degrees":

objectStore.index("temp").openCursor(IDBKeyRange.bound(20, 30)).onsuccess = function(e) {
    var cursor = e.target.result;
    if (cursor) {
        cursor.continue();
        console.log("Found record: ", cursor.value);
    }
}

You just have to be careful about making the key path of the objectstore point to a uniquely identifiable column.

indexedDB doesn't really work like that. Instead of having tables and views etc like an SQL database, you have object stores where javascript objects are stored against keys.

To make an object store emulate a table you could populate it with objects like {row: 1, data: [col1, col2,...]}. You can then retrieve rows and cells etc. However depending how you want to use the database this might not be a good solution since the usual constraints of a table (deleting rows shifts the lower rows up, no missing columns etc) won't automatically apply. Maybe webSQL (which is based on SQLite) would be a better option?