Converter arquivos CSV em pastas de trabalho do Excel

Artigo
02/07/2024

Muitos serviços exportam dados como arquivos CSV (valor separado por vírgula). Essa solução automatiza o processo de conversão desses arquivos CSV em pastas de trabalho do Excel no formato de arquivo .xlsx. Ele usa um fluxo do Power Automate para encontrar arquivos com a extensão .csv em uma pasta do OneDrive e um Script do Office para copiar os dados do arquivo .csv em uma nova pasta de trabalho do Excel.

Solução

Armazene os arquivos .csv e um arquivo "Modelo" em branco .xlsx em uma pasta do OneDrive.
Crie um Script do Office para analisar os dados CSV em um intervalo.
Crie um fluxo do Power Automate para ler os arquivos .csv e passar seu conteúdo para o script.

Arquivos de exemplo

Baixe convert-csv-example.zip para obter o arquivo Template.xlsx e dois arquivos .csv de exemplo. Extraia os arquivos em uma pasta no OneDrive. Este exemplo pressupõe que a pasta seja chamada de "saída".

Adicione o script a seguir à pasta de trabalho de exemplo. No Excel, use Automatizar>Novo Script para colar o código e salvar o script. Salve-o como Converter CSV e experimente o exemplo por conta própria!

Código de exemplo: inserir valores separados por vírgula em uma pasta de trabalho

/**
 * Convert incoming CSV data into a range and add it to the workbook.
 */
function main(workbook: ExcelScript.Workbook, csv: string) {
  let sheet = workbook.getWorksheet("Sheet1");

  // Remove any Windows \r characters.
  csv = csv.replace(/\r/g, "");

  // Split each line into a row.
  // NOTE: This will split values that contain new line characters.
  let rows = csv.split("\n");

  /*
   * For each row, match the comma-separated sections.
   * For more information on how to use regular expressions to parse CSV files,
   * see this Stack Overflow post: https://stackoverflow.com/a/48806378/9227753
   */
  const csvMatchRegex = /(?:,|\n|^)("(?:(?:"")*[^"]*)*"|[^",\n]*|(?:\n|$))/g
  rows.forEach((value, index) => {
    if (value.length > 0) {
      let row = value.match(csvMatchRegex);
    
      // Check for blanks at the start of the row.
      if (row[0].charAt(0) === ',') {
        row.unshift("");
      }
  
      // Remove the preceding comma and surrounding quotation marks.
      row.forEach((cell, index) => {
        cell = cell.indexOf(",") === 0 ? cell.substring(1) : cell;
        row[index] = cell.indexOf("\"") === 0 && cell.lastIndexOf("\"") === cell.length - 1 ? cell.substring(1, cell.length - 1) : cell;
      });
    
      // Create a 2D array with one row.
      let data: string[][] = [];
      data.push(row);
  
      // Put the data in the worksheet.
      let range = sheet.getRangeByIndexes(index, 0, 1, data[0].length);
      range.setValues(data);
    }
  });

  // Add any formatting or table creation that you want.
}

Power Automate flow: criar novos arquivos de .xlsx

Entre no Power Automate e crie um novo fluxo de nuvem agendado.
Defina o fluxo como Repetir cada "1" "Dia" e selecione Criar.
Obtenha o arquivo do Excel de modelo. Essa é a base para todos os arquivos .csv convertidos. No construtor de fluxos, selecione o + botão e Adicione uma ação. Selecione a ação Obter conteúdo de arquivo do conector OneDrive for Business. Forneça o caminho do arquivo para o arquivo "Template.xlsx".
- Arquivo: /output/Template.xlsx
Renomeie a etapa Obter conteúdo do arquivo . Selecione o título atual, "Obter conteúdo do arquivo", no painel de tarefas de ação. Altere o nome para "Obter modelo do Excel".
Adicione uma ação que obtém todos os arquivos na pasta "saída". Escolha os arquivos lista do conector OneDrive for Business na ação de pasta. Forneça o caminho da pasta que contém os arquivos .csv.
- Pasta: /output
Adicione uma condição para que o fluxo funcione apenas em arquivos .csv. Adicione a ação de controle condição . Use os valores a seguir para a Condição.
- Escolha um valor: Nome (conteúdo dinâmico de Listar arquivos na pasta). Observe que esse conteúdo dinâmico tem vários resultados, portanto, um para cada controle envolve a Condição.
- termina com (da lista suspensa)
- Escolha um valor: .csv
O restante do fluxo está na seção Se sim , já que só queremos agir em .csv arquivos. Obtenha um arquivo de .csv individual adicionando uma ação que usa a ação Obter conteúdo do arquivo do conector OneDrive for Business. Use a ID do conteúdo dinâmico de Listar arquivos na pasta.
- Arquivo: Id (conteúdo dinâmico dos arquivos list na etapa de pasta )
Renomeie a nova etapa Obter conteúdo do arquivo para "Obter .csv arquivo". Isso ajuda a distinguir esse arquivo do modelo do Excel.
Faça o novo arquivo .xlsx, usando o modelo do Excel como o conteúdo base. Adicione uma ação que usa a ação Criar arquivo do conector OneDrive for Business. Use os seguintes valores.
- Caminho da pasta: /output
- Nome do arquivo: nome sem extensão.xlsx (escolha o Nome sem conteúdo dinâmico de extensão nos arquivos List na pasta e digite manualmente ".xlsx" após ele)
- Conteúdo do arquivo: conteúdo do arquivo (conteúdo dinâmico do modelo Get Excel)
Execute o script para copiar dados na nova pasta de trabalho. Adicione a ação executar script do conector do Excel Online (Business). Use os valores a seguir para a ação.
- Localização: OneDrive for Business
- Biblioteca de Documentos: OneDrive
- Arquivo: Id (conteúdo dinâmico de Criar arquivo)
- Script: Converter CSV
- csv: Conteúdo do arquivo (conteúdo dinâmico do arquivo Obter .csv)
Salve o fluxo. O designer de fluxo deve se parecer com a imagem a seguir.
Use o botão Testar na página do editor de fluxo ou execute o fluxo por meio da guia Meus fluxos . Certifique-se de permitir o acesso quando solicitado.
Você deve encontrar novos arquivos .xlsx na pasta "saída", juntamente com os arquivos .csv originais. As novas pastas de trabalho contêm os mesmos dados que os arquivos CSV.

Solução de problemas

Teste de script

Para testar o script sem usar o Power Automate, atribua um valor a csv antes de usá-lo. Adicione o código a seguir como a primeira linha da main função e selecione Executar.

  csv = `1, 2, 3
         4, 5, 6
         7, 8, 9`;

Arquivos separados por ponto e vírgula e outros separadores alternativos

Algumas regiões usam ponto-e-vírgula (';') para separar valores de células em vez de vírgulas. Nesse caso, você precisa alterar as linhas a seguir no script.

Substitua as vírgulas por ponto-e-vírgula na instrução de expressão regular. Isso começa com let row = value.match.
```
let row = value.match(/(?:;|\n|^)("(?:(?:"")*[^"]*)*"|[^";\n]*|(?:\n|$))/g);
```
Substitua a vírgula por um ponto e vírgula no marcar para a primeira célula em branco. Isso começa com if (row[0].charAt(0).
```
if (row[0].charAt(0) === ';') {
```
Substitua a vírgula por um ponto e vírgula na linha que remove o caractere de separação do texto exibido. Isso começa com row[index] = cell.indexOf.
```
   row[index] = cell.indexOf(";") === 0 ? cell.substr(1) : cell;
```

Observação

Se o arquivo usar guias ou qualquer outro caractere para separar os valores, substitua o ; nas substituições acima por \t ou qualquer caractere que esteja sendo usado.

Arquivos CSV grandes

Se o arquivo tiver centenas de milhares de células, você poderá alcançar o limite de transferência de dados do Excel. Você precisará forçar o script a sincronizar com o Excel periodicamente. A maneira mais fácil de fazer isso é chamar console.log depois que um lote de linhas tiver sido processado. Adicione as seguintes linhas de código para fazer isso acontecer.

Antes rows.forEach((value, index) => {de , adicione a linha a seguir.
```
  let rowCount = 0;
```
Depois range.setValues(data);de , adicione o código a seguir. Observe que, dependendo do número de colunas, talvez seja necessário reduzir 5000 para um número menor.
```
  rowCount++;
  if (rowCount % 5000 === 0) {
    console.log("Syncing 5000 rows.");
  }
```

Aviso

Se o arquivo CSV for muito grande, você poderá ter problemas de tempo limite no Power Automate. Você precisará dividir os dados CSV em vários arquivos antes de convertê-los em pastas de trabalho do Excel.

Ênfases e outros caracteres unicode

Arquivos com caracteres específicos de unicódigo, como vogais acentuadas como é, precisam ser salvos com a codificação correta. A criação de arquivos do conector do OneDrive do Power Automate é padrão para ANSI para arquivos .csv. Se você estiver criando os arquivos .csv no Power Automate, precisará adicionar a marca de pedido de byte (BOM) antes dos valores separados por vírgulas. Para UTF-8, substitua o conteúdo do arquivo para a operação de arquivo de gravação .csv pela expressão concat(uriComponentToString('%EF%BB%BF'), <CSV Input>) (onde <CSV Input> estão os dados CSV originais).

Observe que esse exemplo não cria os arquivos .csv no fluxo, portanto, essa alteração precisa acontecer na parte personalizada do fluxo. Você também pode ler e reescrever os arquivos .csv com o BOM, se não controlar como esses arquivos são criados.

Aspas ao redor

Este exemplo remove todas as aspas ("") que cercam valores. Normalmente, eles são adicionados a valores separados por vírgulas para evitar que vírgulas nos dados sejam tratadas como tokens de separação. Um arquivo .csv aberto no Excel, salvo como um arquivo .xlsx, nunca terá as aspas mostradas ao leitor. Se desejar manter as aspas e exibi-las nas planilhas finais, substitua as linhas 27-30 do script pelo código a seguir.

// Remove the preceding comma.
row.forEach((cell, index) => {
  row[index] = cell.indexOf(",") === 0 ? cell.substring(1) : cell;
});

Partilhar via