ダブルクォーテーションで括られたCSVカ​ンマ区切りテキストファイルを SQL Server で Bulk Insert する方法

 

ダブルクォーテーションで括られたCSVファイルなんて使うのかよ？と私は疑問符なんだけれど、そういうファイルを吐き出すシステムもあるらしいのでメモ。

区切り文字にカンマ[,]を使用しちゃって、データにカンマ[,]が含まれちゃってるから、ダブルクォーテーションで括るしかないんだって。タブで区切ればいいと思うんだけどね。データにタブって普通入らないでしょ。データにダブルクォーテーションが入っててもNGだし。

 

ポイント

• 最初の区切り文字を、"にする
• 最初の列は切り捨てる
• 2番目以降の区切り文字を","にする
• 最後の区切り文字を"\r\nにする
• "はエスケープが必要なので"\になる。
  区切り文字は""で囲む
• インポートする CSVファイルの文字コードは ANSIにしておく。インポートする文字コードがUNICODEだとインポートできない。
  ※設定誤りでインポートできていない可能性もあるので、インポートできるかもしれない。
• フォーマットファイルの文字コードは ANSI でなければいけない。UNICODEだと「XML の解析: 行 2、文字 0。ドキュメントの構文が正しくありません。」エラーになる。
• フォーマットファイルの最終後は改行が必要。改行なしだと「一括読み込みできません。ファイル "C:\Users\****\Desktop\MovieStars.fmt" が不完全か、読み取れませんでした。オペレーティング システム エラー コード (null)。」となる。

 

素材

インポートするCSVファイル[MovieStars.csv]は

• ダブルクォーテーション["]で括られている
• 項目区切り文字がカンマ[,]である。
• 文字コードは ANSI である。※UNICODEだと取り込めない。

※日本語OKを表現するために、1行目に「トム　クルーズ」を入れているけど Paste As Visual Studio が日本語対応していないから消えてる。

MovieStars.csv

"37834","Miller","","/hIVsySa8Ro6vrQ0hygIF6ylBgK7.jpg"
"11259","Joseph,Donnelly","Tom,Cruise","/hIVsySa8Ro6vrQ0hygIF6ylBgK7.jpg"
"56292","Ethan,Hunt","Tom,Cruise","/hIVsySa8Ro6vrQ0hygIF6ylBgK7.jpg"
"75612","Jack","Tom,Cruise","/hIVsySa8Ro6vrQ0hygIF6ylBgK7.jpg"
"75780","Jack,Reacher","Tom,Cruise","/hIVsySa8Ro6vrQ0hygIF6ylBgK7.jpg"
"4515","Senator,Jasper,Irving","Tom,Cruise","/hIVsySa8Ro6vrQ0hygIF6ylBgK7.jpg"
"9390","Jerry,Maguire","Tom,Cruise","/hIVsySa8Ro6vrQ0hygIF6ylBgK7.jpg"
"7520","Brian,Flanagan","Tom,Cruise","/hIVsySa8Ro6vrQ0hygIF6ylBgK7.jpg"
"744","Pete,'Maverick',Mitchell","Tom,Cruise","/hIVsySa8Ro6vrQ0hygIF6ylBgK7.jpg"
"616","Captain,Nathan,Algren","Tom,Cruise","/hIVsySa8Ro6vrQ0hygIF6ylBgK7.jpg"
"628","Lestat,de,Lioncourt","Tom,Cruise","/hIVsySa8Ro6vrQ0hygIF6ylBgK7.jpg"
"1538","Vincent","Tom,Cruise","/hIVsySa8Ro6vrQ0hygIF6ylBgK7.jpg"
"881","Lt.,Daniel,Kaffee","Tom,Cruise","/hIVsySa8Ro6vrQ0hygIF6ylBgK7.jpg"
"955","Ethan,Hunt","Tom,Cruise","/hIVsySa8Ro6vrQ0hygIF6ylBgK7.jpg"
"956","Ethan,Hunt","Tom,Cruise","/hIVsySa8Ro6vrQ0hygIF6ylBgK7.jpg"
"954","Ethan,Hunt","Tom,Cruise","/hIVsySa8Ro6vrQ0hygIF6ylBgK7.jpg"
"74","Ray,Ferrier","Tom,Cruise","/hIVsySa8Ro6vrQ0hygIF6ylBgK7.jpg"
"227","Steve,Randle","Tom,Cruise","/hIVsySa8Ro6vrQ0hygIF6ylBgK7.jpg"
"180","Chief,John,Anderton","Tom,Cruise","/hIVsySa8Ro6vrQ0hygIF6ylBgK7.jpg"
"87825","Rock","Tom,Dreesen","/vWtJg0UNtXmdrfCYhpocSomU5zM.jpg"
"581","Sergeant,Pepper","Tom,Everett","/nxrCAB3xL79EdWLwBUMwXj7FtI3.jpg"
"13836","Matheson","Tom,Everett,Scott","/8OfPzUKxETeKaALRBVaDTZdLIUG.jpg"
"12444","Draco,Malfoy","Tom,Felton","/1lzM9lcwuZynxX3WEfzJqAAxVag.jpg"
"61791","Dodge,Landon","Tom,Felton","/1lzM9lcwuZynxX3WEfzJqAAxVag.jpg"
"674","Draco,Malfoy","Tom,Felton","/1lzM9lcwuZynxX3WEfzJqAAxVag.jpg"
"672","Draco,Malfoy","Tom,Felton","/1lzM9lcwuZynxX3WEfzJqAAxVag.jpg"
"673","Draco,Malfoy","Tom,Felton","/1lzM9lcwuZynxX3WEfzJqAAxVag.jpg"
"675","Draco,Malfoy","Tom,Felton","/1lzM9lcwuZynxX3WEfzJqAAxVag.jpg"
"767","Draco,Malfoy","Tom,Felton","/1lzM9lcwuZynxX3WEfzJqAAxVag.jpg"
"644","Supernerd","Tom,Gallop","/vk3ittMhuynueCIgHqxgl3U3mhb.jpg"
"2503","Tom,Cronin","Tom,Gallop","/vk3ittMhuynueCIgHqxgl3U3mhb.jpg"
"2502","Tom,Cronin","Tom,Gallop","/vk3ittMhuynueCIgHqxgl3U3mhb.jpg"
"855","SSgt.,Ed,Yurek","Tom,Guiry","/pHMTdX0ITGUpQHfg1Gmm2FayqMa.jpg"
"322","Brendan,Harris","Tom,Guiry","/pHMTdX0ITGUpQHfg1Gmm2FayqMa.jpg"
"568","Jim,Lovell","Tom,Hanks","/xxPMucou2wRDxLrud8i2D4dsywh.jpg"
"13","Forrest,Gump","Tom,Hanks","/xxPMucou2wRDxLrud8i2D4dsywh.jpg"
"591","Dr.,Robert,Langdon","Tom,Hanks","/xxPMucou2wRDxLrud8i2D4dsywh.jpg"
"4147","Michael,Sullivan","Tom,Hanks","/xxPMucou2wRDxLrud8i2D4dsywh.jpg"
"9800","Andrew,Beckett","Tom,Hanks","/xxPMucou2wRDxLrud8i2D4dsywh.jpg"
"64685","Thomas,Schell","Tom,Hanks","/xxPMucou2wRDxLrud8i2D4dsywh.jpg"
"59861","Larry,Crowne","Tom,Hanks","/xxPMucou2wRDxLrud8i2D4dsywh.jpg"
"83542","Old,Salty,Dog,/,Mr.,Meeks,/,Prescient,1","Tom,Hanks","/xxPMucou2wRDxLrud8i2D4dsywh.jpg"
"13448","Robert,Langdon","Tom,Hanks","/xxPMucou2wRDxLrud8i2D4dsywh.jpg"

 

インポート先のテーブル

 

手順

 

フォーマットファイルを作成する

Code Snippet

bcp [TEST].[dbo].[MovieStars] format nul -T -n -f "c:\users\%username%\desktop\MovieStars.fmt"

 

できたフォーマットファイル MovieStars.fmt

MovieStars.fmt修正前

11.0
4
1       SQLINT              1       4       ""   1     TMDbID                     ""
2       SQLCHAR             2       50      ""   2     Character                  Japanese_Unicode_BIN
3       SQLCHAR             2       50      ""   3     Name                       Japanese_Unicode_BIN
4       SQLCHAR             2       50      ""   4     ProfilePath                Japanese_Unicode_BIN

 

MovieStars.fmt をテキストエディタで編集する

修正内容　※どれか一つでも欠けたら失敗する。

• 1行目区切り文字としてダブルクォーテーションを入れる
• 項目間のスペースを1つに修正する
• SQLINTはSQLCHARに変更する
• 1列目の連番は1からスタートし、6列目の連番は0からスタートさせる
• 最終後は改行が必要。改行なしだと「一括読み込みできません。ファイル "C:\Users\****\Desktop\MovieStars.fmt" が不完全か、読み取れませんでした。オペレーティング システム エラー コード (null)。」となる。
  OK
  
  NG
  
  

MovieStars.fmt修正後

11.0
5
1 SQLCHAR 0 0 "\"" 0 DUMMY Japanese_CI_AS
2 SQLCHAR 0 255 "\",\"" 1 TMDbID Japanese_CI_AS
3 SQLCHAR 0 255 "\",\"" 2 Character Japanese_CI_AS
4 SQLCHAR 0 255 "\",\"" 3 Name Japanese_CI_AS
5 SQLCHAR 0 255 "\"\r\n" 4 ProfilePath Japanese_CI_AS

 

BulkInsertしてみる

BulkInsert

USE [TEST]
TRUNCATE TABLE [dbo].[MovieStarsUnicode]
BULK INSERT [dbo].[MovieStarsUnicode] FROM 'C:\Users\*****\Desktop\MovieStarsUnicode.csv' WITH (FIRSTROW = 1,FORMATFILE='C:\Users\*****\Desktop\MovieStarsUnicode.fmt')
SELECT * FROM [dbo].[MovieStarsUnicode]

 

 

インポート先のテーブルの項目がNVARCHARの場合

ダブルクォーテーションで括られたCSVファイル事態馬鹿馬鹿しいのにUNICODEが絡むとめんどくさい。

※ダブルクォーテーションで括られてないなら UNICODE でもめんどくさいないです。

こんなめんどくさいことに時間を費やすのも馬鹿馬鹿しいので終わり。以下の記載は一応できただけのメモで正しいやり方とは思えない。

• CSVファイルはANSI
• フォーマットファイルはSQLNCHARにしてはダメ

MovieStarsUnicode.fmt

11.0
5
1 SQLCHAR 0 0 "\"" 0 DUMMY Japanese_CI_AS
2 SQLCHAR 0 255 "\",\"" 1 TMDbID Japanese_CI_AS
3 SQLCHAR 0 255 "\",\"" 2 Character Japanese_CI_AS
4 SQLCHAR 0 255 "\",\"" 3 Name Japanese_CI_AS
5 SQLCHAR 0 255 "\"\r\n" 4 ProfilePath Japanese_CI_AS