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 を試してみる
参考にしたのは以下のサイト
1.CSV ファイルを BULK INSERT を使ってインポートする
2.UTF-8 形式のテキストファイルを BULK INSERT で一括取り込みする( SQL Server )
3.プログラミング ガイドライン
4.BULK INSERT (Transact-SQL)
※最初は1.の
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
で出来たと思ったのだけど、SSMSから叩くと、文字化けが発生している
※2.を見て UTF-8 でいけるか
DATAFILETYPE='char',
CODEPAGE = '65001',
FIELDTERMINATOR = ','
と思ったら、4.には Linux版はCODEPAGEに未対応と明記してある・・・
※結局、2.3.に書いてある
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 ExplicitPrivate 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
.CloseEnd 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 SubErrProc:
trLevel = DB.RollbackTrans
blnRet = DB.DisConnect
'
Close #fd
'
End Sub
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 を試してみる
参考にしたのは以下のサイト
1.CSV ファイルを BULK INSERT を使ってインポートする
2.UTF-8 形式のテキストファイルを BULK INSERT で一括取り込みする( SQL Server )
※最初は1.の
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
で出来たと思ったのだけど、SSMSから叩くと、文字化けが発生している
※2.を見て UTF-8 でいけるか
DATAFILETYPE='char',
CODEPAGE = '65001',
FIELDTERMINATOR = ','
と思ったら、4.には Linux版はCODEPAGEに未対応と明記してある・・・
※結局、2.3.に書いてある
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