VBScript で Excel
Excel のテーブル定義シートから、CREATE TABLE 文を 作成する (SQL Server 用)
↓ こんな Excel ファイルを読む
Sheet1
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | テーブル名 | |||||||||
| 2 | 仕入先マスタ | M_SIIRE | ||||||||
| 3 | ||||||||||
| 4 | No. | 項目名 | ID | 型 | 桁数 | 小数 | 必須 | 主キー | 初期値 | 備考 |
| 5 | 1 | 仕入先コード | SIIRESAKI_CD | int | ○ | ○ | ||||
| 6 | 2 | 仕入先名 | SIIRESAKI_NM | varchar | 60 | |||||
Sheet2
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | テーブル名 | |||||||||
| 2 | 仕入伝票(ヘッダ) | T_SIIRE_HEADER | ||||||||
| 3 | ||||||||||
| 4 | No. | 項目名 | ID | 型 | 桁数 | 小数 | 必須 | 主キー | 初期値 | 備考 |
| 5 | 1 | 伝票番号 | DENPYO_NO | int | ○ | ○ | ||||
| 6 | 2 | 仕入先コード | SIIRESAKI_CD | int | ○ | |||||
| 7 | 3 | 仕入年 | SIIRE_YYYY | int | ○ | |||||
| 8 | 4 | 仕入月 | SIIRE_MM | int | ○ | |||||
| 9 | 5 | 仕入日 | SIIRE_DD | int | ○ | |||||
Sheet3
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | テーブル名 | |||||||||
| 2 | 仕入伝票(明細) | T_SIIRE_MEISAI | ||||||||
| 3 | ||||||||||
| 4 | No. | 項目名 | ID | 型 | 桁数 | 小数 | 必須 | 主キー | 初期値 | 備考 |
| 5 | 1 | 伝票番号 | DENPYO_NO | int | ○ | ○ | ||||
| 6 | 2 | 行 | GYO | int | ○ | ○ | ||||
| 7 | 3 | 商品ＣＤ | SHOHIN_CD | varchar | 13 | |||||
| 8 | 4 | 単価 | TANKA | decimal | 9 | 2 | 0 | |||
| 9 | 5 | 数量 | SURYO | decimal | 9 | 2 | 0 | |||
| 10 | 6 | 金額 | KINGAKU | decimal | 19 | 2 | 0 | |||
| 11 | 7 | 税区分 | ZEI_KBN | tinyint | ||||||
| 12 | 8 | 消費税 | SHOHIZEI | decimal | 9 | 2 | 0 | |||
lesson001.vbs
Option ExplicitDim excelApp: Set excelApp = CreateObject("Excel.Application")
excelApp.Visible = True
excelApp.DisplayAlerts = False
Dim book: Set book = excelApp.Workbooks.Open(WScript.Arguments(0))Dim fs: Set fs = CreateObject("Scripting.FileSystemObject")
Dim folderName: folderName = WScript.Arguments(1)
If Right(folderName, 1) <> "\" Then
folderName = folderName + "\"
End IfDim ColTableID: ColTableID = 6
Dim RowTableID: RowTableID = 2Dim ColTableName: ColTableName = 1
Dim RowTableName: RowTableName = 2Dim RowTop: RowTop = 5
Dim ColFieldName: ColFieldName = 2
Dim ColFieldID: ColFieldID = 3
Dim ColDataType: ColDataType = 4
Dim ColPrecision: ColPrecision = 5
Dim ColScale: ColScale = 6
Dim ColNullable: ColNullable = 7
Dim ColPrimaryKey: ColPrimaryKey = 8
Dim ColDefault: ColDefault = 9
Dim ColRemark: ColRemark = 10Dim idxSheet
For idxSheet = 1 To book.Sheets.Count
Dim sheet: Set sheet = book.Sheets(idxSheet)
Dim tableID: tableID = sheet.Cells(RowTableID, ColTableID)
Dim tableName: tableName = sheet.Cells(RowTableName, ColTableName)Dim ts: Set ts = fs.OpenTextFile(folderName + "create_table\" + tableID + ".sql", 2, True) '2 = ForWriting
Call makeCreateTable(sheet, tableName, tableID, ts)
ts.Close
Set ts = NothingSet sheet = Nothing
NextSet fs = Nothing
excelApp.Quit
Set excelApp = NothingSub makeCreateTable(sheet, tableName, tableID, ts)
ts.WriteLine "--******************************************************************************"
ts.WriteLine "-- " + tableName
ts.WriteLine "--******************************************************************************"'テーブル削除
ts.WriteLine "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + tableID + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)"
ts.WriteLine "drop table [dbo].[" + tableID + "]"
ts.WriteLine "GO"
ts.WriteLine ""'テーブル作成
ts.WriteLine "CREATE TABLE"
ts.WriteLine " [dbo].[" + tableID + "]"
ts.WriteLine "("
Dim row: row = RowTop
Do
'項目名
If Not sheet.Cells(row, ColFieldID).Font.Strikethrough Then
Dim fieldName: fieldName = Trim(sheet.Cells(row, ColFieldID))
If fieldName = "" Then Exit DoIf row = RowTop Then
ts.Write " "
Else
ts.Write ", "
End If
ts.Write Left(fieldName + Space(32), 32)'データ型
Dim fieldType: fieldType = Trim(sheet.Cells(row, ColDataType))
ts.Write Left(fieldType + Space(16), 16)'桁数
Dim fieldLength: fieldLength = ""
Dim precision
Select Case LCase(fieldType)
Case "varchar", "char"
precision = Trim(sheet.Cells(row, ColPrecision))
fieldLength = "(" + precision + ")"Case "decimal"
precision = Trim(sheet.Cells(row, ColPrecision))Dim scale: scale = Trim(sheet.Cells(row, ColScale))
If scale = "" Then
scale = "0"
End If
fieldLength = "(" + precision + "," + scale + ")"
End Select
ts.Write Left(fieldLength + Space(8), 8)'NULL 許可
Dim nullable: nullable = Trim(sheet.Cells(row, ColNullable))
If nullable = "○" Then
ts.Write "NOT NULL "
Else
ts.Write "NULL "
End If'初期値
Dim default: default = Trim(sheet.Cells(row, ColDefault))
If default <> "" Then
Select Case LCase(fieldType)
Case "varchar", "char", "datetime", "smalldatetime"
ts.Write Left("DEFAULT ('" + default + "')" + Space(16), 16)Case Else
ts.Write Left("DEFAULT (" + default + ")" + Space(16), 16)
End Select
Else
ts.Write Space(16)
End If'項目名
ts.Write "-- "
Dim remark: remark = Trim(sheet.Cells(row, ColFieldName))
ts.Write remark + Space(32 - LenA(remark))'備考
remark = Trim(sheet.Cells(row, ColRemark))
ts.Write remarkts.WriteLine ""
End If
row = row + 1
Loopts.WriteLine ")"
ts.WriteLine "ON [PRIMARY]"
ts.WriteLine "GO"
ts.WriteLine ""'主キー作成
ts.WriteLine "ALTER TABLE"
ts.WriteLine " [dbo].[" + tableID + "]"
ts.WriteLine "ADD CONSTRAINT"
ts.WriteLine " [PK_" + tableID + "] PRIMARY KEY NONCLUSTERED"
ts.WriteLine "("row = RowTop
Dim i: i = 0
Do
'項目名
If Not sheet.Cells(row, ColFieldID).Font.Strikethrough Then
fieldName = Trim(sheet.Cells(row, ColFieldID))
If fieldName = "" Then Exit DoDim primaryKey: primaryKey = Trim(sheet.Cells(row, ColPrimaryKey))
If primaryKey = "○" Then
'項目名
If i = 0 Then
ts.Write " "
i = i + 1
Else
ts.Write ", "
End If
ts.Write Left(fieldName + Space(32), 32)'備考
ts.Write "-- "
remark = Trim(sheet.Cells(row, ColFieldName))
ts.Write remarkts.WriteLine ""
End If
End If
row = row + 1
Loopts.WriteLine ")"
ts.WriteLine "ON [PRIMARY]"
ts.WriteLine "GO"
End SubFunction LenA(s)
Dim result: result = 0Dim i
For i = 1 To Len(s)
If (Asc(Mid(s, i, 1)) And &HFF00) = 0 Then
result = result + 1
Else
result = result + 2
End If
NextLenA = result
End Function
スーパーpre記法 を 試してみる
Option Explicit Dim excelApp: Set excelApp = CreateObject("Excel.Application") excelApp.Visible = True excelApp.DisplayAlerts = False Dim book: Set book = excelApp.Workbooks.Open(WScript.Arguments(0)) Dim fs: Set fs = CreateObject("Scripting.FileSystemObject") Dim folderName: folderName = WScript.Arguments(1) If Right(folderName, 1) <> "\" Then folderName = folderName + "\" End If Dim ColTableID: ColTableID = 6 Dim RowTableID: RowTableID = 2 Dim ColTableName: ColTableName = 1 Dim RowTableName: RowTableName = 2 Dim RowTop: RowTop = 5 Dim ColFieldName: ColFieldName = 2 Dim ColFieldID: ColFieldID = 3 Dim ColDataType: ColDataType = 4 Dim ColPrecision: ColPrecision = 5 Dim ColScale: ColScale = 6 Dim ColNullable: ColNullable = 7 Dim ColPrimaryKey: ColPrimaryKey = 8 Dim ColDefault: ColDefault = 9 Dim ColRemark: ColRemark = 10 Dim idxSheet For idxSheet = 1 To book.Sheets.Count Dim sheet: Set sheet = book.Sheets(idxSheet) Dim tableID: tableID = sheet.Cells(RowTableID, ColTableID) Dim tableName: tableName = sheet.Cells(RowTableName, ColTableName) Dim ts: Set ts = fs.OpenTextFile(folderName + "create_table\" + tableID + ".sql", 2, True) '2 = ForWriting Call makeCreateTable(sheet, tableName, tableID, ts) ts.Close Set ts = Nothing Set sheet = Nothing Next Set fs = Nothing excelApp.Quit Set excelApp = Nothing Sub makeCreateTable(sheet, tableName, tableID, ts) ts.WriteLine "--******************************************************************************" ts.WriteLine "-- " + tableName ts.WriteLine "--******************************************************************************" 'テーブル削除 ts.WriteLine "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + tableID + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)" ts.WriteLine "drop table [dbo].[" + tableID + "]" ts.WriteLine "GO" ts.WriteLine "" 'テーブル作成 ts.WriteLine "CREATE TABLE" ts.WriteLine " [dbo].[" + tableID + "]" ts.WriteLine "(" Dim row: row = RowTop Do '項目名 If Not sheet.Cells(row, ColFieldID).Font.Strikethrough Then Dim fieldName: fieldName = Trim(sheet.Cells(row, ColFieldID)) If fieldName = "" Then Exit Do If row = RowTop Then ts.Write " " Else ts.Write ", " End If ts.Write Left(fieldName + Space(32), 32) 'データ型 Dim fieldType: fieldType = Trim(sheet.Cells(row, ColDataType)) ts.Write Left(fieldType + Space(16), 16) '桁数 Dim fieldLength: fieldLength = "" Dim precision Select Case LCase(fieldType) Case "varchar", "char" precision = Trim(sheet.Cells(row, ColPrecision)) fieldLength = "(" + precision + ")" Case "decimal" precision = Trim(sheet.Cells(row, ColPrecision)) Dim scale: scale = Trim(sheet.Cells(row, ColScale)) If scale = "" Then scale = "0" End If fieldLength = "(" + precision + "," + scale + ")" End Select ts.Write Left(fieldLength + Space(8), 8) 'NULL 許可 Dim nullable: nullable = Trim(sheet.Cells(row, ColNullable)) If nullable = "○" Then ts.Write "NOT NULL " Else ts.Write "NULL " End If '初期値 Dim default: default = Trim(sheet.Cells(row, ColDefault)) If default <> "" Then Select Case LCase(fieldType) Case "varchar", "char", "datetime", "smalldatetime" ts.Write Left("DEFAULT ('" + default + "')" + Space(16), 16) Case Else ts.Write Left("DEFAULT (" + default + ")" + Space(16), 16) End Select Else ts.Write Space(16) End If '項目名 ts.Write "-- " Dim remark: remark = Trim(sheet.Cells(row, ColFieldName)) ts.Write remark + Space(32 - LenA(remark)) '備考 remark = Trim(sheet.Cells(row, ColRemark)) ts.Write remark ts.WriteLine "" End If row = row + 1 Loop ts.WriteLine ")" ts.WriteLine "ON [PRIMARY]" ts.WriteLine "GO" ts.WriteLine "" '主キー作成 ts.WriteLine "ALTER TABLE" ts.WriteLine " [dbo].[" + tableID + "]" ts.WriteLine "ADD CONSTRAINT" ts.WriteLine " [PK_" + tableID + "] PRIMARY KEY NONCLUSTERED" ts.WriteLine "(" row = RowTop Dim i: i = 0 Do '項目名 If Not sheet.Cells(row, ColFieldID).Font.Strikethrough Then fieldName = Trim(sheet.Cells(row, ColFieldID)) If fieldName = "" Then Exit Do Dim primaryKey: primaryKey = Trim(sheet.Cells(row, ColPrimaryKey)) If primaryKey = "○" Then '項目名 If i = 0 Then ts.Write " " i = i + 1 Else ts.Write ", " End If ts.Write Left(fieldName + Space(32), 32) '備考 ts.Write "-- " remark = Trim(sheet.Cells(row, ColFieldName)) ts.Write remark ts.WriteLine "" End If End If row = row + 1 Loop ts.WriteLine ")" ts.WriteLine "ON [PRIMARY]" ts.WriteLine "GO" End Sub Function LenA(s) Dim result: result = 0 Dim i For i = 1 To Len(s) If (Asc(Mid(s, i, 1)) And &HFF00) = 0 Then result = result + 1 Else result = result + 2 End If Next LenA = result End Function
実行形式
C:\>cscript c:\study\vbscript\chapter006\lesson001.vbs c:\study\テーブル定義.xls
c:\study\vbscript\chapter006 //nologo
結果:
M_SIIRE.sql
--******************************************************************************
-- 仕入先マスタ
--******************************************************************************
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[M_SIIRE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[M_SIIRE]
GOCREATE TABLE
[dbo].[M_SIIRE]
(
SIIRESAKI_CD int NOT NULL -- 仕入先コード
, SIIRESAKI_NM varchar (60) NULL -- 仕入先名
)
ON [PRIMARY]
GOALTER TABLE
[dbo].[M_SIIRE]
ADD CONSTRAINT
[PK_M_SIIRE] PRIMARY KEY NONCLUSTERED
(
SIIRESAKI_CD -- 仕入先コード
)
ON [PRIMARY]
GO
T_SIIRE_HEADER.sql
--******************************************************************************
-- 仕入伝票(ヘッダ)
--******************************************************************************
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_SIIRE_HEADER]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_SIIRE_HEADER]
GOCREATE TABLE
[dbo].[T_SIIRE_HEADER]
(
DENPYO_NO int NOT NULL -- 伝票番号
, SIIRESAKI_CD int NOT NULL -- 仕入先コード
, SIIRE_YYYY int NOT NULL -- 仕入年
, SIIRE_MM int NOT NULL -- 仕入月
, SIIRE_DD int NOT NULL -- 仕入日
)
ON [PRIMARY]
GOALTER TABLE
[dbo].[T_SIIRE_HEADER]
ADD CONSTRAINT
[PK_T_SIIRE_HEADER] PRIMARY KEY NONCLUSTERED
(
DENPYO_NO -- 伝票番号
)
ON [PRIMARY]
GO
T_SIIRE_MEISAI.sql
--******************************************************************************
-- 仕入伝票(明細)
--******************************************************************************
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_SIIRE_MEISAI]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_SIIRE_MEISAI]
GOCREATE TABLE
[dbo].[T_SIIRE_MEISAI]
(
DENPYO_NO int NOT NULL -- 伝票番号
, GYO int NOT NULL -- 行
, SHOHIN_CD varchar (13) NULL -- 商品ＣＤ
, TANKA decimal (9,2) NULL DEFAULT (0) -- 単価
, SURYO decimal (9,2) NULL DEFAULT (0) -- 数量
, KINGAKU decimal (19,2) NULL DEFAULT (0) -- 金額
, ZEI_KBN tinyint NULL -- 税区分
, SHOHIZEI decimal (9,2) NULL DEFAULT (0) -- 消費税
)
ON [PRIMARY]
GOALTER TABLE
[dbo].[T_SIIRE_MEISAI]
ADD CONSTRAINT
[PK_T_SIIRE_MEISAI] PRIMARY KEY NONCLUSTERED
(
DENPYO_NO -- 伝票番号
, GYO -- 行
)
ON [PRIMARY]
GO
