Excelシートを読み取って,INSERT文SQLを生成するバッチ (Kingsoft SpreadsheetをWSH/JScriptで自動操作)
「Excelの表データを読み取って,DBへのデータ投入SQLを生成するマクロ」
は,あちこちにある。
ExcelからSQLのINSERT文を作成するマクロ
http://d.hatena.ne.jp/grachro/2011061...
エクセルで作成したデータからinsertSQLを作成するマクロ
http://t-daman.seesaa.net/article/111...
しかし,MS Office以外のオフィスソフトのパクリ(Kingsoft Office等)を使っている場合,VBAマクロが動かない。
その時点で,開発者の観点からするとオフィスソフトとして失格なのだが,
そういう場合でもなんとかして表データを読み取って,作業を自動化したい。
Kingsoftの場合,VBAマクロは実行できないが,幸いオートメーション用のCOMが公開されている。
VBAマクロではなく,外部バッチからファイルの中身を読み取ればよい。
下記は,そのようなバッチ。
create_sql.js
/* Excelシート(Kingsoft Spreadsheet)を読み取って,INSERT文SQLを生成するバッチ */ // 読み取り対象のファイル var filename = "テストデータ.xls"; var ws = WScript.CreateObject("WScript.Shell"); var cwd = ws.CurrentDirectory; var filepath = cwd + "\\" + filename; var file_to = cwd + "\\data.sql"; // Excel(Kingsoft Spreadsheet)を立ち上げる var excel = WScript.CreateObject("ET.Application"); // http://mzex.wordpress.com/2006/11/11/kingsoft-office-2007-%E3%81%AF%E3%83%9F%E3%83%89%E3%83%AB%E3%82%A6%E3%82%A7%E3%82%A2%E3%81%AE%E5%A4%A2%E3%82%92%E8%A6%8B%E3%82%8B%E3%81%8B%EF%BC%9F/ excel.Visible = true; // ブックを開く excel.Workbooks.Open( filepath ); var book = excel.Workbooks( excel.Workbooks.Count ); // http://language-and-engineering.hatenablog.jp/entry/20090717/p1 // 全シートをスキャン var sheets = book.WorkSheets; var e = new Enumerator( sheets ); // http://language-and-engineering.hatenablog.jp/entry/20110921/p1 var str_sql = "-- AUTO-GENERATED DML\r\n\r\n"; for( ; ! e.atEnd() ; e.moveNext() ) { // シートを取得 var sheet = e.item(); //WScript.Echo( sheet.Name ); // このシートからSQLを生成 str_sql += createSqlFromSheet( sheet ); } // Excelを閉じて終了 excel.Quit(); excel = null; // SQLをファイルに記録 var fso_w = WScript.CreateObject( "Scripting.FileSystemObject" ); if( fso_w.FileExists( file_to ) ) { fso_w.DeleteFile( file_to ); } var txt_w = fso_w.CreateTextFile( file_to ); txt_w.WriteLine( str_sql ); txt_w.Close(); WScript.Echo("SQL書き出し完了"); // ------ // あるシートからSQLを作成 function createSqlFromSheet( sheet ) { var str_sql = "INSERT INTO `" + sheet.Name + "` (" ; // 列名をスキャン var continue_flag = true; var col_num = 0; var x = 1; while( continue_flag ) { col_num ++; str_sql += "`" + sheet.Cells( 1, x ).Value.replace( / /g, "" ) + "`" ; //WScript.Echo( str_sql ); x ++; if( sheet.Cells( 1, x ).Value == undefined ) { continue_flag = false; } else { str_sql += ", "; } } str_sql += ") VALUES \r\n"; //WScript.Echo( "col_num = " + col_num ); // データをスキャン var y = 2; var continue_y_flag = true; while( continue_y_flag ) { str_sql += "("; // 列ごとに for( var x = 1; x <= col_num; x ++ ) { var val = sheet.Cells( y, x ).Value; // 空か if( val == undefined ) { val = ''; } else // 数字オンリーか if( ("" + val).match( /^[0-9]+$/g ) ) { // } else if( val == "NULL" ) { // } else { val = "'" + val + "'"; } str_sql += val; if( x < col_num ) { str_sql += ", "; //WScript.Echo("added comma. x = " + x); } } str_sql += ")"; // 次の行 y ++; if( sheet.Cells( y, 1 ).Value == undefined ) { continue_y_flag = false; str_sql += ";\r\n\r\n"; } else { str_sql += ",\r\n"; } } return str_sql; }
ダブルクリックするだけで,該当するファイル名のシートを読み取って,SQLを生成してくれる。
バッチ仕様:
- シート名がテーブル名。
- 各シートの1行目はカラム名を書く。
- 2行目以降にデータを書く。
補足
データの作成作業自体は,下記エントリを参照のこと。
大量のテストデータを快適に作る7つのコツ - 負荷テストのためにExcel&VBAをうまく使う
http://language-and-engineering.hatenablog.jp/entry/20080929/1222698370