SQL Servserでcreate Table(2)

2020-01-03

SQL Servserでcreate Table“という記事の続きです。

あらすじ

  • Investing.comからBYCUSDのヒストリカルデータを入手
  • SQLServerにテーブルを作り、BULK INSERTしようとした
  • データを弄らずにインポートできる方法でSQL Serverにインポートした

前回でBULK INSERTしようとしたのですが、データ内にカンマがあり、このままだとデータを登録できないだろうと思い、急遽"フラットファイルのインポート"で登録を行いました。

今回はデータを加工して、前回行わなかったBULK INSERTを実行し、途中で出たエラーなどにどう対処したのか書いていきます。

データの加工

前回Investing.comからダウンロードしたCSVファイルをメモ帳で開くとこのようになっていました。

これをエクセルで開き、各カラムに対して、セルの書式設定で適切なデータ書式に変更します。

このときの変更した部分は、"Date"カラムの「d-mmm-yy」から「yyyymmdd」への変更、"Price"~"Low"までの価格データを、"金額"から"数値(小数点以下二桁)"への変更、"Vol."を"標準"から"文字列"への変更、そして"Change %"を"パーセンテージ"から"数値(小数点以下五桁)"への変更を行いました。

またヘッダレコードを取り除きました。

いろいろ変更したファイルがこちらです。

CREATE文の変更

“Vol."カラムにNULLが含まれていることを考慮して、CREATE文のクエリを次のように変更しました。

create table testDB001.dbo.BTCUSDDailyPrice_test(
	"Date" varchar(8) NOT NULL,    --dateからvarchar(8)へ
	Price float NOT NULL,
	"Open" float NOT NULL,
	"High" float NOT NULL,
	"Low" float NOT NULL,
	"Vol." varchar(50),    --NOT NULLを削除
	"Change%" float NOT NULL,    --varchar(50)からfloatへ
);

“Date"はとりあえず文字列にしました。多分"date"でもいいと思います。"Change %"は"%"を取っ払い、小数表示に変えたので"float"にしました。

BULK INSERT文

BULK INSER文は次の通りです。

bulk insert testDB001.dbo.BTCUSDDailyPrice_test
from 'D:\Quand\BTC_USD20150101to20191231.csv'
with (
    datafiletype = 'char',
    rowterminator = ','
);

これでうまくいくはずだったのですが、エラーがでました。

ファイル 'D:\Quandl\BTC_USD20150101to20191231.csv’ を開けなかったので、一括読み込みできません。オペレーティング システム エラー コード 3(指定されたパスが見つかりません)。

さっそくググって調べてみました。するとCSVファイルへのアクセス権限がないことによるエラーがたくさんヒットしました。

しかしこのSSMSではWindows認証のため関係ないはず、とは思っていたのですが、とりあえず記述してあるパスをファイルエクスプローラのURL部分にコピーして開けるかどうか試してみました。

開けませんでした。

パスが間違っていただけでした!

さっそく修正して、再度トライしました。

一括読み込み: データ ファイルで予期しないファイルの終了が検出されました。

リンク サーバ “(null)" の OLE DB プロバイダー “BULK" により、エラーがレポートされました。プロバイダーからエラーに関する情報を取得できませんでした。

リンク サーバー “(null)" の OLE DB プロバイダー “BULK" から行をフェッチできません。

3つもエラーがでてきてしまい、げんなりしましたが、とりあえずググりました。

するとこのエラーの原因は、CSVファイルの文字エンコードが"ANSI"でないことだとわかりました。

さっそくAtomで文字エンコードをANSI(Shift JIS)へ変更しました。再度トライましたが、やはり同じエラーが吐き出されました。

bulk insert文が正しいかどうか確認すると、

rowterminator = ','

これはひどい。

“rowterminator"は行の終わりを示すもので、フィールドの区切りと勘違いしていたようです。コピペばかりすると、コピペだから正しいはずとついつい思ってしまったようですね(前回の記事は修正しました)。

正しく修正したBULK INSERT文は次の通りとなります。

bulk insert testDB001.dbo.BTCUSDDailyPrice_test
from 'D:\Quandl\BTC_USD20150101to20191231.csv'
with(
	datafiletype = 'char',
	fieldterminator = ','
)

早速実行してみます。

上手くいきました。

さっそく、ちゃんと登録されているのか確認してみます。

どうやら問題ないようです。

あまり個人的にSQL Serverを使うことはないと思うのですが、なにかの役に立てればなと思いました。以上です。