Executar consultas SQL em arquivos do Excel

Embora as ações do Excel consigam resolvem a maioria dos cenários de automação do Excel, as consultas SQL podem recuperar e manipular volumes de dados significativos do Excel de maneira mais eficiente.

Suponhamos que um fluxo precise modificar somente os registros do Excel que contenham um determinado valor. Para obter essa funcionalidade sem consultas SQL, você precisa de loops, condicionais e várias ações do Excel.

Como alternativa, você pode implementar essa funcionalidade com consultas SQL usando somente duas ações, Abrir conexão SQL e Executar instruções SQL.

Abrir uma conexão SQL para um arquivo do Excel

Para executar uma consulta SQL, você precisa abrir uma conexão com o arquivo do Excel que deseja acessar.

Para estabelecer a conexão, crie uma nova variável chamada %Excel_File_Path% e a inicialize com o caminho do arquivo do Excel. Opcionalmente, você pode ignorar essa etapa e usar o caminho embutido em código do arquivo posteriormente no fluxo.

Agora, implante a ação Abrir conexão SQL e preencha a cadeia de conexão a seguir nas propriedades.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%Excel_File_Path%;Extended Properties="Excel 12.0 Xml;HDR=YES";

Observação

Para usar a cadeia de conexão com êxito, você deve baixar e instalar Mecanismo do Banco de Dados do Microsoft Access 2010 Redistribuível.

Abrir uma conexão SQL para um arquivo do Excel protegido por senha

Uma abordagem diferente é obrigatória em cenários nos quais você executa consultas SQL em arquivos do Excel protegidos por senha. Como a ação Abrir conexão SQL não consegue se conectar a arquivos do Excel protegidos por senha, você precisa remover a proteção.

Para isso, inicie o arquivo do Excel usando a ação Iniciar Excel. Como o arquivo é protegido por senha, digite a senha apropriada no campo Senha.

Em seguida, implante as ações de automação da interface do usuário apropriadas e navegue até Arquivo>Informações>Proteger Pasta de Trabalho>Criptografar com Senha. É possível encontrar mais informações sobre a automação da interface do usuário e como usar as respectivas ações em Automatizar aplicativos de área de trabalho.

Depois de selecionar Criptografar com Senha, preencha uma cadeia de caracteres vazia na caixa de diálogo pop-up usando a ação Preencher campo de texto na janela. Para popular uma cadeia de caracteres vazia, use a seguinte expressão: %""%.

Para pressionar o botão OK na caixa de diálogo e aplicar as alterações, implante a ação Pressionar botão na janela.

Por fim, implante a ação Fechar Excel para salvar a pasta de trabalho não protegida como um novo arquivo do Excel.

Depois de salvar o arquivo, siga as instruções em Abrir uma conexão SQL com um arquivo do Excel para abrir uma conexão com ele.

Quando a manipulação do arquivo do Excel for concluída, use a ação Excluir arquivos para excluir a cópia não protegida do arquivo do Excel.

Ler o conteúdo de uma planilha do Excel

Embora a ação Ler a partir da planilha do Excel possa ler o conteúdo de uma planilha do Excel, os loops podem demorar um tempo significativo para percorrer os dados recuperados.

Uma maneira mais eficiente de recuperar valores específicos das planilhas é tratando os arquivos do Excel como bancos de dados e executar consultas SQL neles. Essa abordagem é mais rápida e aumenta o desempenho do fluxo.

Para recuperar todo o conteúdo de uma planilha, é possível usar a consulta SQL a seguir na ação Executar instruções SQL.

SELECT * FROM [SHEET$]

Observação

Para aplicar essa consulta SQL aos fluxos, substitua o espaço reservado SHEET pelo nome da planilha que você deseja acessar.

Para recuperar as linhas que contenham um determinado valor em uma coluna específica, use a seguinte consulta SQL:

SELECT * FROM [SHEET$] WHERE [COLUMN NAME] = 'VALUE'

Observação

Para aplicar essa consulta SQL nos fluxos, substitua:

• SHEET pelo nome da planilha que você deseja acessar.
• COLUMN NAME pela coluna que contém o valor que você deseja encontrar. As colunas na primeira linha da planilha do Excel são identificadas como os nomes das colunas da tabela.
• VALUE pelo valor que você deseja encontrar.

Excluir dados de uma linha do Excel

Embora o Excel não dê suporte à consulta SQL DELETE, é possível usar a consulta UPDATE para definir todas as células de uma linha específica como nulas.

Mais precisamente, é possível usar a seguinte consulta SQL:

UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE'

Ao desenvolver o fluxo, você precisa substituir o espaço reservado SHEET pelo nome da planilha que deseja acessar.

Os espaços reservados COLUMN1 e COLUMN2 representam os nomes de todas as colunas a serem tratadas. Este exemplo tem duas colunas, mas, em um cenário real, o número de colunas pode ser diferente. As colunas na primeira linha da planilha do Excel são identificadas como os nomes das colunas da tabela.

A parte [COLUMN1]='VALUE' da consulta define a linha que você deseja atualizar. No fluxo, use o nome da coluna e o valor com base na combinação que descreve as linhas com exclusividade.

Recuperar dados do Excel, exceto de uma linha específica

Em alguns cenários, talvez você precise recuperar todo o conteúdo de uma planilha do Excel, exceto de uma linha específica.

Uma maneira prática de conseguir esse resultado é definindo os valores da linha indesejada como nulos e, em seguida, recuperando todos os valores, exceto os nulos.

Para alterar os valores de uma linha específica da planilha, é possível usar uma consulta SQL UPDATE, conforme apresentado em Excluir dados de uma linha do Excel:

UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE'

Em seguida, execute a seguinte consulta SQL para recuperar todas as linhas da planilha que não contenham valores nulos:

SELECT * FROM [SHEET$] WHERE [COLUMN1] IS NOT NULL OR [COLUMN2] IS NOT NULL

Os espaços reservados COLUMN1 e COLUMN2 representam os nomes de todas as colunas a serem tratadas. Este exemplo tem duas colunas, mas, em uma tabela real, o número de colunas pode ser diferente. Todas as colunas na primeira linha da planilha do Excel são identificadas como os nomes das colunas da tabela.