ｂｃｐインサートでのフォーマットファイルの書き方

質問

bcpコマンドでインサートをしたいと考えています。
テーブルの形式は
1 datetime 日時
2 filed_1 varchar(10)
3 filed_2 varchar(10)
4 filed_3 varchar(10)
です。
フォーマットファイルに
8.0
4
1 SQLDATETIME 0 8 "," 1 日時 ""
2 SQLCHAR 0 3 "," 2 filed_1 ""
3 SQLCHAR 0 3 "," 3 filed_2 ""
4 SQLCHAR 0 3 "\r\n" 4 filed_3 ""
書きました。
データは、
2009/04/01 0:00:00,11.0,11.0,11.0
2009/04/01 0:01:00,11.0,11.0,11.0
です。
SQLServerは2005のExpressです。
実行すると
コピーを開始中です...
SQLState = 37000, NativeError = 7339
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]リンク サーバー '(null)'
の OLE DB プロバイダ 'STREAM' から、列 '[!BulkInsert].日時' に無効なデータが返さ
れました。

BCP コピー in が失敗しました
が返されます。
成功させるにはどこを直せばよいでしょうか。
よろしくお願いします。

回答No.3

jamshid6 · ベストアンサー

うーん、複数の端末で試してみましたが、こちらではうまくいきます。
同様のケースでyyyy/MM/ddがうまくいかないという話はあるみたいですね。

ExpressもSP3にしてみるとか、2008Expressにしてみるとかという手もあるかもしれませんが、
実際ロケールに頼るのもどうかと思うので、別の方法も試してみてはどうでしょうか。
（ファイルは同じPC内でしたよね）

BCPの代わりにSQLCMDとOPENROWSETを使う方法です。
SQLCMD -Sサーバ -Uユーザ -Pパスワード -dデータベース -Q"INSERT INTO TEST SELECT * FROM OPENROWSET(BULK 'C:\Test.csv', FORMATFILE='C:\Test.fmt') x"
フォーマットファイルは同じのを使いますが、
8.0
4
1 SQLCHAR 0 20 "," 1 日時 ""
2 SQLCHAR 0 10 "," 2 field_1 ""
3 SQLCHAR 0 10 "," 3 field_2 ""
4 SQLCHAR 0 10 "\r\n" 4 field_3 ""

という風に長さを十分にします。

何が違うかというと、一旦ファイルをすべて文字列でテーブルのように読みだして、テーブルにはINSERTすることにより、
文字列から日付への変換をMDACではなく、SQL Serverに行わせるというものです。
yyyy/MM/dd hh:mm:ssは、SQL Serverではデフォルトで暗黙変換ができます。
この方法だと仮に変換がうまくいかなくても、

INSERT INTO TEST SELECT CONVERT(datetime,日時),field_1,field_2,field_3 FROM OPENROWSET(BULK 'C:\Temp\TestData.csv', FORMATFILE='C:\Temp\Test.fmt') x
という風に変換を明示的に指定してあげることもできるので、柔軟性があります。
（リモートサーバだとBCPのようにはいきませんが）

もちろん、yyyyMMddはうまくいくのだったら、最初にVBで日付形式を変換してからBCP実行するとか、そういう考え方もあるでしょう。

補足

ありがとうございます。
ExpressのSP3は見つかりませんでした。
2008はインストールするときに2005を削除しなくても良いのでしょうか？

SQLCMDとOPENROWSETを使う方法を試してみました。
テーブル名が「TEST」の場合はうまくいきますが
実際のテーブル名は「3ABC」のようなアルファベットと数字を組み合わせています。そうすると
メッセージ 102、レベル 15、状態 1、サーバー MATANO\SQLEXPRESS、行 1
'3' 付近に不適切な構文があります。
が表示されてしまいます。
混ぜることはできないようです。
あとちょっとなのに…

回答No.4

jamshid6

テーブル名は[3ABC]のようにカギカッコで括ってみてください。

お礼

できました。
ありがとうございました。
やっと先に進めそうです。

回答No.2

jamshid6

>でも結果は同じでした。
同じ結果にはならないと思いますよ。少なくともメッセージは違うはずです。

ロケールというのは、それぞれのマシンが持っている日付や金額の基本書式のことです。
（コンパネで設定します）
日本語OSなら通常日付書式はyyyy/MM/ddになっているので、それを認識させて取りこむということです。

ちなみに、以下で試したら問題なく取りこまれましたよ。
<test.fmt>
8.0
4
1 SQLCHAR 0 8 "," 1 日時 ""
2 SQLCHAR 0 3 "," 2 field_1 ""
3 SQLCHAR 0 3 "," 3 field_2 ""
4 SQLCHAR 0 3 "\r\n" 4 field_3 ""

<test.csv>
2009/04/01 12:34:56,11.0,11.0,11.0
2009/04/01 11:23:45,11.0,11.0,11.0

bcp testdb.dbo.tbl1 in "C:\test.csv" -q -U"myuser" -P"mypass" -S"MYSERVER" -R -f"C:\test.fmt"

補足

テストまで調査して頂きありがとうございます。
でも、やっぱりできませんでした。

コピーを開始中です...
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]日付の形式が正しくありません。
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]日付の形式が正しくありません。

0 行コピーされました。
ネットワーク パケット サイズ (バイト数): 4096
クロック タイム (ミリ秒): 合計 1

いったい何が違うのか分かりません。
「20090401」ならうまくいくことがわかりました。
テーブルは下記スクリプトで作成しています。

USE [電気]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** オブジェクト: Table [dbo].[TEST] スクリプト日付: 06/05/2009 15:41:37 ******/
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TEST](
[日時] [datetime] NOT NULL,
[filed_1] [varchar](10) NULL,
[filed_2] [varchar](10) NULL,
[filed_3] [varchar](10) NULL,
CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED (
[日時] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

コントロールパネルの時間形式は、短い形式に「2009/06/08」と書かれています。OSはXP ProのSP3です。SQLServer2005Expressです。
やっぱりどこか設定が間違っているのかもしれません。

回答No.1

jamshid6

SQLDATETIMEはネイティブ型のファイルにしか使えませんので、文字列型のファイルでは、日付でもSQLCHARを使います。
また、もともとyyyy/MM/dd形式のデータはデフォルトでは日付と認識してくれないので、今自分の使っているロケールで取り込むようにBCPに-Rオプションを追加してください。

補足

ありがとうございます。
やってみました。

bcpコマンドは
bcp "電気.dbo.データ" in "D:\test\bcptest\test.csv" -q -U"AAA" -P"BBB" -S"SQLEXPRESS" -R -f "D:\test\bcptest\test.fmt"

でも結果は同じでした。
「ロケールで取り込む」とはどういうことでしょうか？