Windows 10 Pro x64 + Excel2016 x86 VBA で Ubuntu 16.04 + SQL Server 2017 Express に BULK INSERT で書き込む #ExcelVBA

目的

ある程度の行数を1行づつ書き込むのにも飽きたので、BULK INSERT を使うことにする
1行ごとに書いてinvalidなデータでエラーが発生した場合、ログを吐いて止まればいい
と思ったりもするので迷うところではある
以下の書き込みの作業済
Ubuntu 16.04 x64 で SQL Server2017 Express を試してみる
 Windows 10 Pro x64 + Excel2016 x86 VBA で SQL Server 2016 Express にCSVファイルを書き込んでみる
 Ubuntu 16.04 x64 に samba をインストールしてみる

参考にしたのは以下のサイト

１．CSV ファイルを BULK INSERT を使ってインポートする
２．UTF-8 形式のテキストファイルを BULK INSERT で一括取り込みする( SQL Server )
３．プログラミングガイドライン
４．BULK INSERT (Transact-SQL)
※最初は１．の
　　FIELDTERMINATOR = ',',
　　ROWTERMINATOR = '\n'
　で出来たと思ったのだけど、SSMSから叩くと、文字化けが発生している
　
※２．を見て　UTF-8　でいけるか
　　DATAFILETYPE='char',
　　CODEPAGE = '65001',
　　FIELDTERMINATOR = ','
　と思ったら、４．には Linux版はCODEPAGEに未対応と明記してある・・・
　
※結局、２．３．に書いてある
　　DATAFILETYPE='widechar'、
　　FIELDTERMINATOR = ','
　で Unicode に変換したテキストを流すという経緯
※UTF-8 VS Unicode って相互変換不可のコードがあった気が
　
※ネットワーク越しにファイルに書き込む速度が遅すぎなので
　ローカルでファイルを作成後、コピーする
　
対象データは郵便番号検索の17ISHIKA.CSV

サンプルテーブル

データの並びは郵便番号データの説明に準拠
対象データにユニークキーになりそうなデータが無いので
8桁のテキストを追加する
※Accessかリンクを張るときにもユニークキーは必須なので

CREATE TABLE [dbo].[ZIPCODE](
    [SEQ] [nchar](8) NOT NULL,
    [PREFCODE] [nchar](3) NULL,
    [KUBUNCODE] [nchar](8) NULL,
    [POSTAL5] [nchar](5) NULL,
    [POSTAL] [nchar](8) NULL,
    [PREFKANA] [nchar](20) NULL,
    [CITIESKANA] [nchar](40) NULL,
    [POADDRKANA] [nchar](80) NULL,
    [PREFKANJI] [nchar](20) NULL,
    [CITIESKANJI] [nchar](40) NULL,
    [POADDRKANJI] [nchar](80) NULL,
    [FLG1] [int] NULL,
    [FLG2] [int] NULL,
    [FLG3] [int] NULL,
    [FLG4] [int] NULL,
    [FLG5] [int] NULL,
    [FLG6] [int] NULL
) ON [PRIMARY]

サンプルコード


Option Explicit

Private Sub CommandButton1_Click()
On Error GoTo ErrProc
'
    Dim DB          As DBCtrl
    Dim vAry        As Variant
    Dim blnRet      As Boolean
    Dim fd          As Integer
    Dim trLevel     As Integer
    Dim lngCnt      As Long
    Dim strSQL      As String
    Dim strLine     As String
    Dim fName       As String
    Dim oName       As String
    Dim rName       As String
    Dim bName       As String
    Dim adoSt As Object
    Set adoSt = CreateObject("ADODB.Stream")
'
    fName = "C:\Dev\code\excelvb\17ISHIKA.CSV"
    oName = "C:\Dev\code\excelvb\BULK.TXT"
    rName = "\\ip-addr\share\sqlsvbulk\BULK.TXT"
    bName = "/var/export/share/sqlsvbulk/BULK.TXT"
'
    fd = FreeFile
    Open fName For Input As #fd
'
    With adoSt
        .Charset = "Unicode"
        .LineSeparator = adCRLF
        .Open
'
        While Not EOF(fd)
            lngCnt = lngCnt + 1
            Line Input #fd, strLine
            strLine = Replace(strLine, """", "")
            vAry = Split(strLine, ",")
            strLine = Right(String(8, "0") & _
                          CStr(lngCnt), 8) & "," & _
                          Trim(Left(vAry(0), 2)) & "," & strLine
            .WriteText strLine, adWriteLine
        Wend
'
        .SaveToFile oName, adSaveCreateOverWrite
        .Close

    End With
    Close #fd
'
    FileCopy oName, rName
'
    Set DB = New DBCtrl
'
    DB.DBName = "SQLSV2UBUNTU"
    DB.UserName = "demo"
    DB.Password = "demo"
'
    DB.Connect
'
    trLevel = DB.BeginTrans
    strSQL = "TRUNCATE TABLE ZIPCODE"
    blnRet = DB.ExecuteSQL(strSQL)
'
    strSQL = "BULK INSERT ZIPCODE"
    strSQL = strSQL & " FROM '" & bName & "'"
    strSQL = strSQL & " WITH"
    strSQL = strSQL & " ("
    strSQL = strSQL & "   FIELDTERMINATOR = ','"
    strSQL = strSQL & " , DATAFILETYPE='widechar'"
    strSQL = strSQL & " , ROWTERMINATOR = '\n'"
    strSQL = strSQL & " );"
    blnRet = DB.ExecuteSQL(strSQL)
'
    trLevel = DB.CommitTrans
    blnRet = DB.DisConnect
'
    Exit Sub

ErrProc:
    trLevel = DB.RollbackTrans
    blnRet = DB.DisConnect
'
    Close #fd
'
End Sub