Pythonを使用してExcelファイルをデータベーステーブルにインポートする方法

今日のデータ主導の世界では、データの効率的な取り扱いと処理は、あらゆる組織や個人にとって不可欠なタスクである。 Python豊富なライブラリーのエコシステムを持つPandasは、インポート・パンダ・ライブラリーのような、データ操作と管理のための強力なツールを提供している。 よくあるシナリオのひとつに、以下のようなデータを抽出またはインポートする必要がある。エクセル表計算ソフトを使用し、データをデータベースに保存または挿入して、さらなる分析や他のシステムとの統合を行う。 このチュートリアルでは、このプロセスを自動化し、Excelシートファイルからシームレスにデータを読み込んでデータベースに挿入するPythonスクリプトの作成方法を探ります。 このチュートリアルが終わる頃には、データ移行タスクを効率的に処理する準備が整っていることだろう。 始めましょう!

Pythonを使用してExcelファイルをデータベーステーブルにインポートする方法

1. IronXL ライブラリのインストールから始めます。

2. IronXLを使ってExcelファイルをメモリにロードします。

3. 作業したい特定のスプレッドシートをロードする。

4. インポートするデータ範囲を正確に選択します。

5. Pythonを使ってSQLiteやMySQLなどのデータベースとの接続を確立します。

6. インポートしたデータを格納するために、SQLiteデータベース内に新しいテーブルを作成します。

7. Excel ファイルから選択した行を、新しく作成した SQLite テーブルに挿入します。

8. 作成されたSQLiteテーブルからデータを取り出し、選択し、さらなる分析や処理を行う。

   このチュートリアルではIronXLPythonのライブラリで、Excelファイルを効率的に扱うことで有名です。 IronXLをスクリプトに統合することで、Excelスプレッドシートからのシームレスなデータ抽出を保証し、さらなる分析と処理のためにデータベースへのスムーズな挿入を可能にします。

IronXLとは何ですか？

IronXLはIron Software によって開発されたPythonライブラリです。読込み, 生成Pythonアプリケーション内でExcelファイルを直接編集することができます。 特筆すべきは、IronXLがMicrosoft Excelのインストールから独立していることで、異なる環境への展開を簡素化していることである。 IronXL を使用すると、開発者は次のようなメリットがあります：

クロスプラットフォームサポート: Windows、macOS、Linux、Docker、Azure、AWSプラットフォーム上でシームレスな操作を楽しむことができ、多様な開発セットアップへの適応性を保証します。

データのインポートとエクスポート: XLS、XLSX、CSV、TSVファイルからのデータインポートを簡単に処理し、ワークシートをこれらのフォーマットにエクスポートしたり、相互運用性を高めるためにJSONにエクスポートする柔軟性も備えています。

暗号化機能: IronXLの暗号化機能を活用し、XLSX、XLSM、XLTXファイルをパスワードで保護することで、データの安全性を確保します。

数式と再計算: Excelで楽に作業する数式さらに、シートを編集するたびに自動で再計算されるため、データ操作の正確性と信頼性が保証されます。

セルのスタイリング：フォントを調整することによって、個々のセルの外観をカスタマイズするスタイルサイズ、背景パターン、ボーダー、整列など、Excelドキュメントのビジュアルプレゼンテーションを向上させます。

幅広いドキュメント形式: XLS、XLSX、XLST、XLSM、CSV、TSVを含む様々な形式をサポートすることで、IronXLは開発者が多くのシナリオのデータを簡単かつ効率的に扱えるようにします。

それでは、IronXLのインストールから始めましょう。

ステップ1: IronXLライブラリのインストール

最初のステップは、IronXL ライブラリをインストールすることです。 コマンドプロンプトで以下のコマンドを実行し、IronXLをインストールします。

pip install IronXL

pip install IronXL

ステップ2：Excelワークブックを読み込む

次のステップは、エクセル・ファイルを読み込むことです。このチュートリアルでは以下のExcelファイルを使用します。

次のコードは、既存のExcelファイルをメモリにロードします。

from ironxl import *     # Supported for XLSX, XLS, XLSM, XLTX, CSV, and TSV
License.LicenseKey = "IRONSUITE.ABC.XYZ.COM.15796-DEPLOYMENT.TRIAL-5X63V4.TRIAL.EXPIRES.27.MAY.2024";
workbook = WorkBook.Load("sample_excel.xlsx")

from ironxl import *     # Supported for XLSX, XLS, XLSM, XLTX, CSV, and TSV
License.LicenseKey = "IRONSUITE.ABC.XYZ.COM.15796-DEPLOYMENT.TRIAL-5X63V4.TRIAL.EXPIRES.27.MAY.2024";
workbook = WorkBook.Load("sample_excel.xlsx")

上記のPythonコードスニペットは "sample_excel.xlsx "という名前のExcelワークブックをIronXLライブラリ(Python)を使って読み込む例です。 まず、必要なPythonモジュールをIronXLからインポートします。 その後、ライセンスキーが割り当てられ、ライブラリーの利用が認証される。 無料のライセンスキーはIronXL ウェブサイト. 最後に、指定されたExcelワークブックを開いてメモリにロードするために、Loadメソッドが使用される。 これにより、データの読み取り、セル値の変更、フォーマットの適用など、プログラムによる内容の操作が可能になる。

ステップ3：ワークシートの選択

IronXL を使ってExcelワークブックのワークシートを選択するには、ワークシートのインデックスまたは名前を指定します。

worksheet = workbook.WorkSheets[0]

worksheet = workbook.WorkSheets[0]

この行は、読み込まれたエクセルのワークブックの最初のワークシートを選択し、変数 "worksheet "に代入する。 これは、Excelシートからワークシート変数にExcelデータをロードします。

ステップ4：データベース接続を開く

このチュートリアルでは、MySQLデータベースサーバーの代わりにSQLiteデータベースを使用します。 データベース操作を開始するには、データベースへの接続を確立することから始める。

import sqlite3
# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('data.db')

import sqlite3
# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('data.db')

上の行は'data.db'という名前のSQLiteデータベースへの接続を確立する。 指定したデータベースが存在しない場合は、自動的に作成されます。 この接続によって、クエリの実行やデータ操作の実行など、SQLite データベースとのその後のやり取りが可能になります。

ステップ5：テーブルの作成

次のステップは、Excelファイルからデータをインポートするためのデータベース・テーブルを作成することです。SQLiteデータベースにテーブルを作成するには、接続オブジェクトを使ってSQL文を実行します。

cursor = conn.cursor() // database objects
cursor.execute('''CREATE TABLE IF NOT EXISTS customer ( id Number,
            FirstName TEXT,
                    LastName TEXT,
                    Gender TEXT,
            Country TEXT,
            Age Number
                )''')

cursor = conn.cursor() // database objects
cursor.execute('''CREATE TABLE IF NOT EXISTS customer ( id Number,
            FirstName TEXT,
                    LastName TEXT,
                    Gender TEXT,
            Country TEXT,
            Age Number
                )''')

上記のコード・スニペットは、SQLiteデータベース接続内でSQLコマンドを実行するためにカーソル・オブジェクトを初期化します。 カラム 'id'、'FirstName'、'LastName'、'Gender'、'Country'、'Age' を持つ 'customer' という名前のテーブルが作成される。 テーブルがまだ存在しない場合は、指定されたカラム・データ型に従ってテーブルが作成される。

ステップ6：Pythonを使ってデータベースにデータをインポートする

では、新しく作成したテーブルにデータを挿入します。 Excelファイルをインポートし、そのデータをSQLiteデータベースに挿入します。

for i in range(2, 11):
    values_tuple = (
        worksheet[f"A{i}"].StringValue,
        worksheet[f"B{i}"].StringValue,
        worksheet[f"C{i}"].StringValue,
        worksheet[f"D{i}"].StringValue,
        worksheet[f"E{i}"].StringValue,
        worksheet[f"F{i}"].StringValue
    )
    cursor.execute("INSERT INTO customer VALUES (?, ?, ?, ?, ?, ?)", values_tuple)
conn.commit()

for i in range(2, 11):
    values_tuple = (
        worksheet[f"A{i}"].StringValue,
        worksheet[f"B{i}"].StringValue,
        worksheet[f"C{i}"].StringValue,
        worksheet[f"D{i}"].StringValue,
        worksheet[f"E{i}"].StringValue,
        worksheet[f"F{i}"].StringValue
    )
    cursor.execute("INSERT INTO customer VALUES (?, ?, ?, ?, ?, ?)", values_tuple)
conn.commit()

上記のコードは、エクセルのワークシートの2行目から10行目までを反復処理し、各行のA列からF列の値を抽出する。 これらの値は、'customer'テーブルに挿入されるデータを表すタプルに格納される。 その後、カーソルはSQLのINSERTコマンドを実行し、値のタプルをテーブルに組み込みます。 このプロセスを行ごとに繰り返し、ExcelファイルからSQLiteデータベースにデータを効率的にインポートする。 最後に、conn.commit()トランザクションをコミットし、変更がデータベースに保存され、永続化されることを確認します。

ステップ7：データベースからのデータの読み込み

データが正しく挿入されたかどうかを確認するには、SELECTクエリを使用してSQLiteデータベースの'customer'テーブルからデータを読み取ることができます。 例えば：

cursor.execute("SELECT * FROM customer")
# Fetch all rows from the result set
rows = cursor.fetchall()
# Print the rows
for row in rows:
    print(row)
conn.close()

cursor.execute("SELECT * FROM customer")
# Fetch all rows from the result set
rows = cursor.fetchall()
# Print the rows
for row in rows:
    print(row)
conn.close()

上記のコードは、SQLiteデータベースの'customer'テーブルに対してSELECTクエリーを実行し、すべての行を取得する。 フェッチされた行は、fetchallを使用して'rows'変数に格納される。()メソッド。 その後、各行が繰り返し印刷され、「customer」テーブルに挿入されたデータが表示される。 最後に、データベース接続は close()メソッドでリソースを解放する。

コンプリート・コードは以下の通り：

import sqlite3
from ironxl import *     # Supported for XLSX, XLS, XLSM, XLTX, CSV, and TSV
License.LicenseKey = "IRONSUITE.ABC.XYZ.COM.15796-DEPLOYMENT.TRIAL-5X63V4.TRIAL.EXPIRES.27.MAY.2024";
workbook = WorkBook.Load("sample_excel.xlsx")
# Select worksheet at index 0
worksheet = workbook.WorkSheets[0]
# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('data.db')
cursor = conn.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS customer ( id Number,
                    FirstName TEXT,
                    LastName TEXT,
                    Gender TEXT,
                    Country TEXT,
                    Age Number
                )''')
cursor.execute("DELETE FROM customer")
for i in range(2, 11):
    values_tuple = (
        worksheet[f"A{i}"].StringValue,
        worksheet[f"B{i}"].StringValue,
        worksheet[f"C{i}"].StringValue,
        worksheet[f"D{i}"].StringValue,
        worksheet[f"E{i}"].StringValue,
        worksheet[f"F{i}"].StringValue
    )
    cursor.execute("INSERT INTO customer VALUES (?, ?, ?, ?, ?, ?)", values_tuple)
conn.commit()  
cursor.execute("SELECT * FROM customer")
# Fetch all rows from the result set
rows = cursor.fetchall()
# Print the rows
for row in rows:
    print(row)
conn.close()

import sqlite3
from ironxl import *     # Supported for XLSX, XLS, XLSM, XLTX, CSV, and TSV
License.LicenseKey = "IRONSUITE.ABC.XYZ.COM.15796-DEPLOYMENT.TRIAL-5X63V4.TRIAL.EXPIRES.27.MAY.2024";
workbook = WorkBook.Load("sample_excel.xlsx")
# Select worksheet at index 0
worksheet = workbook.WorkSheets[0]
# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('data.db')
cursor = conn.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS customer ( id Number,
                    FirstName TEXT,
                    LastName TEXT,
                    Gender TEXT,
                    Country TEXT,
                    Age Number
                )''')
cursor.execute("DELETE FROM customer")
for i in range(2, 11):
    values_tuple = (
        worksheet[f"A{i}"].StringValue,
        worksheet[f"B{i}"].StringValue,
        worksheet[f"C{i}"].StringValue,
        worksheet[f"D{i}"].StringValue,
        worksheet[f"E{i}"].StringValue,
        worksheet[f"F{i}"].StringValue
    )
    cursor.execute("INSERT INTO customer VALUES (?, ?, ?, ?, ?, ?)", values_tuple)
conn.commit()  
cursor.execute("SELECT * FROM customer")
# Fetch all rows from the result set
rows = cursor.fetchall()
# Print the rows
for row in rows:
    print(row)
conn.close()

結論

結論として、このチュートリアルでは、以下の自動化されたアプローチを示した。読書データは、データマネジメントの可能性を最大限に引き出す。 PythonとIronXLのパワーを活用して、データハンドリングワークフローを最適化し、プロジェクトを自信を持って推進しましょう。