SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Export to Excel
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 30

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 05/18/2005 :  02:02:46  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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 these queries

1 Export data to existing EXCEL file from SQL Server table
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;', 
    'SELECT * FROM [SheetName$]') select * from SQLServerTable


2 Export data from Excel to new SQL Server table
select * 
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;HDR=YES', 
    'SELECT * FROM [Sheet1$]')


3 Export data from Excel to existing SQL Server table
Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;HDR=YES', 
    'SELECT * FROM [SheetName$]')


4 If you dont want to create an EXCEL file in advance and want to export data to it, use

EXEC sp_makewebtask 
	@outputfile = 'd:\testing.xls', 
	@query = 'Select * from Database_name..SQLServerTable', 
	@colheaders =1, 
	@FixedFont=0,@lastupdated=0,@resultstitle='Testing details'
(Now you can find the file with data in tabular format)


5 To export data to new EXCEL file with heading(column names), create the following procedure

create procedure proc_generate_excel_with_columns
(
	@db_name	varchar(100),
	@table_name	varchar(100),	
	@file_name	varchar(100)
)
as

--Generate column names as a recordset
declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)
select 
	@columns=coalesce(@columns+',','')+column_name+' as '+column_name 
from 
	information_schema.columns
where 
	table_name=@table_name
select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')

--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''
exec(@sql)

--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''
exec(@sql)

--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
exec(@sql)

--Delete dummy file 
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
exec(@sql)

After creating the procedure, execute it by supplying database name, table name and file path

EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path'

Madhivanan

Failing to plan is Planning to fail

Edited by - madhivanan on 03/07/2009 03:11:41

SreenivasBora
Posting Yak Master

USA
164 Posts

Posted - 05/24/2005 :  14:38:39  Show Profile  Reply with Quote
Hi Madhavan,

SQL Server offers many ways to Import/Export the data into any hetrogeneous data sources.
Exporting the data into Excel can be done with BCP also.

bcp pubs.dbo.authors out c:\Testing.xls -c -S"TITANIC\LOCAL" -Usa -P""



With Regards
Sreenivas Reddy B
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 05/25/2005 :  01:06:42  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
bcp is usually used to export data to text file
Did you mean this?

Exec Master..xp_cmdshell 'bcp "Select * from myTable" queryout "C:\testing.xls" -c'

Madhivanan

Failing to plan is Planning to fail

Edited by - madhivanan on 05/25/2005 01:08:42
Go to Top of Page

dpdoug
Starting Member

USA
1 Posts

Posted - 05/26/2005 :  17:00:03  Show Profile  Reply with Quote
madhivanan,

I'm assuming that this code:

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

is exporting from a local SQLServer to an excel file on the same machine.

I have a web application where I need to do a backup of a table on a web server and dump the data into an excel file on the user's machine. How would I do that?
Go to Top of Page

smason99
Starting Member

USA
1 Posts

Posted - 06/30/2005 :  12:50:59  Show Profile  Reply with Quote
Perhaps I'm missing something but just for kicks I've copy the code below, changed the xls location, and the FROM table but I'm unsuccessful in getting this to work. It compains about the OLE DB provider. Any ideas?

TIA,
Shawn

quote:
Originally posted by CodeHorse

madhivanan,

I'm assuming that this code:

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

is exporting from a local SQLServer to an excel file on the same machine.

I have a web application where I need to do a backup of a table on a web server and dump the data into an excel file on the user's machine. How would I do that?



smason99
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 07/01/2005 :  02:02:58  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Which version of Excel are you using 2000 or 97?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

MBeaudreau
Starting Member

2 Posts

Posted - 07/06/2005 :  16:29:06  Show Profile  Reply with Quote
Do you know if in addition to identifying the excel sheet name you could state which cell to start at?

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




Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 07/07/2005 :  01:32:34  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Do you mean you want to select the required columns and not all from Excel?
If so, you can specify column names

SELECT col1,col2,..coln FROM [Sheet1$]

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

MBeaudreau
Starting Member

2 Posts

Posted - 07/07/2005 :  08:05:20  Show Profile  Reply with Quote
I need to start reading the excel data from a certain point in the excel file. All rows starting at cell B35. If need be I could read the entire excel file, not use headers, search for the value in B35 that I want to start at by using 'WHERE F1 like' but I'd prefer not to.

thanks.
M
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 07/11/2005 :  02:51:11  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
I want to start at by using 'WHERE F1 like' but I'd prefer not to.

Why?
Can you explain?
If you want to export particular record from excel then you need to query it by column name

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

kucaixx
Starting Member

3 Posts

Posted - 07/25/2005 :  23:01:06  Show Profile  Reply with Quote
I'm also get an error about OLE DB provider using excel 2000.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 07/26/2005 :  00:45:07  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Make sure you used correct sheet name in the query
Which version of Excel are you using 97 or 2000?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

beyonder422
Posting Yak Master

USA
124 Posts

Posted - 08/02/2005 :  11:36:59  Show Profile  Reply with Quote
can anyone add some specific sql/vbscript syntax to look at one specific cell?

haven't tested this yet, but think this link maybe it, just fyi for the forum.

http://groups-beta.google.com/group/microsoft.public.sqlserver.datamining/browse_thread/thread/4398b6d35d853290/2b229cd201e81de5?lnk=st&q=29C76785-22D9-46A2-A398-39393E666E76&rnum=1#2b229cd201e81de5
Go to Top of Page

beyonder422
Posting Yak Master

USA
124 Posts

Posted - 08/02/2005 :  11:37:13  Show Profile  Reply with Quote
can anyone add some specific sql/vbscript syntax to look at one specific cell?

haven't tested this yet, but think this link maybe it, just fyi for the forum.

http://groups-beta.google.com/group/microsoft.public.sqlserver.datamining/browse_thread/thread/4398b6d35d853290/2b229cd201e81de5?lnk=st&q=29C76785-22D9-46A2-A398-39393E666E76&rnum=1#2b229cd201e81de5
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 08/06/2005 :  03:06:46  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>can anyone add some specific sql/vbscript syntax to look at one specific cell?

If you mean specific column, then

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT ColumnName FROM [Sheet1$]')



Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

gomathi
Starting Member

1 Posts

Posted - 08/15/2005 :  11:02:57  Show Profile  Reply with Quote
Hi Madhivanan,

I am trying to use the foll query

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

but i am getting an error..i am using excel 2000...how should i modify the query...

Thanks
Gomathi
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 08/16/2005 :  00:44:53  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote

What is the error you got?

Make sure that the file testing.xls has the same columns as that of table
Otherwise explicitily specify the column names

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


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

sqlspider
Starting Member

16 Posts

Posted - 08/16/2005 :  11:15:35  Show Profile  Reply with Quote
Hi,

I am uploading XL spreadsheets into SQL DB for a long time now using the openrowset and select * from [sheet1$] method.

But becuase of some changes the spreadsheet name is not going to remain sheet1 but will be different each week like list08/12, 08/24 etc.

Is there anyway I could do the select statement from XL file based on the first sheet index without specifying the sheet name itself.

Thanks in advance!!

Manne
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 08/17/2005 :  00:56:28  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
I think you can use Front End like VB to read the sheetname of Excel file and pass it to the query. I am not sure whether this can be done in query itself

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

shahn
Starting Member

1 Posts

Posted - 08/19/2005 :  21:30:45  Show Profile  Reply with Quote
To select a range of cells use a named range as SELECT * FROM NamedRange or specify the cell range as SELECT * FROM [Sheet1$A1:B10]

Take a look at http://support.microsoft.com/kb/257819

Edited by - shahn on 08/19/2005 21:35:18
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 08/22/2005 :  00:48:14  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Thanks. Thats good Article


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
Page: of 30 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000