Importing CSV file data into sqlite3

I was trying to import the data from CSV file into sqlite3 database. Ideally this should be very simple task, with following the steps given in the sqlite tutorial. It is a matter of calling the sqlite command with separator argument, followed by an import operation, as listed below.

sqlite3 test.db  "create table t1 (t1key INTEGER PRIMARY KEY,data TEXT);"
sqlite3 -separator , test.db ".import some.csv t1"

Except that main attribute of my CSV file was that it could contain single records with embedded comma. I was hoping that sqlite3 would be smart enough to detect that the fields were enclosed within double quotes and then separate by comma. But I soon realized that only a code specifically dealing with CSV would know about this.  As we can see in the example above, the import is a generic code and as a user I listed comma as a separator.

My Data looked something like this :

"1","data1"
"2","data2,data3"

So like any *nix geek would do, I tried providing double quote and comma as a separator. To my surprise it worked very well. I though separator would take only single character, and I had provided two (three?). Anyway, important thing to remember is to escape the single quote with a backslash (I didn’t try it without the backslash, may be that would work too)

So here is the syntax that worked :

sqlite3 -separator \", test.db ".import mydata.csv mytbl"

Update : Turns out SQLite Manager is much better solution after all.  It is an Extension for Firefox and other apps to manage any sqlite database. Not only it took care of above situation, it also handled empty cells as well where the command line failed with following error message :

line 4: expected 3 columns of data but found 2

Data with missing cells : Notice two successive commas :

"1","data1","data2"
"2","data3,data4","data5"
"3",,"data6"

Access your todo list from multiple locations

Here is a clever method to access your todo list from multiple locations.

Ingredients

• todo.sh – This is a command line shell scripts which allows to manage your TO DO list
• DropBox Account : Free, Syncs various computers, Cross Platform (at least works on Windows as well as Linux)

Recipe

• Make Sure you have installed Dropbox clients on all of your machines (Mainly Work and Home)
• Download and install todo.sh in your Dropbox folder. This is main trick to make your TODO list portable.
• You need to modify the .todo file (or todo.py script itself) to set the TODO_DIR variable. I’ve created a folder called “tododir” in my Dropbox folder, and set the TODO_DIR variable as “tododir”. This helps avoiding path problems on various machines.
• (Optional) Read the tutorial available at LifeHacker
• Open command Prompt, and take charge of your TODO lists.

Why This Idea Rocks

• Since the todo list is maintained as simple text file, any machine/platform allows you to directly view and edit your todo list.
• While one would install Dropbox clients on all of their regularly used machines, You can always view and edit your todo.txt even from a cyber cafe, using Dropbox’s web interface.

Are you on Windows ?

• If you are on Windows, you will require cygwin which provides unix utilities on windows. (I was unable to get todo.sh working with mSys, which I got as part of mSys Git (More about it later, in another post))
• There is a variation of todo.sh written in Python (which is what I’m using these days). If which case, you need Python instead of Cygwin. Python version supports color coding of the items based on priority even on Windows.

Can I not just use USB Drive instead ?

• Sure you can.  But some employers do not allow the employees to connect their personal USB devices to office computers. (Security, Virus threats) in which case Dropbox is better alternative.
• Even Cyber cafe won’t allow to plug in your USB drive, but you sure can access your files from Web Front end of Dropbox.

Productive use of Active Desktop – More ideas

Once you understand the idea behind using the Active Desktop, you can come up with several creative and unique ideas to make this feature work for you.  Let me get you started by listing some that came to my mind.

1. Apart from the mini phone list, I also have my Plan of the Week, as another HTML on my Desktop. At the end of each week, I create the plan of next week (Friday 4-6PM). Again, it is very easy to convert any piece of text into an HTML.
2. Use a web page as Sticky Note to yourself. No Coding needed. Check this out.
3. You can put your Google Calendar on your Desktop.

Please share your ideas in the comments

Next Page »