Konvertieren von CSV-Dateien in Excel-Arbeitsmappen

  • Artikel

Viele Dienste exportieren Daten als CSV-Dateien (Comma-Separated Value). Diese Lösung automatisiert den Prozess der Konvertierung dieser CSV-Dateien in Excel-Arbeitsmappen im .xlsx Dateiformat. Es verwendet einen Power Automate-Flow , um Dateien mit der .csv-Erweiterung in einem OneDrive-Ordner und einem Office-Skript zu suchen, um die Daten aus der .csv-Datei in eine neue Excel-Arbeitsmappe zu kopieren.

Lösung

  1. Speichern Sie die .csv-Dateien und eine leere "Vorlage"-.xlsx datei in einem OneDrive-Ordner.
  2. Erstellen Sie ein Office-Skript, um die CSV-Daten in einem Bereich zu analysieren.
  3. Erstellen Sie einen Power Automate-Flow, um die .csv Dateien zu lesen und deren Inhalt an das Skript zu übergeben.

Beispieldateien

Laden Sie convert-csv-example.zip herunter, um die Template.xlsx-Datei und zwei Beispiel-.csv-Dateien abzurufen. Extrahieren Sie die Dateien in einen Ordner auf Ihrem OneDrive. In diesem Beispiel wird davon ausgegangen, dass der Ordner "output" heißt.

Fügen Sie der Beispielarbeitsmappe das folgende Skript hinzu. Verwenden Sie in Excel Dasneue Skriptautomatisieren>, um den Code einzufügen und das Skript zu speichern. Speichern Sie es als CSV konvertieren , und probieren Sie das Beispiel selbst aus!

Beispielcode: Einfügen von durch Trennzeichen getrennten Werten in eine Arbeitsmappe

/**
 * 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: Erstellen neuer .xlsx-Dateien

  1. Melden Sie sich bei Power Automate an, und erstellen Sie einen neuen geplanten Cloudflow.

  2. Legen Sie den Flow auf Wiederholen jeden "1" "Tag" fest, und wählen Sie Erstellen aus.

  3. Rufen Sie die Excel-Vorlagendatei ab. Dies ist die Grundlage für alle konvertierten .csv Dateien. Wählen Sie im Flow-Generator die + Schaltfläche und dann Aktion hinzufügen aus. Wählen Sie die Aktion Dateiinhalt abrufendes OneDrive for Business Connectors aus. Geben Sie den Dateipfad zur Datei "Template.xlsx" an.

    • Datei: /output/Template.xlsx

  4. Benennen Sie den Schritt Dateiinhalt abrufen um. Wählen Sie im Aktionsaufgabenbereich den aktuellen Titel "Dateiinhalt abrufen" aus. Ändern Sie den Namen in "Excel-Vorlage abrufen".

    Der abgeschlossene OneDrive for Business Connector im Aktionsaufgabenbereich, der in Excel-Vorlage abrufen umbenannt wurde.

  5. Fügen Sie eine Aktion hinzu, die alle Dateien im Ordner "output" abruft. Wählen Sie die Aktion OneDrive for Business Connector Dateien in Ordnern auflisten aus. Geben Sie den Ordnerpfad an, der die .csv Dateien enthält.

    • Ordner: /output

    Der abgeschlossene OneDrive for Business Connector im Aktionsaufgabenbereich.

  6. Fügen Sie eine Bedingung hinzu, sodass der Flow nur für .csv Dateien ausgeführt wird. Fügen Sie die Aktion Bedingungssteuerelement hinzu. Verwenden Sie die folgenden Werte für die Bedingung.

    • Wählen Sie einen Wert aus: Name (dynamischer Inhalt aus Dateien im Ordner auflisten). Beachten Sie, dass dieser dynamische Inhalt mehrere Ergebnisse aufweist, sodass ein For each-Steuerelement die Bedingung umgibt.
    • endet mit (aus der Dropdownliste)
    • Wert auswählen: .csv

    Das fertige Bedingungssteuerelement im Aktionsaufgabenbereich.

  7. Der Rest des Flows befindet sich im Abschnitt Wenn ja , da wir nur auf .csv Dateien reagieren möchten. Rufen Sie eine einzelne .csv Datei ab, indem Sie eine Aktion hinzufügen, die die Aktion Dateiinhalt abrufen des OneDrive for Business Connectors verwendet. Verwenden Sie die ID aus dem dynamischen Inhalt aus Listendateien im Ordner.

    • Datei: ID (dynamischer Inhalt aus dem Schritt Dateien im Ordner auflisten )

  8. Benennen Sie den neuen Schritt Dateiinhalt abrufen in ".csv Datei abrufen" um. Dies hilft dabei, diese Datei von der Excel-Vorlage zu unterscheiden.

    Die abgeschlossene Aktion .csv Datei abrufen im Aktionsaufgabenbereich.

  9. Erstellen Sie die neue .xlsx-Datei, indem Sie die Excel-Vorlage als Basisinhalt verwenden. Fügen Sie eine Aktion hinzu, die die Aktion Datei erstellendes OneDrive for Business Connectors verwendet. Verwenden Sie die folgenden Werte.

    • Ordnerpfad: /output
    • Dateiname: Name ohne Erweiterung.xlsx (wählen Sie den dynamischen Inhalt Name ohne Erweiterung aus dem Ordner Dateien auflisten aus, und geben Sie danach manuell ".xlsx" ein.
    • Dateiinhalt: Dateiinhalt (dynamischer Inhalt aus Der Excel-Vorlage abrufen)

    Der abgeschlossene Schritt Datei erstellen im Aktionsaufgabenbereich.

  10. Führen Sie das Skript aus, um Daten in die neue Arbeitsmappe zu kopieren. Fügen Sie die Aktion Skript ausführen des Excel Online (Business)-Connectors hinzu. Verwenden Sie die folgenden Werte für die Aktion.

    • Location: OneDrive for Business
    • Document Library: OneDrive
    • Datei: ID (dynamischer Inhalt aus Datei erstellen)
    • Skript: Konvertieren von CSV
    • CSV: Dateiinhalt (dynamischer Inhalt aus .csv Datei abrufen)

    Der abgeschlossene Schritt Skript ausführen im Aktionsaufgabenbereich.

  11. Speichern Sie den Flow. Der Flow-Designer sollte wie in der folgenden Abbildung aussehen.

    Ein Diagramm des abgeschlossenen Flusses, das drei Schritte vor einem für jedes Steuerelement, ein Bedingungssteuerelement in der für jeden und drei Schritte unter dem wahren Pfad der Bedingung zeigt.

  12. Verwenden Sie die Schaltfläche Test auf der Flow-Editor-Seite, oder führen Sie den Flow über die Registerkarte Meine Flows aus. Achten Sie darauf, den Zugriff zuzulassen, wenn Sie dazu aufgefordert werden.

  13. Sie sollten neue .xlsx Dateien im Ordner "output" neben den ursprünglichen .csv-Dateien finden. Die neuen Arbeitsmappen enthalten die gleichen Daten wie die CSV-Dateien.

Problembehandlung

Skripttests

Um das Skript ohne Verwendung von Power Automate zu testen, weisen Sie einen Wert zu, bevor csv Sie es verwenden. Fügen Sie den folgenden Code als erste Zeile der main Funktion hinzu, und wählen Sie Ausführen aus.

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

Durch Semikolons getrennte Dateien und andere alternative Trennzeichen

Einige Regionen verwenden Semikolons (';'), um Zellwerte anstelle von Kommas zu trennen. In diesem Fall müssen Sie die folgenden Zeilen im Skript ändern.

  1. Ersetzen Sie die Kommas in der Anweisung für reguläre Ausdrücke durch Semikolons. Dies beginnt mit let row = value.match.

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

  2. Ersetzen Sie das Komma in der Überprüfung für die leere erste Zelle durch ein Semikolon. Dies beginnt mit if (row[0].charAt(0).

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

  3. Ersetzen Sie das Komma durch ein Semikolon in der Zeile, das das Trennzeichen aus dem angezeigten Text entfernt. Dies beginnt mit row[index] = cell.indexOf.

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

Hinweis

Wenn Ihre Datei Tabstopps oder ein anderes Zeichen verwendet, um die Werte zu trennen, ersetzen Sie die ; in den obigen Ersetzungen durch \t oder ein beliebiges Zeichen, das verwendet wird.

Große CSV-Dateien

Wenn Ihre Datei Hunderttausende von Zellen enthält, können Sie das Excel-Datenübertragungslimit erreichen. Sie müssen die Synchronisierung des Skripts mit Excel in regelmäßigen Abständen erzwingen. Die einfachste Möglichkeit besteht darin, nach der Verarbeitung eines Batches von Zeilen aufzurufen console.log . Fügen Sie die folgenden Codezeilen hinzu, um dies zu ermöglichen.

  1. Fügen Sie vor rows.forEach((value, index) => {die folgende Zeile hinzu.

      let rowCount = 0;

  2. Fügen Sie nach range.setValues(data);den folgenden Code hinzu. Beachten Sie, dass Sie abhängig von der Anzahl der Spalten möglicherweise auf eine niedrigere Anzahl reduzieren 5000 müssen.

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

Warnung

Wenn Ihre CSV-Datei sehr groß ist, können Probleme beim Timeout in Power Automate auftreten. Sie müssen die CSV-Daten in mehrere Dateien unterteilen, bevor Sie sie in Excel-Arbeitsmappen konvertieren.

Akzente und andere Unicode-Zeichen

Dateien mit Unicode-spezifischen Zeichen, z. B. Akzentvokale wie é, müssen mit der richtigen Codierung gespeichert werden. Bei der Erstellung der OneDrive-Connectordatei von Power Automate wird standardmäßig ANSI für .csv Dateien verwendet. Wenn Sie die .csv-Dateien in Power Automate erstellen, müssen Sie die Bytereihenfolgemarkierung (BOM) vor den durch Trennzeichen getrennten Werten hinzufügen. Ersetzen Sie für UTF-8 den Dateiinhalt für den Schreibvorgang .csv Datei durch den Ausdruck concat(uriComponentToString('%EF%BB%BF'), <CSV Input>) (wobei <CSV Input> Ihre ursprünglichen CSV-Daten sind).

Beachten Sie, dass in diesem Beispiel nicht die .csv Dateien im Flow erstellt werden, sodass diese Änderung in Ihrem benutzerdefinierten Teil des Flows erfolgen muss. Sie können auch die .csv Dateien mit der BOM lesen und neu schreiben, wenn Sie nicht steuern, wie diese Dateien erstellt werden.

Umgebende Anführungszeichen

In diesem Beispiel werden alle Anführungszeichen ("") entfernt, die Werte umschließen. Diese werden in der Regel zu durch Trennzeichen getrennten Werten hinzugefügt, um zu verhindern, dass Kommas in den Daten als Trenntoken behandelt werden. Eine .csv Datei, die in Excel geöffnet und dann als .xlsx Datei gespeichert wird, wird dem Leser niemals diese Anführungszeichen angezeigt. Wenn Sie die Anführungszeichen beibehalten und in den endgültigen Tabellen anzeigen lassen möchten, ersetzen Sie die Zeilen 27 bis 30 des Skripts durch den folgenden Code.

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