SQLServerで末尾の列が空欄のTSVをBULK INSERTしてNULLを挿入したい！

この記事で伝えたいこと

改行コードも気をつけよう
末尾の列が空欄でKEEPNULLSをオプションを指定してもNULLにならないことがあるよ
デフォルトで入る改行コードはOSによって、違うよ
tsvを書き出す時に、改行コードを指定しよう

本題

さて、普段(といってもまだ2つ)はPowerAppsの記事を書いていましたが、別のお仕事でPythonとSQLServerを触っていて詰まったので、メモがてらまとめておきたいと思います。

早速本題ですが、皆さんTSVをBULK INSERTで空文字をNULLにしたいことってありませんか？
そんなときはKEEPNULLSオプションを指定しましょう。

BULK INSERT MyTable
FROM 'C:\path\to\sample_data.tsv' -- ファイルのパスを指定
WITH (
    FIELDTERMINATOR = '\t', -- フィールドの区切り文字をカンマと仮定
    KEEPNULLS
);

いかがでしたか？

NULLで登録されない。

・・・こんなものを見に来たわけじゃないですよね。
KEEPNULLSを指定しているのにも関わらず、空文字がNULLで登録されないことがあります。
今回自分が詰まった事例でいうと「ROWTERMINATOR = '0x0A'」が指定されていてかつ、次のようなtsvをWindows環境のPythonで作成していた場合です。

他パターンでも同じことが起きるかもしれませんが、未検証です。すみません。
また、この先は安全のためコードの一部のみ抜粋しているため、多少の誤りがあるかもしれません。

with open('C:/path/to/sample_data.tsv', 'w', encoding='utf-8') as f:
# この先で書き込む処理

BULK INSERT MyTable
FROM 'C:\path\to\sample_data.tsv' -- ファイルのパスを指定
WITH (
    FIELDTERMINATOR = '\t', -- フィールドの区切り文字をカンマと仮定
    ROWTERMINATOR = '0x0A'
    KEEPNULLS
);

sample_data.tsv

| column1 | column2        | column3 | column4        |
|---------|----------------|---------|----------------|
| data1   | "ここに空文字"　 | data3   | "ここに空文字"  |

この場合の「column2」はしっかりとNULLで入るのですが、「column4」は空文字で入ってしまいます。
何となく、ピンと来る方もいると思いますが・・・
Windows環境のPythonでは何も考えずに順当にtsv等で出力すると、改行コードが自動変換されCRLFになってしまいます。
(正確に言うとPythonのnewlineを指定しないと自動で「os.linesep」というものが使われることで結果的に自動変換される、といった形です)

今回、SQLServerで「ROWTERMINATOR = '0x0A'」を指定していることで'0x0A'はLFの16進数表現なのでtsvの改行コードと一致せず、今回のようなことが起きてしまったものと思われます。(末尾に改行コードが入ってきてしまっているイメージでしょうかね・・・？)

じゃあどうすればいいの？

対策は簡単で、Pythonでtsvを出力する時に改行コードを指定してあげればいいのです。

with open('C:/path/to/sample_data.tsv', 'w', encoding='utf-8', newline='') as f:
# この先で改行を'\n'でtsvに書き出す処理

もしくは・・・

with open('C:/path/to/sample_data.tsv', 'w', encoding='utf-8', newline='\n') as f:
# この先で改行を'\n'でtsvに書き出す処理

newlineというオプションに「''」を追加することで改行コードの自動変換が無効になります。
もしくは、'\n'を指定することでも可能です。

これで、無事にNULLで登録されるはずです。

終わりに

いかがでしたでしょうか、PowerApps以外の記事を書くのは初めてで微妙に緊張しました。
今後もPowerApps以外のことも触っていたりするので何かあれば書いていこうと思います。

参考

Pandas: DataFrame.to_csv() の改行コードを制御
https://scrapbox.io/shimizukawa/Pandas:_DataFrame.to_csv()_%E3%81%AE%E6%94%B9%E8%A1%8C%E3%82%B3%E3%83%BC%E3%83%89%E3%82%92%E5%88%B6%E5%BE%A1

一括インポート中の NULL または既定値の保持 (SQL Server)
https://learn.microsoft.com/ja-jp/sql/relational-databases/import-export/keep-nulls-or-use-default-values-during-bulk-import-sql-server?view=sql-server-ver16

Windows上で実行したPythonの出力ファイルの改行コードが変わる
https://qiita.com/tatsuya-miyamoto/items/f57408064b803f55cf99

Pythonでファイルを読み書きするとき、改行コードはどうなるの？
https://pythonmaniac.com/file-rw-newline-code/