Share via


CSV ファイルを Excel ブックに変換する

多くのサービスは、コンマ区切り値 (CSV) ファイルとしてデータをエクスポートします。 このソリューションにより、これらの CSV ファイルを .xlsx ファイル形式の Excel ブックに変換するプロセスが自動化されます。 Power Automate フローを使用して、OneDrive フォルダー内の .csv 拡張子を持つファイルを検索し、Office スクリプトを使用して、.csv ファイルから新しい Excel ブックにデータをコピーします。

ソリューション

  1. .csv ファイルと空の "テンプレート" .xlsx ファイルを OneDrive フォルダーに格納します。
  2. CSV データを範囲に解析する Office スクリプトを作成します。
  3. Power Automate フローを作成して、.csv ファイルを読み取り、その内容をスクリプトに渡します。

サンプル ファイル

convert-csv-example.zip をダウンロードして、Template.xlsx ファイルと 2 つのサンプル .csv ファイルを取得します。 OneDrive 内のフォルダーにファイルを抽出します。 このサンプルでは、フォルダーの名前が "output" であることを前提としています。

サンプル ブックに次のスクリプトを追加します。 Excel で、[新しいスクリプトの自動化] を>使用してコードを貼り付け、スクリプトを保存します。 CSV 変換として保存し、サンプルを自分で試してみてください。

サンプル コード: コンマ区切りの値をブックに挿入する

/**
 * 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 フロー: 新しい .xlsx ファイルを作成する

  1. Power Automate にサインインし、新しいスケジュールされたクラウド フローを作成します。

  2. フローを [1 日 ごとに繰り返す ] に設定し、[ 作成] を選択します。

  3. テンプレート Excel ファイルを取得します。 これは、変換されたすべての .csv ファイルの基礎です。 フロー ビルダーで、[アクションの追加] + ボタンを選択 しますOneDrive for Business コネクタの [ファイル コンテンツの取得] アクションを選択します。 "Template.xlsx" ファイルへのファイル パスを指定します。

    • ファイル: /output/Template.xlsx
  4. [ ファイル コンテンツの取得 ] ステップの名前を変更します。 アクション作業ウィンドウで、現在のタイトル "ファイル コンテンツの取得" を選択します。 名前を "Excel テンプレートの取得" に変更します。

    アクション作業ウィンドウで完了したOneDrive for Business コネクタが、名前が [Excel テンプレートの取得] に変更されました。

  5. "出力" フォルダー内のすべてのファイルを取得するアクションを追加します。 OneDrive for Business コネクタの [フォルダー内のファイルの一覧表示] アクションを選択します。 .csv ファイルを含むフォルダー パスを指定します。

    • フォルダー: /output

    アクション作業ウィンドウで完了したOneDrive for Business コネクタ。

  6. フローが .csv ファイルでのみ動作するように条件を追加します。 条件コントロール アクションを追加します。 条件には次の値を使用 します

    • [名前] ( [フォルダー内のファイルの一覧] の動的コンテンツ) の値を選択します。 この動的コンテンツには複数の結果があるため、[ For each ]\(各コントロール\) が 条件を囲みます。
    • で終わる (ドロップダウン リストから)
    • 値の選択: .csv

    アクション作業ウィンドウの完了した条件コントロール。

  7. フローの残りの部分は[ If yes]\(はい\) セクションの下にあります。これは、.csv ファイルに対してのみ処理するためです。 OneDrive for Business コネクタの [ファイル コンテンツの取得] アクションを使用するアクションを追加して、個々の .csvファイルを取得します。 フォルダー内のリスト ファイルの動的コンテンツの ID を使用します。

    • ファイル: ID ( [フォルダー内のファイルの一覧表示 ] ステップの動的コンテンツ)
  8. 新しい [ファイル コンテンツの取得] ステップの名前を ".csv ファイルの取得" に変更します。 これは、このファイルを Excel テンプレートと区別するのに役立ちます。

    アクション作業ウィンドウで完了した [ファイル .csv 取得] アクション。

  9. Excel テンプレートを基本コンテンツとして使用して、新しい .xlsx ファイルを作成します。 OneDrive for Business コネクタの [ファイルの作成] アクションを使用するアクションを追加します。 次の値を使用します。

    • フォルダー パス: /output
    • ファイル名: 拡張子のない名前.xlsx ([フォルダー内のファイルの一覧] から [拡張子のない名前] 動的コンテンツを選択し、その後に「.xlsx」と手動で入力します)
    • ファイル コンテンツ: ファイル コンテンツ ( Get Excel テンプレートからの動的コンテンツ)

    アクション作業ウィンドウで完了した [ファイルの作成] ステップ。

  10. スクリプトを実行して、新しいブックにデータをコピーします。 Excel Online (Business) コネクタの [スクリプトの実行] アクションを追加します。 アクションには次の値を使用します。

    • 場所: OneDrive for Business
    • ドキュメント ライブラリ: OneDrive
    • ファイル: ID ( ファイルの作成からの動的コンテンツ)
    • スクリプト: CSV を変換する
    • csv: ファイル コンテンツ ( get .csv file から動的コンテンツ)

    アクション作業ウィンドウで完了した [スクリプトの実行] ステップ。

  11. フローを保存します。 フロー デザイナーは次の図のようになります。

    各コントロールの 前の 3 つのステップ、各コントロールの 内の条件コントロール、および条件の真のパスの下の 3 つのステップを示す完了したフローの図。

  12. フロー エディター ページの [テスト ] ボタンを使用するか、[ マイ フロー ] タブでフローを実行します。プロンプトが表示されたら、必ずアクセスを許可してください。

  13. 新しい .xlsx ファイルは、元の .csv ファイルと共に "output" フォルダーに表示されます。 新しいブックには、CSV ファイルと同じデータが含まれています。

トラブルシューティング

スクリプト テスト

Power Automate を使用せずにスクリプトをテストするには、使用する前に に値を csv 割り当てます。 関数の最初の行として次のコードを main 追加し、[ 実行] を選択します。

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

セミコロン区切りファイルとその他の代替区切り記号

一部のリージョンでは、(';')コンマの代わりにセミコロンを使用してセル値を区切ります。 この場合は、スクリプト内の次の行を変更する必要があります。

  1. 正規表現ステートメントのコンマをセミコロンで置き換えます。 これは で let row = value.match始まります。

    let row = value.match(/(?:;|\n|^)("(?:(?:"")*[^"]*)*"|[^";\n]*|(?:\n|$))/g);
    
  2. 空白の最初のセルのチェックのセミコロンでコンマを置き換えます。 これは で if (row[0].charAt(0)始まります。

    if (row[0].charAt(0) === ';') {
    
  3. コンマは、表示されるテキストから区切り文字を削除する行のセミコロンに置き換えます。 これは で row[index] = cell.indexOf始まります。

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

注:

ファイルでタブやその他の文字を使用して値を区切る場合は、上記の置換の の を ; 、使用されている文字または任意の文字に \t 置き換えます。

大きな CSV ファイル

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

  1. の前 rows.forEach((value, index) => {に、次の行を追加します。

      let rowCount = 0;
    
  2. の後 range.setValues(data);に、次のコードを追加します。 列の数によっては、より小さい数に減らす 5000 必要がある場合があることに注意してください。

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

警告

CSV ファイルが非常に大きい場合は、 Power Automate でタイムアウトに問題が発生する可能性があります。 CSV データを Excel ブックに変換する前に、CSV データを複数のファイルに分割する必要があります。

アクセントとその他の Unicode 文字

のようなアクセント付き母音 éなど、Unicode 固有の文字を持つファイルは、正しいエンコードで保存する必要があります。 Power Automate の OneDrive コネクタ ファイルの作成は、.csv ファイルの既定で ANSI になります。 Power Automate で .csv ファイルを作成する場合は、コンマ区切りの値の前に バイトオーダー マーク (BOM) を追加する必要があります。 UTF-8 の場合、書き込み .csv ファイル操作のファイルの内容を式 concat(uriComponentToString('%EF%BB%BF'), <CSV Input>) (元の CSV データ) <CSV Input> に置き換えます。

このサンプルではフローに .csv ファイルが作成されないため、この変更はフローのカスタム部分で行う必要があることに注意してください。 これらのファイルの作成方法を制御しない場合は、BOM を使用して .csv ファイルを読み書きすることもできます。

周囲の引用符

このサンプルでは、値を囲む引用符 ("") を削除します。 通常、これらはコンマ区切りの値に追加され、データ内のコンマが分離トークンとして扱われないようにします。 Excel で開き、.xlsx ファイルとして保存された .csv ファイルには、これらの引用符がリーダーに表示されることはありません。 引用符を保持し、最終的なスプレッドシートに表示する場合は、スクリプトの 27 から 30 行目を次のコードに置き換えます。

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