Microsoft Learn Challenge

CSVファイルをExcelブックに変換 – Office Scripts

多くのサービスはデータをCSVファイルとしてエクスポートしています。このソリューションは、CSVファイルをExcelの.xlsxファイル形式に変換するプロセスを自動化します。Power Automateのフローを使用して、OneDriveフォルダー内の.csv拡張子のファイルを見つけ、Office Scriptを使用してCSVファイルのデータを新しいExcelワークブックにコピーします。CSVデータを範囲に解析するOffice Scriptを作成し、Power Automateのフローを設定して、新しい.xlsxファイルを作成し、その内容をスクリプトで処理します。

CSVファイルをExcelワークブックに変換する自動化ソリューション

多くのサービスでは、データをカンマ区切り値（CSV）ファイルとしてエクスポートします。本記事では、これらのCSVファイルを.xlsx形式のExcelワークブックに変換するプロセスを自動化するソリューションを紹介します。このソリューションでは、OneDriveフォルダ内の.csv拡張子を持つファイルを見つけるためにPower Automateのフローを使用し、Office Scriptを使用してCSVファイルから新しいExcelワークブックにデータをコピーします。

解決策

以下の手順で、CSVファイルをExcelワークブックに変換することができます。

  1. .csvファイルと空の「Template.xlsx」ファイルをOneDriveフォルダに保存します。
  2. Office Scriptを作成し、CSVデータを範囲に解析します。
  3. Power Automateフローを作成し、.csvファイルを読み込み、スクリプトにその内容を渡します。

サンプルファイル

こちらから「convert-csv-example.zip」をダウンロードし、「Template.xlsx」ファイルと2つのサンプル.csvファイルを取得します。ファイルをOneDrive内の「output」という名前のフォルダーに抽出します。

サンプルワークブックに以下のスクリプトを追加します。Excelで「Automate」 > 「New Script」を使用し、コードを貼り付けてスクリプトを保存します。「Convert CSV」という名前で保存して、サンプルを試してみてください！

サンプルコード：カンマ区切り値をワークブックに挿入

/**
 * CSVデータを範囲に変換し、ワークブックに追加します。
 */
function main(workbook: ExcelScript.Workbook, csv: string) {
  let sheet = workbook.getWorksheet("Sheet1");

  // Windowsの\r文字を削除します。
  csv = csv.replace(/\r/g, "");

  // 各行を行に分割します。
  let rows = csv.split("\n");

  const csvMatchRegex = /(?:,|\n|^)("(?:(?:"")*[^"]*)*"|[^",\n]*|(?:\n|$))/g
  rows.forEach((value, index) => {
    if (value.length > 0) {
      let row = value.match(csvMatchRegex);

      if (row[0].charAt(0) === ',') {
        row.unshift("");
      }

      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;
      });

      let data: string[][] = [];
      data.push(row);

      let range = sheet.getRangeByIndexes(index, 0, 1, data[0].length);
      range.setValues(data);
    }
  });
}

Power Automateフロー：新規.xlsxファイルを作成する

以下の手順でPower Automateフローを設定します。

  1. Power Automateにサインインし、新しい「Scheduled cloud flow」を作成します。
  2. フローを「1日ごとに繰り返す」に設定し、「作成」を選択します。
  3. テンプレートExcelファイルを取得します。フローのビルダーで「+」ボタンを選択し、「アクションを追加」で「OneDrive for Business」コネクタの「Get file content」アクションを選択します。ファイルパスに「/output/Template.xlsx」を指定します。

以下の手順に従ってフローを完成させることができます。

トラブルシューティング

スクリプトテスト

Power Automateを使用せずにスクリプトをテストするには、csvに値を割り当ててから使用します。

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

セミコロン区切りファイルと他の代替セパレータ

一部の地域では、セル値を区切るためにセミコロン（’;’）を使用します。その場合、スクリプト内の以下の行を変更する必要があります。

  1. 正規表現ステートメント内のカンマをセミコロンに置き換えます。
  2. 最初のセルが空であるかどうかを確認する行のカンマをセミコロンに置き換えます。
  3. 表示テキストから区切り文字を削除する行のカンマをセミコロンに置き換えます。

大きなCSVファイル

CSVファイルが何十万セルもある場合、Excelのデータ転送制限に達する可能性があります。この場合、スクリプトを定期的にExcelと同期させる必要があります。これを行う最も簡単な方法は、バッチ処理後にconsole.logを呼び出すことです。

アクセントや他のUnicode文字

Unicode特有の文字を含むファイルは、正しいエンコーディングで保存する必要があります。Power AutomateのOneDriveコネクタのファイル作成は、.csvファイルに対してANSIがデフォルトです。UTF-8の場合は、BOMを追加する必要があります。

このソリューションを利用することで、CSVを効率的にExcel形式に変換し、データ管理を大幅に簡素化することができます。

————-

Convert CSV files to Excel workbooks – Office Scripts

Source link

The article discusses an automated solution for converting comma-separated value (CSV) files into Excel workbook (.xlsx) format using Power Automate and Office Scripts. The process involves several key steps:

  1. Setup: Store the CSV files and a blank "Template.xlsx" file in a OneDrive folder.

  2. Office Script Creation: Write an Office Script that parses CSV data and transfers it into a new Excel workbook. This script removes unwanted characters, splits the CSV content into rows, and organizes it into a range in Excel.

  3. Power Automate Flow: Create a Scheduled cloud flow in Power Automate that performs the following actions:

    • Repeats daily.
    • Retrieves the template Excel file.
    • Lists all files in the designated folder and selects only those ending with .csv.
    • For each CSV file, fetch its content, then create a new .xlsx file using the template.
    • Run the Office Script to transfer data from the CSV file to the new Excel workbook.
  4. Testing and Troubleshooting: The article provides sample code for testing the script independently and guides modifications for handling other delimiters (like semicolons) or large files. It also emphasizes the need for correct file encoding and options for keeping or removing surrounding quotation marks in the CSV data.

Overall, this solution streamlines the conversion of CSV files into Excel format, making data management more efficient.

Power BI 2025年2月の機能概要 | Microsoft Power BIブログ 前の記事 ワークブックを開くメソッド (Excel) | Microsoft Learn 次の記事

関連記事

コメント

この記事へのコメントはありません。