VB6 使用 Schema.ini 匯入文字檔

這幾天在寫 SQLite 的連結,才想到之前用 VB6 寫的文字檔匯入程式.人老了怕忘記原始碼放哪,就放上來,順便有需要的就拿去用吧 XD

Schema.ini 裡面存的內容

[Import.txt]
Format = FixedLength
TextDelimiter = none
ColNameHeader = False
MaxScanRows = 0
Col1="OP_NO1" Text Width 6
Col2="OP_NO2" Text Width 8
Col3="OP_NAME1" Text Width 10
Col4="OP_NAME2" Text Width 8
Col5="OP_LEVEL" Text Width 1
Col6="OP_TYPE" Text Width 1

'匯入文字檔使用 Schema.ini 範例

Private Sub ADO_TestText()
    'Schema.ini 檔案 (文字檔驅動程式) 說明網址
    'https://learn.microsoft.com/zh-tw/sql/odbc/microsoft/schema-ini-file-text-file-driver?view=sql-server-ver16
    Dim Cn As New ADODB.Connection
    Dim strConnecting As String
    '檔案設定
    Dim strDir As String
    Dim strTableName As String
    Dim strTextFileName As String
    
    Dim T1 As Double
    Dim T2 As Double
    
    '設定路徑 , 該路徑一定要有 Schema.ini 檔案
    strDir = App.Path
    If Right(strDir, 1) <> "\" Then strDir = strDir & "\"
    '設定寫入資料庫 TABEL
    strTableName = "NEW_TABLE"
    '設定要轉入的文字檔
    strTextFileName = "Import.txt"
    
    strConnecting = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & strDir & "MASTER.MDB;Jet OLEDB:Database Password=1234;"
    Cn.ConnectionString = strConnecting
    
    T1 = Timer
    
    Cn.Open
    '執行文字檔轉入
    Cn.Execute "SELECT * INTO " & strTableName & " FROM [" & strTextFileName & "] IN '" & strDir & "' 'TEXT;'", , adCmdText Or adExecuteNoRecords
    Cn.Close
    
    T2 = Timer
    
    Call MsgBox("轉入完成,秒數:" & T2 - T1)
End Sub