

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で質問しましょう!
似たような質問が見つかりました
- MySQL テーブル作成です。どこかのスペルが間違っているか記号など スペースかな? 1 2022/10/01 05:08
- MySQL 何にかが違うから エラーなんでしょうね! 2 2022/09/18 05:28
- MySQL PHPとMySQLを使った掲示板の作り方 1 2022/06/02 13:00
- MySQL エラー 1068 (42000): 複数の主キーが定義されていますエラー 2 2022/11/17 04:36
- MySQL 参考書に従って入力したつもりでしたが、最後はエラーがでました。 1 2022/09/28 03:45
- MySQL `picture` varchar(255) のコマンドで間違いないでしょうか? 1 2022/11/21 04:08
- SQL Server PCが悪くなって新しいPCにSSMSのデータを移すよう頼まれたけど移し方が分からない 1 2023/05/18 16:54
- UNIX・Linux サーバー間のデータコピー(データ形式とデーターフォーマットの変換あり。一定間隔で処理) 2 2023/08/22 22:15
- MySQL MySQLのテーブル作成でハイフン - は使用できないのでしょうか? 2 2022/10/21 16:50
- Visual Basic(VBA) エクセルのマクロについて教えてください。 1 2023/08/03 12:30
このQ&Aを見た人はこんなQ&Aも見ています
-
昔のあなたへのアドバイス
過去のあなたへ一度だけアドバイスを送れる電話があったとします。
-
【お題】動物のキャッチフレーズ
【お題】「百獣の王 ライオン」「実は動物界最強 カバ」は分かるけど、それはちょっとピンと来ないなと思った動物のキャッチフレーズ
-
人生で一番思い出に残ってる靴
皆さんの人生で一番思い入れのある靴の話を伺ってみたいです。
-
ゆるやかでぃべーと タイムマシンを破壊すべきか。
[状況]これはディベートの論題だと仮定したうえでの回答お願いします。
-
準・究極の選択
「年収1000万円で一生カレーライス」か「年収180万円で毎日何でも食べ放題」
-
BCPでCSV内の文字列をテーブルのDatetime型カラムに取り込む方法
その他(データベース)
-
テキストデータをSQLServerに取込むには
その他(データベース)
-
bcp in でエラー
SQL Server
-
-
4
テーブル列数とデータファイル列数の違うbcpインサートをしたい
SQL Server
-
5
datetime型のインポートについて
SQL Server
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・昔のあなたへのアドバイス
- ・字面がカッコいい英単語
- ・許せない心理テスト
- ・歩いた自慢大会
- ・「I love you」 をかっこよく翻訳してみてください
- ・ゆるやかでぃべーと タイムマシンを破壊すべきか。
- ・はじめての旅行はどこに行きましたか?
- ・準・究極の選択
- ・この人頭いいなと思ったエピソード
- ・「それ、メッセージ花火でわざわざ伝えること?」
- ・ゆるやかでぃべーと すべての高校生はアルバイトをするべきだ。
- ・【お題】甲子園での思い出の残し方
- ・【お題】動物のキャッチフレーズ
- ・人生で一番思い出に残ってる靴
- ・これ何て呼びますか Part2
- ・スタッフと宿泊客が全員斜め上を行くホテルのレビュー
- ・あなたが好きな本屋さんを教えてください
- ・かっこよく答えてください!!
- ・一回も披露したことのない豆知識
- ・ショボ短歌会
- ・いちばん失敗した人決定戦
- ・性格悪い人が優勝
- ・最速怪談選手権
- ・限定しりとり
- ・性格いい人が優勝
- ・これ何て呼びますか
- ・チョコミントアイス
- ・単二電池
- ・初めて自分の家と他人の家が違う、と意識した時
- ・「これはヤバかったな」という遅刻エピソード
- ・ゴリラ向け動画サイト「ウホウホ動画」にありがちなこと
- ・泣きながら食べたご飯の思い出
- ・一番好きなみそ汁の具材は?
- ・人生で一番お金がなかったとき
- ・カラオケの鉄板ソング
- ・自分用のお土産
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
SQL Server のデータをCSVファ...
-
accessのエクスポートエラーに...
-
既に使用されているので、使用...
-
エクスポート時の改行コードに...
-
スクリプトファイルの一括実行
-
アクセスのテーブルからエクス...
-
SQL Server 2000 - bulk insert
-
DB2でのcsvファイルのインポート
-
AccessでCSVインポートのゼロサ...
-
SybaseのBCPでファイル上書き
-
SQLServer2005 データのCSVファ...
-
MS-SQLプロシージャでファイル...
-
SQLServer2005でフラットファイ...
-
bcpによる、テーブル→CSVファイ...
-
DBMAGICのプログラム解析
-
「マスタ」と「テーブル」の違...
-
SELECT時の行ロックの必要性に...
-
クエリのキャンセルがいつにな...
-
AccessShareLock はどの程度気...
-
DataTableから条件を満たした行...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
accessのエクスポートエラーに...
-
SQL Server のデータをCSVファ...
-
既に使用されているので、使用...
-
エクスポート時の改行コードに...
-
symfowareのSQL文in句をバッチ...
-
ACCESSでのエクセルインポート...
-
アクセスのテーブルからエクス...
-
bcpインサートでのフォーマ...
-
SQLServer2005でフラットファイ...
-
Accessのリンクテーブルについて
-
AccessでCSVインポートのゼロサ...
-
bcpによる、テーブル→CSVファイ...
-
SybaseのBCPでファイル上書き
-
MS-SQLプロシージャでファイル...
-
Access csvファイルで出力したい
-
pl/sqlでcsvファイルダウンロード
-
スクリプトファイルの一括実行
-
sqlite(spatialite_gui)での...
-
Access97の文字化けについて
-
CSVファイルのインポートについて
おすすめ情報