Notas .NET

Thursday, March 05, 2009

Exportando tablas de SQL a Excel y viceversa

1. De una tabla SQL a un Excel ya existente:
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;', 'SELECT * FROM [SheetName$]') select * from SQLServerTable


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


3. Desde Excel a una tabla de SQL ya existente:
Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;HDR=YES', 'SELECT * FROM [SheetName$]')


4. Creando la tabla en un Excel a partir de una tabla de SQL:
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. Creando el Excel con columnas, a partir de una tabla SQL.
Primero se crea el siguiente Stored 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)


Para ejecutar el SP:
EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path'


6. En algunas ocasiones nos dirá que algunos permisos no están configurados. Uno de ellos es "Web Assistant Procedures" y para activarlo debemos ejecutar los siguientes comandos:
sp_configure
sp_configure 'show advanced options', 1
reconfigure
sp_configure 'Web Assistant Procedures', 1
reconfigure

0 Comments:

Post a Comment

<< Home