bcpコマンドでインサートをしたいと考えています。
テーブルの形式は
1 datetime 日時
2 filed_1 varchar(10)
3 filed_2 varchar(10)
4 filed_3 varchar(10)
です。
フォーマットファイルに
8.0
4
1SQLDATETIME08","1日時""
2SQLCHAR03","2filed_1""
3SQLCHAR03","3filed_2""
4SQLCHAR03"\r\n"4filed_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ベストアンサー
- 回答日時:
うーん、複数の端末で試してみましたが、こちらではうまくいきます。
同様のケースで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
- 回答日時:
テーブル名は[3ABC]のようにカギカッコで括ってみてください。
No.2
- 回答日時:
>でも結果は同じでした。
同じ結果にはならないと思いますよ。少なくともメッセージは違うはずです。
ロケールというのは、それぞれのマシンが持っている日付や金額の基本書式のことです。
(コンパネで設定します)
日本語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
- 回答日時:
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"
でも結果は同じでした。
「ロケールで取り込む」とはどういうことでしょうか?
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
これ何て呼びますか Part2
あなたのお住いの地域で、これ、何て呼びますか?
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
映画のエンドロール観る派?観ない派?
映画が終わった後、すぐに席を立って帰る方もちらほら見かけます。皆さんはエンドロールの最後まで観ていきますか?
-
海外旅行から帰ってきたら、まず何を食べる?
帰国して1番食べたくなるもの、食べたくなるだろうなと思うもの、皆さんはありますか?
-
天使と悪魔選手権
悪魔がこんなささやきをしていたら、天使のあなたはなんと言って止めますか?
-
BCPでCSV内の文字列をテーブルのDatetime型カラムに取り込む方法
その他(データベース)
-
datetime型のインポートについて
SQL Server
-
bcp in でエラー
SQL Server
-
-
4
処理件数を非表示にしたい
SQL Server
-
5
テキストデータをSQLServerに取込むには
その他(データベース)
-
6
テーブル列数とデータファイル列数の違うbcpインサートをしたい
SQL Server
-
7
SQLSever 一括インポートについて
その他(パソコン・スマホ・電化製品)
-
8
Oracle 8i コンマ(,)を含むデータをinsertしたい
その他(データベース)
-
9
オラクルではできるのにSQLSERVERではサブクエリーで複数キーを指定できない?
SQL Server
-
10
コマンドプロンプトの「%1」と「%~1」の違いがわからない
その他(プログラミング・Web制作)
-
11
BCPコマンドのリダイレクト値が空になる
SQL Server
-
12
【DB】同じトランザクション内でupdateとselectをしたときの結果値
その他(データベース)
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
- ・ゆるやかでぃべーと タイムマシンを破壊すべきか。
- ・歩いた自慢大会
- ・許せない心理テスト
- ・字面がカッコいい英単語
- ・これ何て呼びますか Part2
- ・人生で一番思い出に残ってる靴
- ・ゆるやかでぃべーと すべての高校生はアルバイトをするべきだ。
- ・初めて自分の家と他人の家が違う、と意識した時
- ・単二電池
- ・チョコミントアイス
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
accessのエクスポートエラーに...
-
SQL Server のデータをCSVファ...
-
ACCESSでのエクセルインポート...
-
エクスポート時の改行コードに...
-
SQLServer2005 データのCSVファ...
-
既に使用されているので、使用...
-
Access97の文字化けについて
-
symfowareのSQL文in句をバッチ...
-
ACCESSでのCSVデータの自動取り...
-
アクセスのテーブルからエクス...
-
「マスタ」と「テーブル」の違...
-
3つ以上のテーブルをUNIONする...
-
ACCESSのSQLで、NULLかNULLでな...
-
Access 1レコードずつcsvで出力...
-
Accessにインポートしたら並び...
-
sqlserverで集計結果をUPDATEし...
-
SQLServerで同一条件レコードの...
-
ACCESS2007 フォーム 「バリア...
-
クエリのキャンセルがいつにな...
-
SELECT時の行ロックの必要性に...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
accessのエクスポートエラーに...
-
エクスポート時の改行コードに...
-
既に使用されているので、使用...
-
アクセスのテーブルからエクス...
-
Accessのリンクテーブルについて
-
SQLServer2005でフラットファイ...
-
AccessでCSVインポートのゼロサ...
-
bcpによる、テーブル→CSVファイ...
-
symfowareのSQL文in句をバッチ...
-
bcpインサートでのフォーマ...
-
MS-SQLプロシージャでファイル...
-
SybaseのBCPでファイル上書き
-
ACCESSでのエクセルインポート...
-
SQL Server のデータをCSVファ...
-
Access97の文字化けについて
-
SQLServer2005 データのCSVファ...
-
sqlite(spatialite_gui)での...
-
Access2003でのエクスポートに...
-
スクリプトファイルの一括実行
-
DBMAGICのプログラム解析
おすすめ情報