0
Hello, I am creating a SP to export data from a spreadsheet in xlsx format (Excel ), however I am not succeeding. My SQL SERVER is an older version of SQL Server 2000 (Build Version 8.00.2187), and I am not an administrator.
ALTER PROCEDURE [citdifo].[spImportaTempoDeAnalise]
@caminhoArquivo VARCHAR(300)
AS
DECLARE @tabelaTmpTempoDeAnalise VARCHAR(100), @consultaSQL VARCHAR(5000)
PRINT @caminhoArquivo
SET @tabelaTmpTempoDeAnalise = 'tbTmpTempoDeAnalise' + CONVERT(VARCHAR(10), @@SPID)
PRINT @tabelaTmpTempoDeAnalise
IF OBJECT_ID(@tabelaTmpTempoDeAnalise) IS NULL BEGIN
SET @consultaSQL = 'CREATE TABLE ' + @tabelaTmpTempoDeAnalise + ' (
Matrícula VARCHAR(70) NULL,
Conta VARCHAR(70) NULL,
Tipo VARCHAR(70) NULL,
Status VARCHAR(15) NULL,
Horário VARCHAR(50) NULL
)'
PRINT 'AQUI1'
END ELSE BEGIN
SET @consultaSQL = 'TRUNCATE TABLE ' + @tabelaTmpTempoDeAnalise
PRINT 'AQUI2'
END
PRINT @consultaSQL
EXEC(@consultaSQL)
SET @consultaSQL = 'BULK INSERT ' + @tabelaTmpTempoDeAnalise + ' FROM ''' + @caminhoArquivo + ''' WITH (FIRSTROW = 1, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'', FORMAT=''xlsx'')'
--SET consultaSQL = SELECT * FROM OPENROWSET('Microsoft.Jet.OleDB.4.0', 'EXCEL 13.0;Database=C:\Tempo de Análise - C024880 - 01-02-2019.xlsx ', [Data$]);
PRINT @consultaSQL
EXEC(@consultaSQL)`
The error shown when executing the AS is:
BULK INSERT tbTmpTempoDeAnalise59 FROM 'C:\Users\c108949\Downloads\qualidade\Tempo de Análise - C024880 - 01-02-2019.xlsx' WITH (FIRSTROW = 1, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FORMAT='xlsx')
Message 170, Level 15, Status 1, Line 3 Line 1: Incorrect syntax near 'FORMAT'. I have tried without passing the Format parameter and failed.
I’ve read some procedures that could work, but I’m still not succeeding. I’d like to know if anyone’s been through this and could guide me. Thank you.
Export an Excel CSV to make life easier.
– Bacco
FORMAT does not exist in the SQL Server version in use and XLSX files require Microsoft.ACE.OLEDB.12. 0. I also suggest that, (1) in Excel, export the worksheet data as CSV file and (2) in SQL Server import data from CSV file into table.
– José Diz
See the article "Bulk data import", available at https://portosql.wordpress.com/blog/
– José Diz