自分用 CREATE文自動生成
ChatGPTが全部やってくれました。
VBAで動かす想定。
Option Explicit
Sub GenerateSQLCreateStatements()
Dim inputFolder As String
Dim outputFolder As String
Dim filePath As String
Dim outputFilePath As String
Dim fileContent As String
Dim sqlCreateStatement As String
Dim textLine As String
Dim schemaName As String
Dim tableName As String
Dim columnName As String
Dim dataTypeNumber As String
Dim dataType As String
Dim stringLength As String
Dim intLength As String
Dim decimalLength As String
Dim columnDefinition As String
Dim fso As Object
Dim inputFile As Object
Dim outputFile As Object
Dim file As Object
Dim wbPath As String
Dim logSheet As Worksheet
Dim logRow As Long
' ログシートの準備
On Error Resume Next
Set logSheet = ThisWorkbook.Sheets("Log")
If logSheet Is Nothing Then
Set logSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
logSheet.Name = "Log"
End If
On Error GoTo 0
logSheet.Cells.Clear
logRow = 1
logSheet.Cells(logRow, 1).Value = "Processing Log"
logRow = logRow + 1
' 現在のワークブックのパスを取得
wbPath = ThisWorkbook.Path
' 入力フォルダと出力フォルダのパスを設定
inputFolder = wbPath & "\In\"
outputFolder = wbPath & "\Out\"
' データ型番号とデータ型名のマッピング
Dim dataTypeMap As Object
Set dataTypeMap = CreateObject("Scripting.Dictionary")
dataTypeMap.Add "56", "INT"
dataTypeMap.Add "61", "DATETIME"
dataTypeMap.Add "62", "FLOAT"
dataTypeMap.Add "104", "BIT"
dataTypeMap.Add "106", "DECIMAL"
dataTypeMap.Add "108", "NUMERIC"
dataTypeMap.Add "167", "VARCHAR"
dataTypeMap.Add "231", "NVARCHAR"
dataTypeMap.Add "175", "CHAR"
dataTypeMap.Add "239", "NCHAR"
dataTypeMap.Add "40", "DATE"
dataTypeMap.Add "41", "TIME"
dataTypeMap.Add "58", "SMALLDATETIME"
dataTypeMap.Add "59", "REAL"
dataTypeMap.Add "48", "TINYINT"
dataTypeMap.Add "52", "SMALLINT"
dataTypeMap.Add "127", "BIGINT"
dataTypeMap.Add "173", "BINARY"
dataTypeMap.Add "165", "VARBINARY"
dataTypeMap.Add "36", "UNIQUEIDENTIFIER"
dataTypeMap.Add "34", "IMAGE"
dataTypeMap.Add "35", "TEXT"
dataTypeMap.Add "99", "NTEXT"
dataTypeMap.Add "122", "SMALLMONEY"
dataTypeMap.Add "60", "MONEY"
' ファイルシステムオブジェクトを作成
Set fso = CreateObject("Scripting.FileSystemObject")
' 入力フォルダのファイルをループ
For Each file In fso.GetFolder(inputFolder).Files
filePath = file.Path
logSheet.Cells(logRow, 1).Value = "Processing file: " & filePath
logRow = logRow + 1
' インプットファイルを開く
Set inputFile = fso.OpenTextFile(filePath, 1)
' SQL Create文の初期化
sqlCreateStatement = ""
' ファイル内容を読み込み
Do While Not inputFile.AtEndOfStream
textLine = inputFile.ReadLine
logSheet.Cells(logRow, 1).Value = "Read line: " & textLine
logRow = logRow + 1
If textLine <> "" Then
' タブで区切られた情報を取得
schemaName = Split(textLine, vbTab)(0)
tableName = Split(textLine, vbTab)(1)
columnName = Split(textLine, vbTab)(2)
dataTypeNumber = Split(textLine, vbTab)(3)
stringLength = Split(textLine, vbTab)(4)
intLength = Split(textLine, vbTab)(5)
decimalLength = Split(textLine, vbTab)(6)
' データ型番号をデータ型名に変換
If dataTypeMap.Exists(dataTypeNumber) Then
dataType = dataTypeMap(dataTypeNumber)
Else
dataType = "UNKNOWN"
End If
logSheet.Cells(logRow, 1).Value = "Data type converted: " & dataType
logRow = logRow + 1
' カラム定義の作成
Select Case dataType
Case "VARCHAR", "CHAR", "NVARCHAR", "NCHAR"
columnDefinition = "[" & columnName & "] " & dataType & "(" & stringLength & ") NULL"
Case "DECIMAL", "NUMERIC"
columnDefinition = "[" & columnName & "] " & dataType & "(" & intLength & "," & decimalLength & ") NULL"
Case Else
columnDefinition = "[" & columnName & "] " & dataType & " NULL"
End Select
' SQL Create文の構築
If sqlCreateStatement = "" Then
sqlCreateStatement = "CREATE TABLE [" & schemaName & "].[" & tableName & "] (" & vbCrLf
Else
sqlCreateStatement = sqlCreateStatement & "," & vbCrLf
End If
sqlCreateStatement = sqlCreateStatement & " " & columnDefinition
logSheet.Cells(logRow, 1).Value = "Column definition: " & columnDefinition
logRow = logRow + 1
End If
Loop
' ファイルの終了処理
inputFile.Close
sqlCreateStatement = sqlCreateStatement & vbCrLf & ");"
' 出力ファイルのパスを設定
outputFilePath = outputFolder & schemaName & "." & tableName & ".sql"
' 出力ファイルに書き込み
Set outputFile = fso.CreateTextFile(outputFilePath, True)
outputFile.Write sqlCreateStatement
outputFile.Close
logSheet.Cells(logRow, 1).Value = "SQL Create statement written to: " & outputFilePath
logRow = logRow + 1
Next file
MsgBox "Processing complete. Check the Log sheet for details.", vbInformation
End Sub
想定インプットファイルの書式
schema1 table1 column1 56 0 0 0
schema1 table1 column2 61 0 0 0
schema1 table1 column3 62 0 0 0
schema1 table1 column4 104 0 0 0
schema1 table1 column5 106 18 10 0
schema1 table1 column6 108 18 10 0
schema1 table1 column7 167 50 0 0
schema1 table1 column8 231 50 0 0
schema1 table1 column9 175 10 0 0
schema1 table1 column10 239 10 0 0
schema1 table1 column11 40 0 0 0
schema1 table1 column12 41 0 0 0
schema1 table1 column13 58 0 0 0
schema1 table1 column14 59 0 0 0
schema1 table1 column15 48 0 0 0
schema1 table1 column16 52 0 0 0
schema1 table1 column17 127 0 0 0
schema1 table1 column18 173 50 0 0
schema1 table1 column19 165 50 0 0
schema1 table1 column20 36 0 0 0
schema1 table1 column21 34 0 0 0
schema1 table1 column22 35 0 0 0
schema1 table1 column23 99 0 0 0
schema1 table1 column24 122 0 0 0
schema1 table1 column25 60 0 0 0
実行結果
CREATE TABLE [schema1].[table1] (
[column1] INT NULL,
[column2] DATETIME NULL,
[column3] FLOAT NULL,
[column4] BIT NULL,
[column5] DECIMAL(10,0) NULL,
[column6] NUMERIC(10,0) NULL,
[column7] VARCHAR(50) NULL,
[column8] NVARCHAR(50) NULL,
[column9] CHAR(10) NULL,
[column10] NCHAR(10) NULL,
[column11] DATE NULL,
[column12] TIME NULL,
[column13] SMALLDATETIME NULL,
[column14] REAL NULL,
[column15] TINYINT NULL,
[column16] SMALLINT NULL,
[column17] BIGINT NULL,
[column18] BINARY NULL,
[column19] VARBINARY NULL,
[column20] UNIQUEIDENTIFIER NULL,
[column21] IMAGE NULL,
[column22] TEXT NULL,
[column23] NTEXT NULL,
[column24] SMALLMONEY NULL,
[column25] MONEY NULL
);
ファイル名だけ変えたい
Sub RenameFilesWithSuffix()
Dim wb As Workbook
Dim ws As Worksheet
Dim folderPath As String
Dim inFolderPath As String
Dim outFolderPath As String
Dim file As Object
Dim fso As Object
Dim fileName As String
Dim newFileName As String
Dim suffix As String
' Suffix to be added to the filenames
suffix = "_suffix" ' 任意の文字列に置き換えてください
' Set workbook and worksheet
Set wb = ThisWorkbook
Set ws = wb.Sheets(1)
' Get the folder path where the Excel file is located
folderPath = wb.Path & "\"
' Set In and Out folder paths
inFolderPath = folderPath & "In\"
outFolderPath = folderPath & "Out\"
' Create FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")
' Check if In and Out folders exist
If Not fso.FolderExists(inFolderPath) Then
MsgBox "Inフォルダが存在しません: " & inFolderPath, vbExclamation
Exit Sub
End If
If Not fso.FolderExists(outFolderPath) Then
' Create Out folder if it doesn't exist
fso.CreateFolder outFolderPath
End If
' Loop through all files in the In folder
For Each file In fso.GetFolder(inFolderPath).Files
' Get the original file name
fileName = fso.GetBaseName(file.Name)
' Get the file extension
fileExtension = fso.GetExtensionName(file.Name)
' Create the new file name with the suffix
newFileName = fileName & suffix & "." & fileExtension
' Copy the file to the Out folder with the new name
fso.CopyFile file.Path, outFolderPath & newFileName, True
Next file
' Inform the user that the process is complete
MsgBox "ファイル名にサフィックスを追加してOutフォルダに出力しました。", vbInformation
End Sub
この記事が気に入ったらサポートをしてみませんか?