Como DBAs/Desenvolvedores do SQL Server, frequentemente somos solicitados a importar dados de planilhas do Excel para tabelas de banco de dados. Existem várias maneiras diferentes de fazer isso:

• SSIS: use a tarefa de fluxo de dados e escolha o Excel como fonte de dados
• Consultas ad hoc: use OPENROWSET/OPENDATASOURCE ou servidor vinculado com o provedor Microsoft Jet OLEDB
• Métodos COM do Excel: Use procedimentos armazenados da família sp_OA para chamar os métodos COM do Excel
• Carga em massa: Salve as planilhas do Excel como arquivos CSV e use BCP ou BULK INSERT

No entanto, às vezes ainda enfrentamos desafios:

• Não há um provedor Microsoft OLE DB de 64 bits para Jet. O provedor é necessário para servidor vinculado ou OPENROWSET/OPENDATASOURCE etc para importar planilhas do Excel, tabelas do Access, arquivos CSV etc.
• Às vezes, precisamos lidar com planilhas dinâmicas, ou seja, não conhecemos o número de colunas e seus nomes com antecedência, o que não é fácil de lidar no SSIS ou em consultas ad hoc
• Às vezes, a política de segurança não nos permite usar procedimentos armazenados da família sp_OA ou consultas ad hoc OPENROWSET/OPENDATASOURCE. Eles são desativados pelos administradores.

Este artigo discute uma maneira de importar planilhas do Excel usando o T-SQL nativo do SQL Server 2005 ou posterior, para que funcione bem em plataformas de 32 bits e 64 bits.

Visão Geral

Desde 2003, o MS Office suporta planilhas XML, ou seja, uma pasta de trabalho do Excel pode ser salva como um documento XML. A planilha XML possui “Excel.Sheet” como instrução de processamento, então, quando você clica duas vezes nela, ela será aberta no Excel em vez do Internet Explorer ou outro processador XML.

A ideia básica é salvar as planilhas do Excel como planilhas XML. Use o provedor de conjunto de linhas em massa do OPENROWSET para carregar o arquivo XML no SQL Server; Processar o XML carregado para analisar o cabeçalho e os valores de cada célula; Pivotar as células e gerar a tabela de dados;

Procedimento Armazenado de Importação do Excel

O procedimento armazenado dbo.uspImportExcelSheet pode ser usado para importar planilhas XML do Excel em vários cenários.

1) Importar de uma planilha e retornar todas as colunas em um conjunto de resultados

EXEC dbo.[uspImportExcelSheet]
       @ExcelFileName=N'C:\TestBook.xml',
       @WorkSheetName=N'Sheet1',
       @FirstRowIsHeader=1

2) Importar de uma planilha e salvar todas as colunas em uma tabela especificada

IF object_id('tempdb..##Data') IS NOT NULL
     DROP TABLE ##Data;
EXEC dbo.[uspImportExcelSheet]
    @ExcelFileName=N'C:\TestBook.xml',
    @WorkSheetName=N'Sheet1',
    @OutputTableName=N'##Data',
    @FirstRowIsHeader=1,
    @IdentityColumn=N'RecordID'
SELECT * FROM ##Data
IF object_id('tempdb..##Data') IS NOT NULL
      DROP TABLE ##Data;

3) Importar uma planilha com todas as colunas e salvar os dados em uma tabela pré-criada

IF object_id('tempdb..#Data') IS NOT NULL
      DROP TABLE #Data;
CREATE TABLE #Data (
            TextCol nvarchar(30) NULL,
            NumCol nvarchar(50) NULL,
            DateCol nvarchar(50) NULL,
            Text2Col nvarchar(30) NULL
      )
EXEC dbo.[uspImportExcelSheet]
      @ExcelFileName=N'C:\TestBook.xml',
      @WorkSheetName=N'Sheet1',
      @OutputTableName=N'#Data',
      @FirstRowIsHeader=0
SELECT * FROM #Data
IF object_id('tempdb..#Data') IS NOT NULL
       DROP TABLE #Data;

4) Importar uma planilha com colunas especificadas em uma tabela pré-criada

IF object_id('tempdb..#Data') IS NOT NULL
      DROP TABLE #Data;
CREATE TABLE #Data (
            DateCol nvarchar(50) NULL,
            Text2Col nvarchar(30) NULL
      )
EXEC dbo.[uspImportExcelSheet]
      @ExcelFileName=N'C:\TestBook.xml',
      @WorkSheetName=N'Sheet1',
      @OutputTableName=N'#Data',
      @FirstRowIsHeader=1,
      @FirstRowToImportData=3,
      @RowsToImport=2
SELECT * FROM #Data
IF object_id('tempdb..#Data') IS NOT NULL
        DROP TABLE #Data;

5) Importar uma planilha com todas as colunas e salvar os resultados retornados em uma variável de tabela

DECLARE @Data TABLE (
            RowNumber int not null,
            TextCol nvarchar(30) NULL,
            NumCol nvarchar(50) NULL,
            DateCol nvarchar(50) NULL,
            Text2Col nvarchar(30) NULL
      )
INSERT @Data (RowNumber,TextCol,NumCol,DateCol,Text2Col)
EXEC dbo.[uspImportExcelSheet]
      @ExcelFileName=N'C:\TestBook.xml',
      @WorkSheetName=N'Sheet1',
      @FirstRowIsHeader=1,
      @RowsToCheckDataType=0
SELECT * FROM @Data