How to convert the resultset from a query to EXCEL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to convert the resultset from a query to EXCEL

Author

Topic

Raoulh79
Starting Member

24 Posts

Posted - 2007-11-12 : 04:37:15

DEAR ALL,

How can i export the result set of a query in EXCEL, so that every field goes to a sepate field in the excel sheet???

For example

select * from ITEMS
where
n0_item_code =1001

Any help will be appreciated!!!!

Thanks in advance....

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-12 : 04:42:24

Refer http://sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

Raoulh79
Starting Member

24 Posts

Posted - 2007-11-12 : 04:55:09

How can i join this 2 queries together: I mean the query from insert into bla bla bla and the query i want to run ?????

Apart from using DTS and Export wizard, we can also use this query to export data from SQL Server2000 to Excel

Create an Excel file named testing having the headers same as that of table columns and use this query

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable
and

select * from ITEMS
where
n0_item_code =1001

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-12 : 05:14:00

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]')
Select query 1
union all
Select query 2

Madhivanan

Failing to plan is Planning to fail

Raoulh79
Starting Member

24 Posts

Posted - 2007-11-12 : 16:26:30

Dear All i run the query like:

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\test.xls;',
'SELECT * FROM [sheet1$]')
select * from items

where I saved the excel file in c:\test.xls and the worksheet by default has the name sheet1, but i get the following message:

Server: Msg 7354, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' supplied invalid metadata for column 'sz_item_ref_no'. The data type is not supported.

I didnt put data type for the columns on the excel, the data i get from the query are numbers and text i dont get why it gives me these error???

Thanks in advance!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-12 : 17:28:11

What datatype is then 'sz_item_ref_no'? Did it occur to you that you can CAST or CONVERT the data to proper datatype?
And you must also be aware that the path to the excel file is RELATIVE from SQL Server point of view, not your local workstation.
Use UNC path if path not found.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-13 : 02:28:39

what is the datatype of sz_item_ref_no in items table?

Madhivanan

Failing to plan is Planning to fail

Raoulh79
Starting Member

24 Posts

Posted - 2007-11-13 : 02:50:35

The 'sz_item_ref_no' in the Item's table has the internal code of the product, it is not sth strange as data type. As i told you the data types that i used are or varchar or text or integer. I dont understand why excel can not take this data types. The sql server is in my laptop where i have created a TEST environment and i am working on it. So the SQL Server 2000 and the Query are both run in the same environment...
Does the structure of the query is right??? I am not sure for that!!!

Thanks in advance!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-13 : 03:26:48

I am not sure EXCEL accepts TEXT datatype.
I think there is a limit for 1024 bytes in each cell.

E 12°55'05.25"
N 56°04'39.16"

Raoulh79
Starting Member

24 Posts

Posted - 2007-11-13 : 03:38:42

I entered in the database and the 3 fields that i want to import on the excel are the following: The message i get is from the first field sz_item_ref_no.

sz_item_ref_no char(16)
sz_long_descr varchar(60)
sz_short_descr varchar (20)

I want to underline that the fields on the database are fixed, they are related to an application and i can not change them. Do I have to change sth in the query???

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\test.xls;',
'SELECT * FROM [sheet1$]')
select * from items

The [sheet1$] is right???? Do i need to put the dollar sign anyway???

Any suggestion to over ride the error message...

Thanks in advance!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-13 : 04:06:55

Try to use MSDASQL provider temporarily to see if you get more precise error message
http://weblogs.sqlteam.com/peterl/archive/2007/10/24/Getting-errors-when-working-with-Excel-and-SQL-Server.aspx

E 12°55'05.25"
N 56°04'39.16"

Raoulh79
Starting Member

24 Posts

Posted - 2007-11-13 : 07:58:54

Peso i tried what you said, and to say the truth i get the same message:
Server: Msg 7354, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' supplied invalid metadata for column 'sz_item_ref_no'. The data type is not supported.

The query was like this:

select * from OPENROWSET('MSDASQL',
'Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\test.xls;','SELECT t1.sz_item_ref_no, DESCRIPTIONS.SZ_SHORT_DESCR, DESCRIPTIONS.SZ_long_DESCR FROM [sheet1]')
select t1.sz_item_ref_no, DESCRIPTIONS.SZ_SHORT_DESCR, DESCRIPTIONS.SZ_long_DESCR from ITEMS
where
n0_item_code =1001

In addition i have to i tried to change [sheet1$] with [sheet1] and the result i get was the following:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'sheet1'. Make sure the object exists and that you spell its name and the path name correctly.]

The syntax of the query is correct???? Any idea what is going on!!!

Thanks in advance

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources