Getting null values while importing data from excel sheet to sqql server

Hai Friends,

Actually i want to import excel sheet in to sql server tables. I am writing the following query SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Book1.xls','SELECT * FROM [Sheet1$]') and able to retireve the data but the problem is the data present in the excel sheet is

Usernamepasswordhello1234512345hellohai-123haihaihaihellohello-123

Once i run this query i am getting it as

Username Password hello NULL
NULL hello
hai-123 haihai
haihello hello-123
can anybody please help me out in this case and how to solve this problem and if you have any piece of code please pass it on to me and i am in very urgent need waiting for your replies thanks in advance.

Friends, what i mean to say is i am getting null values in the results.

Getting Matching Records

Hi,

I have the following table:

***********************************************************************************************
Sheet Cycle

Init SC 89
Post NCOA 89
Post Supp 89
Revised Final State Counts 89
Revised Final State Counts 94
***********************************************************************************************

Since "Revised Final State Counts" appears in both cycles 89 & 94. How can I query the table so that I only get that 1 record?

Thanks

You can do query like below:

select t.sheet, min(t.Cycle) as Cycle -- max will also do or avg depending on what you want

from table t

group by t.sheet

If you need to retrieve other columns also then you can do below in SQL Server 2000 (assuming that the combination of (sheet,cycle) is unique):

select t1.sheet, t1.cycle, t1....

from table as t1

where t1.cycle in (

select top 1 t2.cycle

from table as t2

where t2.sheet = t1.sheet

order by t2.cycle desc -- or asc

)

You can do above in SQL Server 2005 like:

select t2...

from (

-- you could use rank/dense_rank also depending on your requirements

select row_number() over(partition by t1.sheet order by t1.cycle desc) as rownum

, t1.sheet, t1.cycle, t1....

from table as t1

) as t2

where t2.rownum = 1