sql-server-2008 - tabla - query to excel sql server

T-SQL: Exportar a un nuevo archivo de Excel

Question

Tengo un script que hace varias cosas y el resultado final es una gran tabla. Me preguntaba cómo podría exportar esta tabla final a un nuevo archivo de Excel (con encabezados de columna también).

Tendría que hacer esto dentro del script.

Answer 1

Esta es de lejos la mejor publicación para exportar a Excel desde SQL:

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

Para citar al usuario madhivanan ,

Además de usar DTS y el asistente de exportación, también podemos usar esta consulta para exportar datos de SQL Server2000 a Excel

Cree un archivo de Excel llamado testing que tenga los encabezados iguales a los de las columnas de la tabla y use estas consultas

1 Exportar datos al archivo EXCEL existente desde la tabla de SQL Server

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

2 Exportar datos de Excel a la nueva tabla de SQL Server

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

3 Exportar datos de Excel a la tabla de SQL Server existente (editado)

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

4 Si no desea crear un archivo EXCEL por adelantado y desea exportar datos a él, use

EXEC sp_makewebtask 
    @outputfile = ''d:/testing.xls'', 
    @query = ''Select * from Database_name..SQLServerTable'', 
    @colheaders =1, 
    @FixedFont=0,@lastupdated=0,@resultstitle=''Testing details''

(Ahora puede encontrar el archivo con datos en formato tabular)

5 Para exportar datos al nuevo archivo EXCEL con encabezado (nombres de columna), cree el siguiente procedimiento

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)

Después de crear el procedimiento, ejecútelo proporcionando el nombre de la base de datos, el nombre de la tabla y la ruta del archivo:

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

Es un susurro de 29 páginas, pero eso se debe a que otros muestran varias otras formas, así como a personas que hacen preguntas como esta sobre cómo hacerlo.

Siga ese hilo por completo y observe las diversas preguntas que las personas han formulado y cómo se resuelven. Recogí un poco de conocimiento simplemente rozando y he usado porciones de él para obtener los resultados esperados.

Para actualizar celdas individuales

Un miembro también allí Peter Larson publica lo siguiente: Creo que falta una cosa aquí. Es genial poder exportar e importar archivos a Excel, pero ¿qué hay de actualizar las celdas individuales? O un rango de celdas?

Este es el principio de cómo manejas eso

update OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', 
''Excel 8.0;Database=c:/test.xls;hdr=no'', 
''SELECT * FROM [Sheet1$b7:b7]'') set f1 = -99

También puede agregar fórmulas a Excel usando esto:

update OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', 
''Excel 8.0;Database=c:/test.xls;hdr=no'', 
''SELECT * FROM [Sheet1$b7:b7]'') set f1 = ''=a7+c7''

Exportación con nombres de columna usando T-SQL

El miembro Mladen Prajdic también tiene una entrada en el blog sobre cómo hacer esto here

Referencias: www.sqlteam.com (por cierto, este es un excelente blog / foro para cualquiera que busque sacar más provecho de SQL Server). Para hacer referencia a errores, utilicé this

Errores que pueden ocurrir

Si obtiene el siguiente error:

El proveedor OLE DB ''Microsoft.Jet.OLEDB.4.0'' no se puede usar para consultas distribuidas

Luego ejecuta esto:

sp_configure ''show advanced options'', 1;
GO
RECONFIGURE;
GO
sp_configure ''Ad Hoc Distributed Queries'', 1;
GO
RECONFIGURE;
GO

Answer 2

Me gustaría agregar un comentario adicional a lo que JonH hizo en el paso 5:

@columns=coalesce(@columns+'','','''')+column_name+'' as ''+''['' + column_name + '']''

agregar los corchetes permite incluir columnas con espacios en la salida.

Answer 3

Use PowerShell:

$Server = "TestServer"
$Database = "TestDatabase"
$Query = "select * from TestTable"
$FilePath = "C:/OutputFile.csv"

# This will overwrite the file if it already exists.
Invoke-Sqlcmd -Query $Query -Database $Database -ServerInstance $Server | Export-Csv $FilePath