Import data from xlsx file to SQL Server

Asked

Viewed 311 times

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.

  • 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.

  • See the article "Bulk data import", available at https://portosql.wordpress.com/blog/

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.