Converter ficheiros CSV em livros do Excel

Muitos serviços exportam dados como ficheiros de valores separados por vírgulas (CSV). Esta solução automatiza o processo de conversão desses ficheiros CSV em livros do Excel no formato de ficheiro .xlsx. Utiliza um fluxo do Power Automate para localizar ficheiros com a extensão .csv numa pasta do OneDrive e um Script do Office para copiar os dados do ficheiro .csv para um novo livro do Excel.

Solução

1. Armazene os ficheiros .csv e um ficheiro de .xlsx "Modelo" em branco numa pasta do OneDrive.
2. Crie um Script do Office para analisar os dados CSV num intervalo.
3. Crie um fluxo do Power Automate para ler os ficheiros .csv e transmitir os respetivos conteúdos para o script.

Ficheiros de exemplo

Transfiraconvert-csv-example.zip para obter o ficheiro de Template.xlsx e dois ficheiros de .csv de exemplo. Extraia os ficheiros para uma pasta no seu OneDrive. Este exemplo pressupõe que a pasta tem o nome "output".

Adicione o seguinte script ao livro de exemplo. No Excel, utilize Automatizar a Criação> deNovo Script>no Editor de Código para colar o código e guardar o script. Guarde-o como Converter CSV e experimente o exemplo!

Código de exemplo: inserir valores separados por vírgulas num livro

/**
 * 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.
}

Fluxo do Power Automate: Criar novos ficheiros de .xlsx

1. Inicie sessão no Power Automate e crie um novo fluxo de cloud agendada.

2. Defina o fluxo como Repetir a cada "1" "Dia" e selecione Criar.

3. Obtenha o ficheiro do Excel de modelo. Esta é a base para todos os ficheiros .csv convertidos. No construtor de fluxos, selecione o + botão e Adicionar uma ação. Selecione a ação Obter conteúdo do ficheiro do conector OneDrive for Business. Indique o caminho do ficheiro para o ficheiro "Template.xlsx".

   • Ficheiro: /output/Template.xlsx

4. Mude o nome do passo Obter conteúdo do ficheiro . Selecione o título atual, "Obter conteúdo do ficheiro", no painel de tarefas de ação. Altere o nome para "Obter modelo do Excel".

   

5. Adicione uma ação que obtém todos os ficheiros na pasta "output". Selecione os ficheiros lista do conector OneDrive for Business na ação de pasta. Indique o caminho da pasta que contém os ficheiros .csv.

   • Pasta: /output

   

6. Adicione uma condição para que o fluxo funcione apenas em ficheiros .csv. Adicione a ação de controlo Condição . Utilize os seguintes valores para a Condição.

   • Escolha um valor: Nome (conteúdo dinâmico de Listar ficheiros na pasta). Tenha em atenção que este conteúdo dinâmico tem vários resultados, pelo que um para cada controlo rodeia a Condição.
   • termina com (na lista pendente)
   • Escolha um valor: .csv

   

7. O resto do fluxo encontra-se na secção Se sim , uma vez que só queremos agir .csv ficheiros. Obtenha um ficheiro de .csv individual ao adicionar uma ação que utiliza a ação Obter conteúdo de ficheiro do conector do OneDrive for Business. Utilize o ID do conteúdo dinâmico de Listar ficheiros na pasta.

   • Ficheiro: ID (conteúdo dinâmico do passo Listar ficheiros na pasta )

8. Mude o nome do novo passo Obter conteúdo de ficheiro para "Obter .csv ficheiro". Isto ajuda a distinguir este ficheiro do modelo do Excel.

   

9. Crie o novo ficheiro .xlsx, utilizando o modelo do Excel como conteúdo base. Adicione uma ação que utiliza a ação Criar ficheiro do conector OneDrive for Business. Use os seguintes valores.

   • Caminho da Pasta: /output
   • Nome do Ficheiro: nome sem extensão.xlsx (selecione o Nome sem conteúdo dinâmico da extensãona pasta Listar ficheiros e escreva manualmente ".xlsx" após a mesma)
   • Conteúdo do Ficheiro: Conteúdo do ficheiro (conteúdo dinâmico do modelo Obter Excel)

   

10. Execute o script para copiar dados para o novo livro. Adicione a ação Executar script do conector do Excel Online (Empresas). Utilize os seguintes valores para a ação.

    • Localização: OneDrive for Business
    • Biblioteca de Documentos: OneDrive
    • Ficheiro: ID (conteúdo dinâmico de Criar ficheiro)
    • Script: Converter CSV
    • csv: Conteúdo do ficheiro (conteúdo dinâmico do ficheiro Obter .csv)

    

11. Guarde o fluxo. O estruturador de fluxo deve ter um aspeto semelhante à imagem seguinte.

    

12. Utilize o botão Testar na página do editor de fluxos ou execute o fluxo através do separador Os meus fluxos . Certifique-se de que permite o acesso quando lhe for pedido.

13. Deverá encontrar novos ficheiros .xlsx na pasta "output", juntamente com os ficheiros de .csv originais. Os novos livros contêm os mesmos dados que os ficheiros CSV.

Solução de problemas

Teste de scripts

Para testar o script sem utilizar o Power Automate, atribua um valor a csv antes de o utilizar. Adicione o seguinte código como a primeira linha da main função e selecione Executar.

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

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

Algumas regiões utilizam pontos e vírgulas (';') para separar valores de células em vez de vírgulas. Neste caso, tem de alterar as seguintes linhas no script.

1. Substitua as vírgulas por ponto e vírgula na instrução de expressão regular. Isto começa com let row = value.match.

   let row = value.match(/(?:;|\n|^)("(?:(?:"")*[^"]*)*"|[^";\n]*|(?:\n|$))/g);
   

2. Substitua a vírgula por um ponto e vírgula na marcar da primeira célula em branco. Isto começa com if (row[0].charAt(0).

   if (row[0].charAt(0) === ';') {
   

3. Substitua a vírgula por um ponto e vírgula na linha que remove o caráter de separação do texto apresentado. Isto começa com row[index] = cell.indexOf.

      row[index] = cell.indexOf(";") === 0 ? cell.substr(1) : cell;
   

Ficheiros CSV grandes

Se o seu ficheiro tiver centenas de milhares de células, poderá atingir o limite de transferência de dados do Excel. Terá de forçar o script a sincronizar com o Excel periodicamente. A forma mais fácil de o fazer é chamar console.log depois de um lote de linhas ter sido processado. Adicione as seguintes linhas de código para que isto aconteça.

1. Antes rows.forEach((value, index) => {de , adicione a seguinte linha.

     let rowCount = 0;
   

2. Depois de range.setValues(data);, adicione o seguinte código. Tenha em atenção que, dependendo do número de colunas, poderá ter de reduzir 5000 para um número mais baixo.

     rowCount++;
     if (rowCount % 5000 === 0) {
       console.log("Syncing 5000 rows.");
     }
   

Destaques e outros carateres unicode

Os ficheiros com carateres específicos de unicode, como vogais acentuadas como é, têm de ser guardados com a codificação correta. A criação de ficheiros do conector do OneDrive do Power Automate é predefinida para ANSI para .csv ficheiros. Se estiver a criar os ficheiros .csv no Power Automate, terá de adicionar a marca de ordem de bytes (BOM) antes dos valores separados por vírgulas. Para UTF-8, substitua o conteúdo do ficheiro para a operação de escrita .csv ficheiro pela expressão concat(uriComponentToString('%EF%BB%BF'), <CSV Input>) (onde <CSV Input> estão os dados CSV originais).

Tenha em atenção que este exemplo não cria os ficheiros .csv no fluxo, pelo que esta alteração tem de ocorrer na sua parte personalizada do fluxo. Também pode ler e reescrever os ficheiros .csv com o BOM, se não controlar a forma como esses ficheiros são criados.

Aspas adjacentes

Este exemplo remove as aspas ("") que rodeiam os valores. Normalmente, estes valores são adicionados a valores separados por vírgulas para impedir que vírgulas nos dados sejam tratadas como tokens de separação. Um ficheiro .csv aberto no Excel, depois guardado como um ficheiro .xlsx, nunca terá as aspas apresentadas ao leitor. Se quiser manter as aspas e apresentá-las nas folhas de cálculo finais, substitua as linhas 27 a 30 do script pelo seguinte código.

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