SQLserver 列の違うcsvファイルをインポート 連番

SQLserverの性質が分からず使っていますが、自動でCSVファイルをSQLSERVERのテーブルにインポートしたい。
ただし、SQLserverでは、後々インポートした後処理をしたら削除したいので、主キーが無いと削除できないので、csvファイルの列よりも1列 NO という列を追加して主キーを付けてテーブルを準備した。

ちなみにテーブル構造から、その主キーのプロパティを開き、IDENTITY列っていうところに、NOをドロップダウンから選択して設定しておくとデータをインポートしたときに、自動で連番がふられるという仕組み。

インポートテーブルとCSVデータが同じ列数ならば、クエリからBULK INSERTにて、インポートが可能だ。

しかし、列が違うと面倒な手続きが必要になる。

まず、実機から、フォーマットファイルというのを作成する。

下記の例では、testdbがデータベース名 dbo.tableがテーブル名 JOYSQL\SQLEXPRESS がサーバーとインスタンス名となる。あらかじめ、インポート先のテーブルは作成しておき、フォーマットファイルを作成する手順。

(1)bcp testdb.dbo.table format nul -T -c -t “,” -f C:\data\table.fmt -S JOYSQL\SQLEXPRESS

※サーバーとインスタンスの設定をしないと、接続できないよってエラーが発生する。

上記のコマンドをバッチファイルにして実行してもいいし、直接DOSプロンプトに打ち込んでもOK
その結果

(2)dataディレクトリにtable.fmtというフォーマットファイルができる。
※フォーマットファイルは、xml形式の物もできるようだ。

13.0
5
1 SQLCHAR 0 12 “,” 1 NO “”
2 SQLCHAR 0 24 “,” 2 日付 “”
3 SQLCHAR 0 7 “,” 3 スクールCD “”
4 SQLCHAR 0 100 “,” 4 ヨヤクルCD Japanese_CI_AS
5 SQLCHAR 0 100 “\r\n” 5 氏名 Japanese_CI_AS

今回は、最初の列を飛ばしたいので、1の行の列を下記のようにスキップさせる様に書き換えて保存。
※1行目はNULLを書いて飛ばしているようだ。でも、連番の設定をしているので自動で振られる。

13.0
5
1 SQLCHAR 0 0 “” 0 NO “”
2 SQLCHAR 0 24 “,” 2 日付 “”
3 SQLCHAR 0 7 “,” 3 スクールCD “”
4 SQLCHAR 0 100 “,” 4 ヨヤクルCD Japanese_CI_AS
5 SQLCHAR 0 100 “\r\n” 5 氏名 Japanese_CI_AS

次に実際にインポートコマンド

(3)bcp testdb.dbo.table in C:\point\20161110.csv -T -f C:\data/table.fmt -S JOY2016SQL\SQLEXPRESS

これで、20161110.csvが、dbo.tableに無事にインポートされた。

(1)と(3)をバッチファイルにして、定期実行させれば自動でインポートができるようになる。

ただ、毎日の日付ファイルをインポートしたいので、ちょっとした小細工がひつようになる。
以前、JOY備忘録でも、日付の自動バッチファイルを作成したことがある。
それを参考に毎日、インポートできるようにしていくのが次の課題だ。

Access パラメータをVBAで渡してクエリを実行

CSVファイルを出力するのに、クエリにパラメータを設定すると、入力を求められる。
めんどくさいので自動化したい。その時の物です。
エクスポートの定義は手動でやらないとダメですね。

Private Sub web_output_Click()

Const TBL_NAME = “web_pdf_output”
Const RPT_NAME = “repo_web_pdf_output”
Const TBL_NAME0 = “web_pdf_output0”
Const RPT_NAME0 = “repo_web_pdf_output0”
Const PDF_PATH = “D:\NTT\”

Dim Rs As ADODB.Recordset
Set Rs = New ADODB.Recordset

Dim rs0 As ADODB.Recordset
Set rs0 = New ADODB.Recordset

Dim dbs As Database
Dim qdf As QueryDef

Dim myStr As String

DoCmd.SetWarnings False
DoCmd.OpenQuery “repo_all_chage_pdf”, acViewNormal
DoCmd.OpenQuery “repo_all_chage_pdf0”, acViewNormal

Do While True
myStr = InputBox(“yyyymmの形式を入力してください。”)

‘—(1)キャンセルしたとき
If StrPtr(myStr) = 0 Then
MsgBox “キャンセルします”
Exit Sub

‘—(2)空欄のまま[OK]したとき
ElseIf myStr = “” Then
MsgBox “未入力です”, vbExclamation

‘—(3)入力文字が6文字より長いとき
ElseIf Len(myStr) > 6 Then
MsgBox “文字が長すぎます”, vbExclamation

Else
‘—(4)入力文字が6文字以内のとき
MsgBox “入力された文字列は「” & myStr & “」です”
GoTo Nextjob
End If
Loop

Nextjob:

Rs.Open “SELECT DISTINCT FID FROM web_pdf_output”, CurrentProject.Connection, adOpenStatic, adLockReadOnly

Do Until Rs.EOF

DoCmd.OpenReport RPT_NAME, acViewPreview, , “FID=” & Rs!FID, acWindowNormal
DoCmd.OutputTo acOutputReport, RPT_NAME, acFormatPDF, PDF_PATH & Rs!FID & “0000” & myStr & “.PDF”
DoCmd.Close
Rs.MoveNext
Loop

rs0.Open “SELECT DISTINCT ID FROM web_pdf_output0”, CurrentProject.Connection, adOpenStatic, adLockReadOnly

Do Until rs0.EOF

DoCmd.OpenReport RPT_NAME0, acViewPreview, , “ID=” & rs0!ID, acWindowNormal
DoCmd.OutputTo acOutputReport, RPT_NAME0, acFormatPDF, PDF_PATH & rs0!ID & myStr & “.PDF”
DoCmd.Close
rs0.MoveNext
Loop
rs0.Close

DoCmd.SetWarnings False
DoCmd.DeleteObject acTable, “web_renkei_csv”
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs(“output_web_data”)
With qdf
.Parameters(“tsuki”) = myStr
.Execute
End With

DoCmd.TransferText _
TransferType:=acExportDelim, _
specificationname:=”Web_renkei_csv エクスポート定義”, _
TableName:=”web_renkei_csv”, _
FileName:=”D:\NTT\webdatajoy_” & Format(Date, “yyyymmdd”) & “.txt”

End Sub

Access TSVファイルの出力

TSVファイルって何?

CSVファイルのタブ区切り版のようだ。そのようなCSVファイルをどうやって出力したらいいのだろうか?

フォームに適当なボタンを作成して、下記のようなVBAを実行させればいいようだ。

個配指示データというテーブルをTSVファイルに出力する例をあげている。個配指示というボタンをクリックすると実行される。

Private Sub 個配指示_Click()

DoCmd.TransferText _
TransferType:=acExportDelim, _
TableName:=”個配指示データ”, _
FileName:=”D:\test\kohaishiji_DDDAAA_” & Format(Date, “yyyymmdd00”) & “.txt”

End Sub

※上記の例では、kohaishiji_DDDAAA_2016012000.txtってファイルを作成するように設定(日付20160120)

しかーーーーし、これでは、カンマ区切りの状態になる。調べて調べて、

適当なテーブルをクリックして、外部データ>>エクスポート>>テキストファイル を選択
データのエクスポート先の選択とかでるが、気にせずに、OK
そうすると、カンマ区切りの表示がでるので、設定をクリックして、エクスポート定義ってのでタブ区切り等の設定をする。保存を押すと、定義名を変更できるようになるので、定義する。

その定義分を付け加えてやればいい。

Private Sub 個配指示_Click()

DoCmd.TransferText _
TransferType:=acExportDelim, _
specificationname:=”個配指示データ エクスポート定義”, _
TableName:=”個配指示データ”, _
FileName:=”D:\test\kohaishiji_DDDAAA_” & Format(Date, “yyyymmdd00”) & “.txt”

End Sub