自分用　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