CSV to SQLlite tutorial

README.mkd

1. Start up the sqlite3 commandline on a new datafile: sqlite3 myFooData.db

2. You should see a prompt something like this:

SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> 

1. Create a table to accomodate your data. Column definitions follow the form 'name datatype' and the columns of a database are defined as a set enclosed in ():

sqlite> create table myFooData (frequency integer, mi integer, phrase text);

1. Define your csv separator and import your csv into the table:

sqlite> .separator ","
sqlite> .import myFooData.csv myFooData

1. If all went well, your data is loaded, to see how many rows you have:

sqlite> select count(*) from myFooData;

1. To see all your data:

sqlite> select * from myFooData;

1. To exit sqlite simply type Ctrl-d. You should now see a datafile named myFooData.db in your current directory. To load it again, just start sqlite3 with it as an argument as in the first step.

2. Next Steps:

   • http://stackoverflow.com/questions/9802680/importing-files-with-extension-sqlite-into-r
   • http://www.bioconductor.org/help/course-materials/2006/rforbioinformatics/labs/thurs/SQLite-R-howto.pdf