hmm..will let you know my findings or what ever i do. thanks man. repped.

Ok. One more thing (and I use this technique a lot). You can conditionally inject a zero to your string data based on conditions.

This code will inject a '0' in front of any single digit number.

i am changing the excel column to varchar before i actually write to the sql table.
i used this earlier:

INSERT INTO table(c1,c2)
SELECT Code1,Desc
FROM OPENDATASOURCE
('Microsoft.Jet.OLEDB.4.0','Data Source="C:\test1.xls";Extended Properties="Excel 8.0;IMEX=1;HDR=YES"')
...Sheet1$

I modified the above as follows and i think i see correct results:

INSERT INTO table(c1,c2)
SELECT CAST(Code1 AS varchar(6)),Desc
FROM OPENDATASOURCE
('Microsoft.Jet.OLEDB.4.0','Data Source="C:\test1.xls";Extended Properties="Excel 8.0;IMEX=1;HDR=YES"')
...Sheet1$

i have to do vast testing before i can say that this worked.

Be careful in situations where you have different data types in the same column.

For example, in my spreadsheet, the first four rows contain information that really has nothing to do with the data. The real data starts from the fifth row and down. So in my example, I have integer data in the first column (starting from the fifth row). However, cell A1 contains a title that is a string. If I use cast(f1 as int) in my code, it will fail on cell A1.

In your case, you're casting to a varchar so no big deal, but if you are casting to a numeric data type you'll need to be aware of the pit falls.

Bottom line - lots of error checking and data massaging may be required.

Higa, you are right. But in my case, the excel sheet will have only two columns and I know for sure it doesnt cause any problems.
Now I have a new problem. Its about sheet names in the excel. I dont have the problem and solution clearly yet. I will post both of them soon.

OK..here it is. I am trying to read a specific sheet from an excel file. I realized that the name of the sheet may not be same all the time, but the position of the sheet is fixed. I can take the sheet name as input, but sometimes it may have spaces in that name and i tried in vain to read those sheets with spaces. So, two ways..one is to be able to access the sheets by their postion or to be able to read the sheet names with spaces.

If anyone know how to tackle this problem, please post a solution.

I don't know how to specify the sheet by position.

I also struggled with the sheet names containing spaces. This seems to be a limitation as far as I was able to research. In the end, I replaced all spaces in the sheet name with the underscore character. (This was the least headache.)