Windows 10 Pro_x64 + Excel2013_x86 VBA で Ubuntu 16.04 LTS + SQL Server 2017 Express に BULK INSERT で書き込む

Windows 10 Pro_x64 + Excel2013_x86 VBA で Ubuntu 16.04 LTS + SQL Server 2017 Express に BULK INSERT で書き込む

ある程度の行数を1行づつ書き込むのにも飽きたので、BULK INSERT を使うことにする

1行ごとに書いてinvalidなデータでエラーが発生した場合、ログを吐いて止まればいい

と思ったりもするので迷うところではある

下記で設定してみたので、例によって書き込んでみる

Ubuntu 16.04 LTS で SQL Server 2017 Express を試してみる

Ubuntu 16.04 LTS で SQL Server 2017 Express をもう少し試してみる

Windows 10 Pro_x64 + Excel2013_x86 VBA DataBase Access クラスを使って書き込んでみる

Ubuntu 16.04 LTS で 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 って相互変換不可のコードがあった気が

※ネットワーク越しにファイルに書き込む速度が遅すぎなので

　ローカルでファイルを作成後、コピーする

※内容が重複する内容を書くのは迷ったけど、メモということで

対象データは以下の全国一括(KEN_ALL.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