【業務効率化】ExcelをCloud FunctionでCSV形式に変換してBigQueryに連携する

今回は、Excelのxlsx形式のファイルをCloud Storageにアップロード後、Cloud Functionでcsv形式に変換して、Bigqueryに連携する方法をまとめていきます。

この投稿は、Google Cloudのアカウント設定はすでに完了している前提で進めていきます。

全体の流れは以下です。
①ExcelファイルをCloud Storageにアップロードする
②Cloud Functionでcsv形式に変換
③Cloud StorageからBigqueryに連携

Cloud Storageでバケットをつくる

Excelファイルをアップロードするバケットを作成する

Google CloudでCloud Storageを表示して「バケット」を選択します。

上部にある「＋作成」をクリックする。

バケットに名前を付けて「続行」をクリックする

「Region」「asia-northest1（東京）」を選択して
「続行」をクリックする。

その他の項目はそのままで［続行］を選択して、最後［作成］を選択します。バケットが作成されました。

もうひとつ、CSVファイルをアップロードするバケットを作成する

上記Excel用バケットと同じ要領でもうひとつバケットを作成しておきます。

アップロードするExcelファイルの準備

Excelでxlsx形式のファイルを生成します。
ファイルをマージするため、2ファイル生成します。

Cloud Functionsで関数をデプロイする

イベントを作成する

+ファンクションを作成をクリックします

環境：第1世代
関数名：ここではcsv_test
トリガーのタイプ：Cloud Storage
バケット：ここではxslx_upload_test
「保存」して「次へ」をクリック

コードをかいていく

requirements.txtに
openpyxlとgoogle-cloud-storageライブラリを記載する

openpyxl==3.*
google-cloud-storage

次にmain.pyに以下のコードを記入して「デプロイ」をクリック

import io
import openpyxl
import re
import csv
from google.cloud import storage

def download_blob_to_buffer(blob):
    """Download a blob to an in-memory buffer."""
    buffer = io.BytesIO()
    blob.download_to_file(buffer)
    buffer.seek(0)
    return buffer

def read_excel_and_extract_data(buffer, sheet_name, start_row):
    """Read an Excel file from a buffer and extract data from a specified sheet."""
    wb = openpyxl.load_workbook(buffer)
    if sheet_name not in wb.sheetnames:
        return []
    sheet = wb[sheet_name]
    max_row, max_column = sheet.max_row, sheet.max_column

    data = []
    for row in range(start_row, max_row + 1):
        row_data = [sheet.cell(row=row, column=col).value for col in range(1, max_column + 1)]
        data.append(row_data)
    
    wb.close()
    return data

def upload_csv_to_bucket(client, bucket_name, data, output_name):
    """Upload CSV data to a specified Google Cloud Storage bucket."""
    bucket = client.get_bucket(bucket_name)
    blob = bucket.blob(output_name)

    si = io.StringIO()
    writer = csv.writer(si, quoting=csv.QUOTE_ALL)
    writer.writerows(data)
    
    blob.upload_from_string(si.getvalue(), content_type='text/csv')
    si.close()

def conv_csv_2(event, context):
    """Google Cloud Function to convert Excel files in a GCS bucket to a single CSV file."""
    BUCKET_NAME = event['bucket']
    TARGET_BUCKET_NAME = "csv_output_1"
    TARGET_SHEET_NAME = "ローデータ"
    OUT_NAME = 'merge.csv'
    START_ROW = 2

    client = storage.Client()
    bucket = client.get_bucket(BUCKET_NAME)
    blobs = client.list_blobs(BUCKET_NAME)

    data = []
    pattern = r".*\.(xlsx|xls)$"

    for blob in blobs:
        if re.match(pattern, blob.name, re.IGNORECASE):
            buffer = download_blob_to_buffer(blob)
            data.extend(read_excel_and_extract_data(buffer, TARGET_SHEET_NAME, START_ROW))

    upload_csv_to_bucket(client, TARGET_BUCKET_NAME, data, OUT_NAME)
    
    return "OK"

デプロイが終わると、緑のチェックがつきます

動作テスト

Excelファイルが、csvに変換されるかテストします。
Excelファイルをバケットにアップロードする

csvのバケット csv_output_1 に無事csv形式に変換されたファイルが格納されました。

BigQueryで外部テーブルとして登録する

次にファイルをBigqueryに連携します。

BigQuery → BigQuery Studioを選択する

データセットを作成

データセットID
リージョンを選択
データのロケーション：asia-northeast1（東京）
「データセットを作成」をクリック

データセットにテーブルを作成する

テーブルの作成元：Google Cloud Storage
参照をクリックしてバケット csvを選択「merge.scv」を選び「選択」をクリック

テーブル名を入力
テーブルタイプ：外部テーブル
スキーマ「テキストとして編集」をクリックして

以下コードを入力します。

[
  {
    "mode": "NULLABLE",
    "name": "ID",
    "type": "INTEGER",
    "description": "Identifier"
  },
  {
    "mode": "NULLABLE",
    "name": "imp",
    "type": "INTEGER",
    "description": "Impressions"
  },
  {
    "mode": "NULLABLE",
    "name": "CLICK",
    "type": "INTEGER",
    "description": "Clicks"
  },
  {
    "mode": "NULLABLE",
    "name": "CV",
    "type": "INTEGER",
    "description": "Conversions"
  }
]

テーブルが作成されました。

クエリでデータを参照する

作成したテーブルをクリックして、クエリを選択します。

SQLを入力する

SELECT * FROM 'プロジェクトID' LIMIT 1000

データを確認することができました。

まとめ

業務でExcelファイルをBigQueryにアップロードする可能性がでてきたため、csvに変換して、BigQueryで参照できるようにしてみました。
最後まで読んでいただきありがとうございます。