質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

Q&A

解決済

2回答

4307閲覧

ExcelのVBAで特定シートの中身を一発でMySQL内のテーブルに反映させたい

H.K2

総合スコア88

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

0グッド

0クリップ

投稿2020/05/01 13:41

前提・実現したいこと

下記のようなテーブルがExcelのシートにあるとして、このテーブルの内容を
MySQLに反映させるプログラムを作成したいと思っています。
イメージ説明

発生している問題・エラーメッセージ

ネットで探してみて、これをRecordsetを用いて別のシートに反映させるプログラムはあったのですが、
mysqlに直接反映させる方法が分かりませんでした。
pythonでやるのであれば、pandasに取り込んで、to_sqlなどすれば解決すると思うのですが、
VBAは不慣れで方法が分かりませんでした。(今回のプログラムはVBAで記載する必要があります)
お手数をおかけいたしますが、ご教示いただくことは可能でしょうか。

該当のソースコード

VBA

1Sub test_register_to_db() 2 Dim cn_excel As ADODB.Connection 3 Dim rs_excel As ADODB.Recordset 4 Call DB_Connect 'mysqlのDBに接続(connectionはcn) 5 6 Set cn_excel = CreateObject("ADODB.Connection") 7 Set rs_excel = CreateObject("ADODB.Recordset") 8 cn_excel.Provider = "Microsoft.ACE.OLEDB.12.0" 9 '1行目がヘッダの場合はHDR=YESにする。NOの場合はF1,F2,F3・・・と番号が振られる。 10 11 cn_excel.Properties("Extended Properties") = "Excel 12.0;HDR=NO;IMEX=1" 12 13 cn_excel.Open ThisWorkbook.FullName '自ワークブックのファイル名を指定しているが、 14 'もちろん別ワークブックでもOK! 15 16 strSQL = "" 17 strSQL = strSQL & " SELECT * " 18 strSQL = strSQL & " FROM [db登録$B2:F10] " 'Sheet2のA3:Z100にデータがあると仮定する。 19 rs_excel.Open strSQL, cn_excel, adOpenKeyset, adLockReadOnly 20 21 22 ThisWorkbook.Worksheets("コピー先").Cells(10, 3).CopyFromRecordset rs_excel 'シート1のセル(10,3)からデータをセットする 23 24 rs_excel.Close 25 Set rs_excel = Nothing 26 cn_excel.Close 27 Set cn_excel = Nothing 28 29End Sub

試したこと

ネットでサンプルコードを探し、これを改造してみた。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2

0

mysqlにデータを追加する場合は,SQLのinsertを使う必要があります。

こちらを確認されてみると参考になると思います。

直接セルを指定していませんが、テーブルを作ってデータをinsertして
mysqlのデータを表示するという一覧の動作を確認できます。

https://excelwork.info/excel/databasemysql/

この部分がinsert文になります。

VBA

1 'データ追加 2 strSQL = "insert into 仕入先一覧 values " & _ 3 "('00001', '大門商店', '40012345'), " & _ 4 "('00002', '中門商店', '11012311'), " & _ 5 "('00003', '小門商店', '12012221'), " & _ 6 "('00004', '日進電器', '13422900'), " & _ 7 "('00005', '山本電気', '11199923'), " & _ 8 "('00006', '松尾商社', '23247001'), " & _ 9 "('00007', '丸得商店', '11223345'), " & _ 10 "('00008', '青葉倉庫', '12120000'), " & _ 11 "('00009', '緑銀行', '12345678'), " & _ 12 "('00010', '赤黄信用金庫', '87654321');" 13 rs.Source = strSQL

投稿2020/05/01 20:44

退会済みユーザー

退会済みユーザー

総合スコア0

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

H.K2

2020/05/01 21:35

ご回答ありがとうございます。 insert文の場合、特に長さに制限などはないのでしょうか。 上の例だと10レコードしかないのですが、将来的に5000レコードとかある場合、 すごい長いinsert文になるのですが、問題なく動きますでしょうか。
退会済みユーザー

退会済みユーザー

2020/05/01 22:04

mysqlにはCSVをテーブルに取り込む命令があります。 エクセルシートをCSV出力し、それを一括で読み込むコードを記述すれば シンプルなVBAで済むと思います。 関連する記事の(例) 1,CSVファイルをMySQLにインポートするSQL文(例) https://www.virment.com/sql-for-import-csv-mysql/ https://mugendennou.net/mysql/8_1loaddatainfile/ 2,Excelシート上のデータを DB にインポートするVBAマクロ https://language-and-engineering.hatenablog.jp/entry/20090327/p1
退会済みユーザー

退会済みユーザー

2020/05/01 22:23 編集

まとめてインポートとは少し違いますが皆さん色々されてるようですね。。 下記のケースですと、MysqlにinsertするSQLを自動で作成するVBAを紹介しています。 windowsですとコマンドプロンプトでにsqlを貼り付けて実行するパターンですね・・。 【VBA】ExcelマクロからSQLのinsert文を自動生成する方法 https://utage.headwaters.co.jp/blog/archives/2954
guest

0

ベストアンサー

6000件でinsert文を実験してみました。PCのスペックによりますがwindows10 64bit/メモリ8Gで
10秒~15秒くらいでした。
データはサンプルです。

サンプルデータベース作成

SQL

1create database db_users; 2 3use db_users; 4 5create table t_users( 6id varchar(20) not null primary key, 7name varchar(20), 8password varchar(32), 9section varchar(15) 10);

sheet:data1
データ数:6000件

|id|name|password|setion|
|ID1|ダミー1|XXXX|:YY部|
|ID2|ダミー2|XXXX|:ZZ部|

VBA

1Sub Mysql_連続insert() 2 3'ExcelでADO・ADODBへの参照設定で下記をチェック(古いバージョンでも可) 4'Microsoft ActiveX 'Data Objects 6.1 Library 5 6 7Dim cn As ADODB.Connection 8Dim rs As ADODB.Recordset 9 10Dim connectionString As String 11Dim sqlStr As String 12 13Dim id As String 14Dim name As String 15Dim password As String 16Dim section As String 17 18 19'接続文字列 20connectionString = "Driver={MySQL ODBC 8.0 ANSI Driver};" _ 21 & " SERVER=localhost;" _ 22 & " DATABASE=db_users;" _ 23 & " USER=root;" _ 24 & " PASSWORD=test;" 25 26'ADODB.Connection生成 27Set cn = New ADODB.Connection 28 29On Error GoTo Err 30 31'MySQLに接続 32cn.Open connectionString 33 34For i = 2 To 6000 35 36 With Sheets("data1") 37 id = .Range("A" & i).Value 38 name = .Range("B" & i).Value 39 password = .Range("C" & i).Value 40 section = .Range("D" & i).Value 41 42 End With 43 44 sqlStr = "insert into t_users (id,name,password,section) values ('" & id & "','" & name & "','" & password & "','" & section & "')" 45 Set rs = cn.Execute(sqlStr) 46 47Next 48 49cn.Close 50Set rs = Nothing 51Set cn = Nothing 52 53MsgBox "データの登録がおわりました" 54Exit Sub 55 56Err: 57 Set rs = Nothing 58 Set cn = Nothing 59 MsgBox (Err.Description) 60 61End Sub

投稿2020/05/02 03:30

編集2020/05/02 05:38
退会済みユーザー

退会済みユーザー

総合スコア0

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

H.K2

2020/05/02 15:00

ありがとうございます!助かりますー。
退会済みユーザー

退会済みユーザー

2020/05/02 21:46 編集

ついでに。。。今回のご質問中、Mysql接続関数(select文用)を作りました。 拡張して使ってみてください・ この例では、Excelの指定したdataシートにsqlの結果を展開します。 select文と指定シートを変えることでsqlの検証が簡単になると思います。 使い方。 Sub test() Dim sql As String sql = "select * from t_users" Call Mysql_select("data", sql)  '自作関数使い方 Mysql_select(展開したいシート名、select構文) end sub sub Mysql_select(sh As String, sql As String) 'mysql関数 Mysql_select(展開したいシート名、select構文) Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim connectionString As String Dim sqlStr As String Dim rowNo As Integer Dim colNo As Integer Dim i As Integer Dim item As Variant '接続文字列 connectionString = "Driver={MySQL ODBC 8.0 ANSI Driver};" _ & " SERVER=localhost;" _ & " DATABASE=db_users;" _ & " USER=root;" _ & " PASSWORD=;" 'ADODB.Connection生成 Set cn = New ADODB.Connection On Error GoTo Err 'MySQLに接続 cn.Open connectionString sqlStr = sql Set rs = cn.Execute(sqlStr) 'シートデータクリア Worksheets(sh).Cells.Clear rowNo = 2 colNo = 1 i = 1 '■フィールド名抽出 For Each fld In rs.Fields Worksheets(sh).Cells(1, i).Value = fld.name i = i + 1 Next '■データ抽出 Do While rs.EOF = False For Each item In rs.Fields Worksheets(sh).Cells(rowNo, colNo).Value = item.Value colNo = colNo + 1 Next colNo = 1 rowNo = rowNo + 1 rs.MoveNext Loop cn.Close Set rs = Nothing Set cn = Nothing Exit Sub Err: Set rs = Nothing Set cn = Nothing MsgBox (Err.Description) End Sub
退会済みユーザー

退会済みユーザー

2020/05/02 21:48 編集

ついでに・・・・。 手作業でエクセルシートをWindows cドライブ直下にCSV保存したとしてinportするMysqlコマンドは下記のとおりです。 1秒かからないくらいで6000件の取り込みが終わります。 load data local infile "C:\data.csv" INTO TABLE t_users FIELDS terminated by ',' enclosed by '"' escaped by '"'; mysqlについてはネットで調べてみてください。 今回の記事に関連して、下記の書籍ではExcelVBA・Userfomでmysqlクライアント管理ツールを作る 事例を紹介しています(高橋良明さんの書籍で関連書籍は3つあります)。 購入後、勉強した気になり開いていませんでしたが3冊とも家にありました。 python+pandasをご使用になられるのでしたら、テストデータ作成・開発にぜひ。 ■参考:Amazon Excelで学ぶMySQL5.0―もう迷わない!データベースの勘所 スラスラ書けるSQLプログラミングの急所 (日本語) 単行本 – 2006/5/1
H.K2

2020/05/02 23:43 編集

いろいろありがとうございますー。ちょっと買ってみます!助かります! …と思ったんですが、amazonだと中古品でめっちゃ高い値段になってますね…。 コロナが収まったら図書館で借りて読んでみます。ありがとうございました。 レコードが数万件くらいあってボトルネックが気になってて、一発でやると早くなるかな、というところが気になってたのでありがたいですー! 個人的にはpython+pandasでやりたいのですが、会社でpython使えるの僕しかいなくて、VBAで書かないといけなくて…????
退会済みユーザー

退会済みユーザー

2020/05/03 00:55 編集

あらま、高額な値段でしたか(たけーな)。。。差し上げでもいいのですが、10分ぐらいしか私も読んでいないので・・。 でも、基本的な動作のサンプルはネットを調べると色々でてきますので、 あとは、Access+excel VBAで組むADOのコードを参考にすると応用がきくかもしれませんね。 接続を確立してしまったら、あとはADOの事例に従えばいいという安直な考えでいくとうまくいきます。 ですから、今回の事例はMYSQLの事例なのですが私はADOの事例を調べました。 ・・・。私も素人ですので、コツみたいなものということで勘弁してくださいませ。 python100本ノックとか本を買ったりと私もpython好きです。 最近はLinux mintに開発環境を構築したりして(満足していじっていない) VBAに似た感じでしょうか・・・。
H.K2

2020/05/03 18:36

了解しました。ADOの事例を調べつつやってみますーありがとうございます。 pythonはコードが読みやすくて好きですね。VBAはどうもなんか癖のようなもの(+前提とか?)が強くてまだまだ苦手感があります…。 #というか、うちの会社の先輩のコードがすごい汚くて、それで苦手になったところが大きいような… (マテ 一応最近RubberDuckを入れて、VBE環境を少しだけ強化したんですが、pycharmとかVSCodeみたいな レベルには全然ならなくて…????(RubberDuckは、日本語の紹介サイトあまりなくて、いまいちよくわからないですし…)
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問