Excel Sheet IMEX=1 and how text gets treated as numbers

I was surprised today to see how microsoft excel finds out the datatype of rows in a excel sheet.

Let us say you have an Excel sheet with 60 rows and the first column has first 20 rows as numbers and the next 40 rows as alphabets, some thing like,

UserIdentifier First name last name
10 John Smith
20 Steve Martin
....
....
....
ALAN Alan Smith

User identifier in this case is alphanumeric (can contain both numbers or alphabets)

If you import this sheet using ADO in ASP or in VB
excel will look at first 8 rows and decide for itself that the datatype of first column is number and hence will import null for Alan and all other non-numeric rows.

This is based on a registry setting,
The registry key where the settings described above are located is: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel

called TypeGuesses = 8

So it looks for first 8 rows to decide what is the data type and then fixes the datatype.

the property IMEX=1 means treat all cols as text but still it too does not work.

http://support.microsoft.com/kb/194124

This is explained in the KB above.

The only way to fix this is to increase the type guesses in teh registry to a higher value.

So if you have a excel sheet say with 3000 rows and first 1000 rows are numbers and next 2000 rows are alphabets, it will work fine only if typeguesses = 2001 otherwise it will import spaces for every alphabet.

This is totally crazy and Microsoft should have come up with some other better way of finding the datatype in a excel sheet.

ERROR [IM003] Specified driver could not be loaded due to system error 5 (IBM DB2 ODBC DRIVER)

Today was one of those "dog days", where you go to solve a problem and you get lead in to another, and if you google for a solution, you get 10 different answers none of which work and it drives you nuts.

But this was very tricky problem.

Here is the issue,
1) I use DB2, ASP.Net with vb.net code behind,
2) DSN is created in ODBC and sits on windows dsn,
3) Run regedit and go to HKEY_LOCAL_MACHINE >> SOFTWARE >> ODBC >> ODBC.INI >> select the name of the dsn >> Right click permissions >> Add the user name underwhich IIS runs (or) the current logged in user and give him read access (i gave full control)

4) Open mycomputer >> properties >> advanced tab >> Environmental Variables >> click path and add ;.; to the existing path at the end.
5) Restart the machine, since it is a odbc change we need to restart pc.

One problem leads to another:

• When i went to try to restart (i was in remote desktop), i could not see the restart menu, since only disconnect is shown.
• In such situations when we open task manager there used to be a menu called "Shut down" which would be visible, but unfortunately cannot see this too.
• So one problem leads to another, how am i going to restart the damn pc.
• Found solution at last, go to desktop and send Alt-F4 and then the shut down menu appears.

Logged back in and was able to login to the website, no ODBC errors.

The issue was there is this ODBC Key in the registry, the current user did not have read permission to read the key and the key told ASPNET where the db2 odbc driver was, so as we cannot read the key we cannot use the driver and hence cannot open a connection.

Great atlast it works :)

Google signup restriction and email alias

I wanted to create another email to synchronize my website, my blog and my email id, so tried to create an id without a dot, and i learnt something new today,

1) If you already have a friend, he can send you an invite, but if you dont have an invite, if you try to login via signup, it is not that easy, i saw a unique feature, when you try to login to google they ask you to give your cell phone number, what cell phone ???? lol then how will people in asia or europe sign up, if i give my asian number will you send an sms to that phone.

https://www.google.com/accounts/SmsMailSignup1

There was a big explanation on this link which said, we ask for cellphone so that we can send you an invite which will stop abuse of email, blah... blah.... ok looks innovative, but at the same time i have 97 invites available to send to anyone who can register lol there is a big hole isnt there? You can send an invite to anyone they need not provide their cell phone to register but new users need to provide cell phone to get sms invite???

Any way the idea of sending an invite via sms to phone and allowing sign ups was pretty good, also you have a very good track of who uses the number, just in case you want to track back the user bingo, it becomes very very easy. So you can accomplish a lot, find out who is messing or who is playing prank etc.... but if this is enforced throughout for all users then it is a great feature.

2) I did not know until today that for gmail id, you can use any number of dots in your email id, so let us say the email id is say donaldduck@gmail.com, if some one sends an email to d.o.naldduck@gmail.com or donald.duck@gmail.com or any number of dots donald duck will still get it :) wow it makes the duck sooo happy doesnt it ;)
http://mail.google.com/support/bin/answer.py?ctx=%67mail&hl=en&answer=10313

And above is the link which explains "am i receiving someones email" no you are not.
And also ofcourse they blocked duplicate signups, so if someone else tried to create a new id say do.naldduck@gmail.com they will say it is already used up.

Wow nice idea !!! think outside the box :)

Assembly Info usefulness

Most of our project we leave the assemblyinfo file empty, this is a very bad practice, if someone is going to use our assembly as a COM object they need to understand who is the provider.

So look at this artcile,

http://www.artima.com/intv/principles4.html

http://www.codinghorror.com/blog/archives/000141.html

I understood the real use of it only today.

Problems with chm files

This is so pathetic, i got lot of chm files on my hard drive (useful stuff) but all these days, when i click them i used to get this 404 page, at that time i used to click other chm files and they worked fine.

Recently i felt like banging myself on the wall when i came to notice this in the properties.

When you download a chm from the web, XP automatically blocks it (isnt this ridiculous).

So you have to right click on the file and say unblock to show its contents.

This was frustrating, if i had known this earlier i wouldnt have missed many chm files.

Internet Explorer 7.0 Vs Fire Fox 2.0

Browser Battles still continue, now it is whether the 800 pound gorilla IE 7.0 or the zilla from Mozilla :)

This months Information Week carried an excellent article comparing both the browsers.

Here is the gist of the same,

Eight major browser features which are important to businesses,

1) Remote management
IE allows remote management of browser where as Fire fox needs explicit plug-ins

2) Tabbed browsing
IE 7 added it but firefox already had it.

3) Security
IE had lot of security holes which are plugged but fire fox never had these issues.

4) Search
search bars can now be added inside IE7 but fire fox already has this.

5) Feeds
Support for RSS, Atom feeds is provided new for IE but firefox already had it

6) Standards
IE trying to comply with W3C, CSS & DOM

7) Printing
Good feature is IE tries to shrink the web page and print it, fire fox doesnt

8) Saves / restore
When browser crashes firefox remembers and restores session, IE doesnt

Now i leave it to you to decide which one to use. :)

Dont ask me what i use ;)

CAPTCHA project

CAPTCHA is an acronym for "Completely Automated Public Turing test to tell Computers and Humans Apart".

We all would have seen lot of sites which show an image which is disturbed and has some text in it and we need to read and enter that text in to a textbox so that we can avoid automatic spam bots or automated testing tools entering the page.

This is a project of Carnegie Mellon university and they hold the copyrights for it.

http://www.captcha.net/

http://en.wikipedia.org/wiki/Captcha

http://www.aladdin.cs.cmu.edu/

ALADDIN is the algorithmic adaptation dissemination and integration team at CMU their papers are great :)

Virtualization is going to hit you soon

Well Apple is talking about it, microsoft is talking abt it, all Fortune 100 and Fortune 500 companies are using it.

Soon virtualization is going to hit every developer,

We all have heard about VMWare, Microsoft Virtual PC (we are talking about giants), but this small company is rocking too, they have a 50$ product for virtualization and it rocks.
http://www.parallels.com/en/products/workstation/

I spoke with my pal who works for VMWare, some of the features he is talking about make me open my mouth.

VMotion - http://www.vmware.com/products/vi/vc/vmotion.html

Ok so here is my version of "VMotion for Dummies"

So you are in this super duper Server of yours, you installed VMWare and you have 10 virtual machines, suddenly your raid card or network card smokes out, so what do you do !!!!!!!!

Go nuts and drive ppl around you nuts. :) No here is what you do.

If you are smart enough you setup a VMWare Garden (ha ha ha that rings a bell, like IIS Web Farm), and you install VMotion, so if anything to hardware failure happens, the VMotion kicks in and it moves the virtual machine from one VM Ware server to another VMWare server.

Wow, where do these guys get such ideas from and the interesting of all is, when VM is moving it still servers.

So at last in this world 100% uptime is guaranteeed.

hey scripting guy

I had a need today to play with some VB Script, i was amazed to see the number of samples provided by microsoft,

http://www.microsoft.com/technet/scriptcenter/resources/qanda/default.mspx

This book has a lot of samples, what surprises me is, one team example scripting team @ MS is doing this but why not teams like Message Queue team or other teams !!!!!!!!

May be Institutionalization is tough there tooo :)

GC Memory leask

http://blogs.msdn.com/ricom/archive/2004/12/10/279612.aspx