I’m trying to export data to .xlsx via T-SQL. I changed the script to .xlsx and the file is corrupted.
Is there a way directly export to .xlsx or use a .csv file and convert it to .xlsx via SQL script?
SET @BcpStatement = 'bcp "' + @SqlStatement + '" queryout "' + @ExportFileName + '" -t"," -c -T'
4
IF OBJECT_ID('TEMPDB..##TABELA') IS NOT NULL
DROP TABLE ##TABELA
CREATE TABLE dbo.##TABELA
(
ID INT
,NAME VARCHAR(100)
,CITY VARCHAR(100)
)
INSERT INTO ##TABELA
values
(1, 'NAME 01', 'CITY 01')
,(1, 'NAME 02', 'CITY 02')
,(1, 'NAME 03', 'CITY 03')
,(1, 'NAME 04', 'CITY 04')
DECLARE @SERVIDOR VARCHAR(50)
DECLARE @CAMINHO VARCHAR(500)
SET @SERVIDOR = (SELECT @@SERVERNAME)
SET @CAMINHO = 'D:TEMP'
exec
('XP_CMDSHELL ''BCP "SELECT * from ##TABELA" QUERYOUT '+@CAMINHO+'RELATORIO.CSV -c -t; -T -C RAW -S '+@SERVIDOR+'''')
1