PR
たろさん7890
心筋梗塞で死にかけたオヤジのつぶやき
Freepage List
パソコン関係
ブラウザを遅くする奴らとの戦い
Lenovo G570 をいじくる
突然、DVDが読めなくなった
無線LANが定期的に切断される
CPUファンの異音
ディスクのエラー対応
マラソン
GARMIN Forerunner 405 電池交換
情報処理試験
情報セキュリティスペシャリスト
ネットワークスペシャリスト
データベーススペシャリスト
ITサービスマネージャ
システム監査技術者
システムアーキテクト
散歩
豊洲・会社のまわり
幕張
月島
その他
情報処理以外の試験
日商簿記
救急法基礎講習
日本健康マスター検定
AWS認定試験
ITIL
ポケモンGO
GOFEST札幌2022
Safari Zone in YOKOSUKA
Calendar
Keyword Search
▼キーワード検索
Free Space
Shopping List
日本製 久留米産 はんてん メンズ 大きいサイズ おしゃれ 暖かい 大判サイズ ドビー織 アクリル生地 中綿 手詰め 男性用 袢纏 半天 半纏 どてら ちゃんちゃんこ 丹前 冬 部屋着 手づくり プレゼント ギフト
『1年保証』送料無料 累計230000個完売！レビュー投稿で10つ以上のプレゼントから選択可！ USB 3.0対応 書き込み 読み込み cdドライブTYPE-Cコネクター付き ケーブル内蔵「ランキング常連1位！」1年保証 DVDドライブ 外付け dvd cd ドライブ 外付け USB 3.0対応 書き込み 読み込み dvdドライブ 外付けdvdドライブ cdドライブ TYPE-Cコネクター ケーブル内蔵 CD/DVD-RWドライブ Mac Windows11対応 敬老の日
ふっくら仕上げ★無添加★網元が作る釜揚げシラス【極上品】釜揚げシラス【1k】(250gx4P)小分けパック【千葉県産】国産 送料無料 冷凍 天然 海鮮 母の日 父の日 敬老の日 お祝い 御中元 御歳暮 ギフト やみつき 特産 やわらか ふっくら 健康 網元 漁師直送
【おかげさまで楽天ランキング1位獲得！】加工場は漁港のすぐそば。門外不出の塩加減調整で、ふわふわの釜揚げしらすを実現。小分けで便利です。化粧箱なし割引！【楽天1位獲得】【化粧箱無し割引】龍馬しらす 250g×4パック 1000g 1kg 釜揚げしらす 小分けで便利 国産 高知産 冷凍 ギフト 自宅用 お中元 お歳暮 母の日 父の日 子供 赤ちゃん うす塩 天日干し 丼 大容量 お得用
【ちりめんじゃこ・しらす干しデイリーランキング1位獲得】 【 減塩 愛知県 ちりめんじゃこ シラス 減塩 国産 愛知 お土産 お歳暮 ギフト お取り寄せグルメ お取り寄せ 海鮮 送料無料 】しらす干し1kg 小分け(200×5) 訳あり 愛知県産 ちりめん 【 シラス干し しらす 減塩 愛知県 ちりめんじゃこ シラス 減塩 国産 愛知 お土産 お歳暮 ギフト お取り寄せ 海鮮 】
『1年保証』送料無料 累計230000個完売！レビュー投稿で10つ以上のプレゼントから選択可！ USB 3.0対応 書き込み 読み込み cdドライブTYPE-Cコネクター付き ケーブル内蔵「ランキング常連1位！」1年保証 DVDドライブ 外付け dvd cd ドライブ 外付け USB 3.0対応 書き込み 読み込み dvdドライブ 外付けdvdドライブ cdドライブ TYPE-Cコネクター ケーブル内蔵 CD/DVD-RWドライブ Mac Windows11対応 敬老の日
ふっくら仕上げ★無添加★網元が作る釜揚げシラス【極上品】釜揚げシラス【1k】(250gx4P)小分けパック【千葉県産】国産 送料無料 冷凍 天然 海鮮 母の日 父の日 敬老の日 お祝い 御中元 御歳暮 ギフト やみつき 特産 やわらか ふっくら 健康 網元 漁師直送
【おかげさまで楽天ランキング1位獲得！】加工場は漁港のすぐそば。門外不出の塩加減調整で、ふわふわの釜揚げしらすを実現。小分けで便利です。化粧箱なし割引！【楽天1位獲得】【化粧箱無し割引】龍馬しらす 250g×4パック 1000g 1kg 釜揚げしらす 小分けで便利 国産 高知産 冷凍 ギフト 自宅用 お中元 お歳暮 母の日 父の日 子供 赤ちゃん うす塩 天日干し 丼 大容量 お得用
【ちりめんじゃこ・しらす干しデイリーランキング1位獲得】 【 減塩 愛知県 ちりめんじゃこ シラス 減塩 国産 愛知 お土産 お歳暮 ギフト お取り寄せグルメ お取り寄せ 海鮮 送料無料 】しらす干し1kg 小分け(200×5) 訳あり 愛知県産 ちりめん 【 シラス干し しらす 減塩 愛知県 ちりめんじゃこ シラス 減塩 国産 愛知 お土産 お歳暮 ギフト お取り寄せ 海鮮 】
2021.12.22
カテゴリ： カテゴリ未分類
Excel のデータをMySQLに入れたいけど、CSVだと何かと大変。
Excelって、
・セルの中で改行していたり、
・データ区切り文字に使いたいカンマ(､)
・文字列囲みのダブルコーテーション（”）
とかが、含まれていたりすると、
インポートするときに、そこそこ面倒です。
phpMyAdmin だと、CSVを画面からインポートすることができるのですが、
100%の確率で、上記のような理由で、エラーとなってしまいます。
エラーをすべてつぶしていると、件数が多いと、かなりの時間を使ってしまいます。
そこで、オススメなのが、XML形式です。
前提としては、MySqlのクライアントが必要です。
MySqlクライアントから、Loadコマンドを利用します。
■MySQLのLoadコマンドで取り込む（ゴールイメージ）
たとえば、
上の例では、
・ホスト名、ユーザID、パスワード、データベース名：環境にあわせて指定
・取り込むファイルを指定： "D:\chumon.xml"
・取り込み先のテーブルを指定： table_chumon
・レコードの区切りの指定： <table_chumon>
を指定してます。
■取り込むデータの準備
XML形式での準備としては、Excelでデータを作成すると思うのですが、
実はExcelファイルから、XMLファイルを直接出力するのは面倒です。
Excelの開発ツールを使う必要があります。
Excelの開発ツールで、出力するXMLの項目などを定義するのですが、これが結構手間がかかります。
開発に慣れていない方には、ハードルが高いかも・・・
お薦めは、Accessの利用です。Accessない場合は、Excelで頑張りましょう。
Accessだと、事前の定義なしで、XMLファイルを出力できます。
大まかなイメージとしては、
１．Accessから、データが入っているExcelシートを読み込むか、またはリンクしてます
２．そして、対象のシートをXML形式で保存します。
３．事前にExcel側で、Excelの列名をテーブルの列名に合わせておきます。
具体的にみると、
１．AccessにExcelデータと取り込む
Accessを起動して、「外部データ」から「Excel」をクリック。
あとは、画面に合わせて、対象のExcelのシートを選びます（リンクでも、インポートでもどちらでもOK)
２．XMLファイルの出力
Excelを取り込むと、テーブルに表示されます。
右クリックで、「エクスポート」から「XMLファイル」を選択します。
エクスポートの情報は、「データ」と「スキーマ」で良いでしょう。
出力されたXMLって、どうなっているの？
出力したファイルをテキストエディタなどで開くと、以下のようになっています。
Excelの１行が、タグ付されます。
以下の例では、＜レイド＞と＜/レイド＞で囲まれた部分が、１行分です。
上の例で、「レコードの区切りの指定： <table_chumon> 」にあたります。
Excelのシート名がここに変換されます。
Excelの列名が、＜＞で囲まれたところに変換されています。
Mysqlにロードするときに、ここがMySQLの列名の指定になります。
つまり、Excelの方で、列名をMySQLの列名に合わせておけば、出力されたXMLにその列名で生成されます。
Excelの１つのセルは、この＜列名＞と＜/列名＞で囲まれた形式に変換されます。
セル中に改行や、区切り文字が入っていても、そのままこの＜列名＞と＜/列名＞の間に入ります。
■MySQLコマンドの実行
最後に、上で書いたのMySQLのコマンドを実行すれば、終了です。
コマンドでエラーになる主なケース： だいたい、次の２つです。
・MySQLの列名と、XMLで指定した値と一致していない
・行の区切り（例では、＜レイド＞と＜/レイド＞の部分）がXMLファイルと、MySQLコマンドで指定した値と一致していない
■MySQLクライアント
Mysqlクライアントのインストールが必要なんですが、次のようなキーワードでググれば、すぐに見つかります。
MySQL Command Line Client
MySQLクライアント
CSVのロードエラーをつぶしていく時間を考えれば、圧倒的に楽だと思います。
Excelって、
・セルの中で改行していたり、
・データ区切り文字に使いたいカンマ(､)
・文字列囲みのダブルコーテーション（”）
とかが、含まれていたりすると、
インポートするときに、そこそこ面倒です。
phpMyAdmin だと、CSVを画面からインポートすることができるのですが、
100%の確率で、上記のような理由で、エラーとなってしまいます。
エラーをすべてつぶしていると、件数が多いと、かなりの時間を使ってしまいます。
そこで、オススメなのが、XML形式です。
前提としては、MySqlのクライアントが必要です。
MySqlクライアントから、Loadコマンドを利用します。
■MySQLのLoadコマンドで取り込む（ゴールイメージ）
たとえば、
mysql --host ホスト名 --user ユーザＩＤ -pパスワード <<EOF
use データベース名;
set character_set_client="utf8";
truncate table table_chumon;
load xml local infile "D:\chumon.xml"
replace into table table_chumon
rows identified by "<table_chumon>"
EOF
上の例では、
・ホスト名、ユーザID、パスワード、データベース名：環境にあわせて指定
・取り込むファイルを指定： "D:\chumon.xml"
・取り込み先のテーブルを指定： table_chumon
・レコードの区切りの指定： <table_chumon>
を指定してます。
■取り込むデータの準備
XML形式での準備としては、Excelでデータを作成すると思うのですが、
実はExcelファイルから、XMLファイルを直接出力するのは面倒です。
Excelの開発ツールを使う必要があります。
Excelの開発ツールで、出力するXMLの項目などを定義するのですが、これが結構手間がかかります。
開発に慣れていない方には、ハードルが高いかも・・・
お薦めは、Accessの利用です。Accessない場合は、Excelで頑張りましょう。
Accessだと、事前の定義なしで、XMLファイルを出力できます。
大まかなイメージとしては、
１．Accessから、データが入っているExcelシートを読み込むか、またはリンクしてます
２．そして、対象のシートをXML形式で保存します。
３．事前にExcel側で、Excelの列名をテーブルの列名に合わせておきます。
具体的にみると、
Accessを起動して、「外部データ」から「Excel」をクリック。
あとは、画面に合わせて、対象のExcelのシートを選びます（リンクでも、インポートでもどちらでもOK)
２．XMLファイルの出力
Excelを取り込むと、テーブルに表示されます。
右クリックで、「エクスポート」から「XMLファイル」を選択します。
エクスポートの情報は、「データ」と「スキーマ」で良いでしょう。
出力されたXMLって、どうなっているの？
出力したファイルをテキストエディタなどで開くと、以下のようになっています。
Excelの１行が、タグ付されます。
以下の例では、＜レイド＞と＜/レイド＞で囲まれた部分が、１行分です。
上の例で、「レコードの区切りの指定： <table_chumon> 」にあたります。
Excelのシート名がここに変換されます。
Excelの列名が、＜＞で囲まれたところに変換されています。
Mysqlにロードするときに、ここがMySQLの列名の指定になります。
つまり、Excelの方で、列名をMySQLの列名に合わせておけば、出力されたXMLにその列名で生成されます。
Excelの１つのセルは、この＜列名＞と＜/列名＞で囲まれた形式に変換されます。
セル中に改行や、区切り文字が入っていても、そのままこの＜列名＞と＜/列名＞の間に入ります。
■MySQLコマンドの実行
最後に、上で書いたのMySQLのコマンドを実行すれば、終了です。
コマンドでエラーになる主なケース： だいたい、次の２つです。
・MySQLの列名と、XMLで指定した値と一致していない
・行の区切り（例では、＜レイド＞と＜/レイド＞の部分）がXMLファイルと、MySQLコマンドで指定した値と一致していない
■MySQLクライアント
Mysqlクライアントのインストールが必要なんですが、次のようなキーワードでググれば、すぐに見つかります。
MySQL Command Line Client
MySQLクライアント
CSVのロードエラーをつぶしていく時間を考えれば、圧倒的に楽だと思います。
お気に入りの記事を「いいね！」で応援しよう
【毎日開催】
15記事にいいね！で1ポイント
© Rakuten Group, Inc.
