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"

Why SharpDevelop is better IDE ?

In my first post about IronPython, I documented how installing IronPython Studio was painful (Needed Visual Studio shell, which in itself was confusing). When I started with IronPython I did not know about any other IDE, hence I went ahead with IronPython Studio. But later I came to know about SharpDevelop.

My initial problem with SharpDevelop was that it needed .NET 3.5 SP1 at the minimum. I had just gone through the painful exercise of downloading and installing the prerequisites for IronPython Studio. So I was in no mood of downloading another big chunk before I can start my IronPython Development. But once I got past my initial development cycle, I wanted to give Sharp Develop a try.

After using both the ID interchangeably, I finally settled on SharpDevelop as my choice for IronPython Development

Read more..

Works with Firefox !!

We keep hearing that lot of important sites do not work with Firefox e.g.

1. Rohit Srivastava is Frustrated
2. During the Breakfast with Mozilla event that took place in Pune on February 15th, Arun made a remark that several web developers still develop for IE 6 (which was released around 2000, eight years ago)

While that may have been correct a while ago, I’m sure that things are improving. Since I switched full time to Ubuntu few weeks ago, I had no choice but to use Firefox (considering there is no IE on Linux – Not sure if IE works under Wine) and I am glad to say that I had no issues what so ever. I’m using Firefox 3.0.5, as well as Firefox 3.1Beta 2 on Ubuntu 8.10 (if it matters)

I’m listing the websites that I know work with Firefox, in the hope that this would help more and more people to switch to Firefox.

• Online Banking (India)
  1. HDFC Bank
  2. ICICI
  3. Citibank
  4. Axis Bank (Thanks to @shantanoo)
• Online Banking (US)
  1. Wells Fargo
• Online Trading
  1. E-Trade – A leader in Online trading and Investing
• Localized Contents
  1. Marathi
     1. Leading Marathi newspaper – eSakal (While developed in ASP, it works, and works well in Firefox)
     2. Pune Pratibimb – Sakaal newpaper’s marathi blog
     3. Maharashtra Times : Marathi Newspaper by India Times
     4. Dainik Aikya
     5. Website Dedicated to famous marathi playwright – ??. ?.
  2. Gujarathi
     1. Nobat : Online Newspaper
     2. Lokprabha Newsline : Online Newspaper
     3. Divya Bhaskar : Online Newspaper
  3. Bengali
     1. Bangla Daily News : Online Newspaper
  4. Oriya
     1. Dharitri : Online News in Oriya
  5. Hindi
     1. Navbharat Times
  6. Nepali (Script is Devnagari)
     1. Himgiri News

Sure everyone knows Firefox is (relatively) secure compared to IE. But if folks can’t get their work done, they WILL continue to use IE. So in order to show them that indeed more and more websites work with Firefox without any issues, I started this list.  Obviously, this list is far from complete. But with your support, we can make this better, in the hopes that users do not have the option of not-using Firefox, just because their favorite website doesn’t work with firefox.

If you are not using Firefox yourself, please click on the button on the right to download Firefox right now !!

Please leave comments with URLs that you know work with Firefox.

Related articles by Zemanta

• Mozilla for your business: Meet Seth Bindernagel and Arun Ranganathan of Mozilla – Sun, 15 Feb (punetech.com)
• Internet Explorer’s share of the browser market fell below 70% in November (Victor Godinez/techblog.dallasnews.com) (techmeme.com)

Next Page »