はじめに
外部システムから出力されたCSVファイルをDBに取り込む依頼がありました。
C#やPythonでプログラムを組む方法もありますが、今回はSQL Serverで完結させたいと思います。
CSVファイルの読み込みとデータの整形は出来ています。
データ変換
CSVファイルを読み込むときに型を指定できますが、型に合っていないデータがあるとエラーになります。
読み込む際に不正データの加工が出来ないので、読み込む時点ではすべて文字列型にしました。
その為、読み込み後に型変換を行う必要があります。
型変換
SQL Serverの型変換関数は「CAST」と「CONVERT」があります。
CAST(値 AS データ型)
CONVERT(データ型, 値 [, スタイル])
まずは２つの違いを纏めます。
| 比較 | CAST | CONVERT |
|---|---|---|
| 仕様 | ANSI SQL標準（他のDBでも使える） | SQL Server独自の関数 |
| 機能 | 型変換のみ | 型変換＋書式指定 |
| 主な用途 | 型の変換 | 日付や数値のフォーマット変換 |
つまり
・単純変換なら「CAST」
・フォーマット付きなら「CONVERT」
となります。
今回の型変換は主に文字列⇒数値、日付なので「CAST」が適切です。
シリアル値 ⇒ 日付変換
「yyyy/mm/dd」のような形式であればCAST関数で変換できるのですが、シリアル値を変換します。
難しいことは無く、「1900年1月1日」を基準にしてシリアル値を日数として加算すればOKです。
正確にはシリアル値は「1900年1月1日」を「1」としているので、「1」を引きます。
つまり
DATEADD(DAY, シリアル値 - 1, '1900-01-01')
となります。
ただ1点注意が必要です。
Excelで作成したシリアル値の場合、「2」を引く必要がある。
という事です。
西暦の年が4で割り切れる年がうるう年です。
ただし、100で割り切れる年はうるう年になりません。
ただし、100で割り切れる場合でも400で割り切れる年はうるう年になります。
というルールですが、Excelのバグで「1900/2/29」が存在しています。
その為、正しい日数を計算するには「2」を引く必要があります。
変換できる場合は○○、できない場合は××
シリアル値を日付に変換する際、数値に変換できないデータがありました。
そのデータを
DATEADD(DAY, CAST(シリアル値 AS INT) - 1, '1900-01-01')
で変換するとエラーになります。
そこで数値に変換できるかどうかの判定を入れます。
CASE
WHEN TRY_CAST(シリアル値 AS INT) IS NOT NULL THEN
DATEADD(DAY, CAST(シリアル値 AS INT) - 1, '1900-01-01')
ELSE
NULL
END
これで数値に変換できないデータがあってもエラーにならないようになりました。