T-SQL : Exporter vers un nouveau fichier Excel

Question

J'ai un script qui fait diverses choses et le résultat final est un grand tableau. Je me demandais comment je pouvais exporter ce tableau final vers un nouveau fichier Excel (avec les en-têtes de colonne également).

J'aurais besoin de faire cela dans le script.

Answer 1

C'est de loin le meilleur article sur l'exportation vers Excel à partir de SQL :

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

Pour citer l'utilisateur madhivanan ,

En dehors de l'utilisation du DTS et de l'assistant d'exportation, nous pouvons également utiliser cette requête pour exporter des données de SQL Server2000 vers Excel.

Créez un fichier Excel nommé testing dont les en-têtes sont identiques à ceux des colonnes du tableau et utilisez les requêtes suivantes

1 Exportation de données vers un fichier EXCEL existant à partir d'une table SQL Server

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

2 Exportation des données d'Excel vers une nouvelle table 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 Exportation de données d'Excel vers une table SQL Server existante (éditée)

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 vous ne voulez pas créer un fichier EXCEL à l'avance et que vous souhaitez y exporter des données, utilisez la fonction

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

(Maintenant vous pouvez trouver le fichier avec les données en format tabulaire)

5 Pour exporter des données vers un nouveau fichier EXCEL avec un intitulé (noms de colonnes), créez la procédure suivante

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)

Après avoir créé la procédure, exécutez-la en fournissant le nom de la base de données, le nom de la table et le chemin du fichier :

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

Il y a 29 pages, mais c'est parce que d'autres montrent d'autres méthodes et que des personnes posent des questions comme celle-ci sur la façon de procéder.

Suivez entièrement ce fil de discussion et regardez les différentes questions que les gens ont posées et comment elles ont été résolues. J'ai acquis pas mal de connaissances en le parcourant et j'en ai utilisé certaines parties pour obtenir les résultats escomptés.

Pour mettre à jour les cellules individuelles

Un membre également présent, Peter Larson, affiche ce qui suit : Je pense qu'il manque une chose ici. C'est génial de pouvoir exporter et importer vers des fichiers Excel, mais comment mettre à jour des cellules individuelles ? Ou une plage de cellules ?

C'est le principe de la façon dont vous gérez cela.

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

Vous pouvez également ajouter des formules à Excel en utilisant cette méthode :

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'

Exportation avec des noms de colonnes en utilisant T-SQL

Le député Mladen Prajdic a également publié un article de blog sur la manière de procéder. aquí

Références : www.sqlteam.com (entre-temps, il s'agit d'un excellent blog / forum pour tous ceux qui cherchent à tirer le meilleur parti de SQL Server). Pour le référencement des erreurs, j'ai utilisé este

Erreurs qui peuvent se produire

Si vous obtenez l'erreur suivante :

Le fournisseur OLE DB 'Microsoft.Jet.OLEDB.4.0' ne peut pas être utilisé pour des requêtes distribuées

Alors exécutez ceci :

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

Answer 2

Utilisez 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

Dans mes cas habituels, tout ce dont j'ai vraiment besoin est un fichier CSV qui peut être lu par Excel. Toutefois, si vous avez besoin d'un véritable fichier Excel, ajoutez du code pour convertir le fichier CSV en fichier Excel. Cette réponse donne une solution pour cela, mais je ne l'ai pas testée.