Am I understanding how to import my .csv file into sqlite?

I currently have a .csv file with several unlabeled columns of data, which to my knowledge translate to the following datatypes in sqlite:

• datetime (in the format 7/19/2011 12:00:00 PM) -> numeric
• double -> real
• char(1) -> text
• float -> real

I can create the database by doing the following:

sqlite> create table myTable (myVar1 numeric, myVar2 real, myVar3 text, myVar4 real);
sqlite> .separator ","
sqlite> .import myFile.csv myTable

Then I copy and paste the newly created myTable.db into the "assets" folder in my project in eclipse. I make a DatabaseHelper class that extends SQLiteOpenHelper, and then I can start using and reading from the database in my Android project.

Am I getting this right? I've never used a database before and I've seen so m开发者_StackOverflow中文版any vastly different instructions on doing this. Some of my questions are-- do I have to label the columns of my .csv file? Is my .csv file not "simple" enough to just use .import and I'll need to find a program to translate it? I've come across sites saying that I need to rename something (which I don't seem to have) to "_id", and I don't know what this is, where this is, or how to do this, or if it's even necessary, or what it's for. What else am I missing?

I think you are getting it "right" except for that first datetime column. You should use the TEXT type, not a numeric type.

Also, you can inspect your data after the import to see if all is well, especially with that datetime field:

SELECT * FROM myTable ORDER BY RANDOM() LIMIT 10;

UPDATE

In response to the OP's last comment: my understanding of how you store date(time) is that it depends on your context. So if the date in the flat file is in the format "7/19/2011 12:00:00 PM", then without any transformation it'll be imported as TEXT anyway.

Importing csv into database is 15 lines of code task and it gives you more control over this process. Table columns names like "var3" are just terrible, however there is no need to rename it in database you can just use sql aliases:

select myVar1 _id, myVar2 from myTable 

_id is common name for primary key column of table (it's usually numeric column witch must be unique). Every ADK class using datastore assumes relays on it, so it's nice to use this convention. If you just want read only database you can prepare db locally and find some tutorials how to include it into your app.