PYTHONのためのIRONXLの使用

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

Name: IronXL
Brand: Iron Software
Availability: InStock
Rating: 4.67 (175 reviews)

チャクニット・ビン

2024年6月6日

共有:

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

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

IronXL ライブラリのインストールから始めます。
IronXLを使ってExcelファイルをメモリにロードします。
作業したい特定のスプレッドシートをロードする。
インポートするデータ範囲を正確に選択します。
Pythonを使ってSQLiteやMySQLなどのデータベースとの接続を確立します。
インポートしたデータを格納するために、SQLiteデータベース内に新しいテーブルを作成します。
Excel ファイルから選択した行を、新しく作成した SQLite テーブルに挿入します。
作成されたSQLiteテーブルからデータを取り出し、選択し、さらなる分析や処理を行う。

このチュートリアルでは、Excelファイルの処理に優れたPythonライブラリであるIronXLを使用します。 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ドキュメントの視覚的なプレゼンテーションを強化します。

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

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

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

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

pip install IronXL

pip install IronXL

PYTHON

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

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

Pythonを使用してExcelファイルをデータベーステーブルにインポートする方法: 図1 - サンプル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

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

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

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

worksheet = workbook.WorkSheets[0]

worksheet = workbook.WorkSheets[0]

PYTHON

この行は、読み込まれたエクセルのワークブックの最初のワークシートを選択し、変数 "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')

PYTHON

上の行は'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
                )''')

PYTHON

上記のコード・スニペットは、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()

PYTHON

上記のコードは、エクセルのワークシートの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()

PYTHON

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

Pythonを使用してExcelファイルをデータベーステーブルにインポートする方法: 図2 - データベース出力から読み取る

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

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