プロが教えるわが家の防犯対策術!

BCPを使用してCSVをテーブルに取込みたいのですが、
CSV内に日付形式の文字列(YYYY/MM/DD)があり、
取込先のテーブルの型はDatetimeになっています。
フォーマットファイルのデータ型は「SYBCHAR」
で設定しているのですが、いざ取込もうとすると、
以下のようなエラーが出てうまく取込めません。

SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]日付の形式が正しくありません。

フォーマットファイルの設定が悪いのでしょうか?
なんとか回避する方法はないでしょうか。

このQ&Aに関連する最新のQ&A

A 回答 (1件)

日付形式がYYYY-MM-DDなら取り込めます。


事前に/を-に置換しないといけませんが・・
<フォーマットファイル>
7.0
1
1 SQLCHAR 0 10 "\r\n" 1 dt

置換しない方法だと
日付フィールドがvarcharとしたテンポラリー用のテーブルを用意してそこにBCPでインポートする。
インポート完了後、本番テーブルに日付フィールドをコンバートしてインサートすればいけると思います。
    • good
    • 0
この回答へのお礼

参考になりました。ありがとうございました。

お礼日時:2004/02/05 09:30

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

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

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 が失敗しました
が返されます。
成功させるにはどこを直せばよいでしょうか。
よろしくお願いします。

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 ...続きを読む

Aベストアンサー

うーん、複数の端末で試してみましたが、こちらではうまくいきます。
同様のケースで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実行するとか、そういう考え方もあるでしょう。

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

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

BCPの代わりにSQLCMDとOPENROWSETを使う方法です。
SQLCMD -Sサーバ -Uユーザ -Pパスワード -dデータベース -Q"INSERT INTO TEST SELECT * FROM ...続きを読む

QSQL Server のキャラクターセット(内部文字コード)は何処で定義するのでしょうか?

SQL Server超初心者です。(Oracleについては約1年ちょっとの経験はあります)どうぞよろしくお願いします。
今回SQL Server2005 を使って簡単なシステムを構築する事になりました。
SQL Serverが扱う文字コートについて教えてください。
Oracleを使っていた時の経験としてDB内部のキャラクターセットと
クライアントで使う文字セット(NLS_LANG)を意識しておかないと、いろんな「文字化け」問題に遭遇した時に対応できませんでした。
きっとSQL Serverにおいても同じような事ではないかと思いここに質問させていただきます。
以下2点についてどなたかコメント願います。
1.(Oracleでいうキャラクターセット)はどこで定義するのでしょうか?
2.OracleでいうNLS_LANGに相当するものはあるのでしょうか?
 (ひょっとしたらマイクロソフト製品なのでサーバもクライアントもCP932固定なのでしょうか?)

根本的にはシステム構築する上で『極力文字化けに遭遇したくない』思い出このような質問をさせていただきました。
『文字化け』について注意点などありましたら合わせてコメントいただければ助かります。
以上よろしくお願いします。

SQL Server超初心者です。(Oracleについては約1年ちょっとの経験はあります)どうぞよろしくお願いします。
今回SQL Server2005 を使って簡単なシステムを構築する事になりました。
SQL Serverが扱う文字コートについて教えてください。
Oracleを使っていた時の経験としてDB内部のキャラクターセットと
クライアントで使う文字セット(NLS_LANG)を意識しておかないと、いろんな「文字化け」問題に遭遇した時に対応できませんでした。
きっとSQL Serverにおいても同じような事ではないかと思いここに質問...続きを読む

Aベストアンサー

Windowsのロケールの設定を日本語にした状態でSQLServerをインストールした場合、cp932がデフォルトの照合順序になります。照合順序については参考URLをご覧下さい。

SQLServerではわかりませんが、PostgresではJDBCドライバ部分で変換していたように記憶しています。ODBCドライバではAutoTranslateという機能があります。

このため、ODBC接続で何も考えずにvarcharを使用するとcp932になります。

参考URL:http://www.microsoft.com/japan/msdn/sqlserver/sql2005/bb330962.aspx

QBULK INSERTについて

CSVをバルクインサートでitemテーブルにBULK INSERTする際に、
下記のようなSQL文を使っているのですが、CSV内のブランク文字は
実行後にNULLになってしまいます。
やりたいことはNULLではなく空白文字("")にしたいのですが、
下記のSQL文をどのように変えればよいのでしょうか。
または、CSVを変更する方法でもよいです。

BULK INSERT item
FROM 'c:\test.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
);

大変お手数ですが、
わかる方がいらっしゃいましたらよろしくお願いいたします。

Aベストアンサー

http://msdn.microsoft.com/ja-jp/library/ms187887.aspx

あらかじめ、インサートする対象のフィールドにデフォルト値で空文字列を設定します。この状態でBULK INSERTを実行すれば空文字列が設定されます。

QBULKINSERTのWITHオプションについて

SQLserver 2005 Expressを使用してます。

csvをBULKINSERTでインポートする際にcsvの最終行に終了コードのようなデータが
入っていてインポートに失敗します。
BULKINSERTのWITHオプションのLASTROWの引数に変数を使用して
日々行数の変化するcsvの最終行-1の値をセットしたいのですが構文エラーに
なります。どのように記述したらいいのでしょうか?

試した事は

test.csv
1,あ,4200
2,い,5300
. ← 何かのデータ

CREATE PROCEDURE [dbo].[test1]
AS
bulk insert test_table
from 'C:\test.csv'
with
(firstrow = 1,formatfile = 'C:\Import_Fmt.xml')

で実行するとエラー「一括読み込み: データ ファイルで予期しないファイルの終了が検出されました。」になるので、最終行の「.」が原因かと思い。

ALTER PROCEDURE [dbo].[test1]
AS
bulk insert test_table
from 'C:\test.csv'
with
(firstrow = 1,lastrow = 2,formatfile = 'C:\Import_Fmt.xml')

で実行するとエラー無くインポートされました。

実際のcsvは行数10000件はあり日々増減しますので毎回手作業で数えることは難しいです。
そこで一度仮テーブルを作り改行コードまでを一つのカラムにインポートして
行数をカウントし、変数にセットしてLASTROWの引数に渡そうとして

ALTER PROCEDURE [dbo].[test1]
AS
declare @maxseq as int

drop table test2
create table test2(F1 varchar(max))

bulk insert test2
from 'C:\test.csv'
with
(firstrow = 1,formatfile = 'C:\ImportOrg_Fmt2.xml')

set @maxseq = (select count([F1]) from test2)
--ここにreturn @maxseqを記述して以降を削除して実行し、ストアドを実行したら
      --取り込んだcsvの行数が表示されました。
bulk insert test_table
from 'C:\data\test.csv'
with
(firstrow = 1,lastrow = @maxseq -1 ,formatfile = 'C:\ImportOrg_Fmt.xml')
と記述して実行すると
メッセージ 102、レベル 15、状態 1、プロシージャ test1、行 23
'@maxdeq' 付近に不適切な構文があります。
となりました。
どのように記述したら構文エラーにならずに実行できるかご回答をよろしくお願いします。

SQLserver 2005 Expressを使用してます。

csvをBULKINSERTでインポートする際にcsvの最終行に終了コードのようなデータが
入っていてインポートに失敗します。
BULKINSERTのWITHオプションのLASTROWの引数に変数を使用して
日々行数の変化するcsvの最終行-1の値をセットしたいのですが構文エラーに
なります。どのように記述したらいいのでしょうか?

試した事は

test.csv
1,あ,4200
2,い,5300
. ← 何かのデータ

CREATE PROCEDURE [dbo].[test1]
AS
bulk insert test_table
from 'C:\test.csv'
wi...続きを読む

Aベストアンサー

試してないけど
set @maxseq = @maxseq - 1;
bulk insert test_table
from 'C:\data\test.csv'
with
(firstrow = 1,lastrow = @maxseq,formatfile = 'C:\ImportOrg_Fmt.xml');
でうまくいくような気がする。

それでだめなら
declare cmdstr as varchar(2000);
set @maxseq = @maxseq - 1;
set cmdstr =
'bulk insert test_table
from ''C:\data\test.csv''
with
(firstrow = 1,lastrow = ' + @maxseq + ',formatfile = ''C:\ImportOrg_Fmt.xml'')';
exec (cmdstr);
といった感じで書く。
execは
http://msdn.microsoft.com/ja-jp/library/ms188332.aspx
参照。

QBCPユーティリティの使用法_ヘッダー情報の取得_

どなたかご存知の方教えてください。
bcp ユーティリティから、SQLサーバーのテーブルのファイルエクスポート(.txt)は出力できたのですが、データ列名が取得できていませんでした。
列名を取得することは可能なのでしょうか?
可能な場合、.fmt のファイルで設定するのでしょうか?
よろしくお願いします。

Aベストアンサー

キーワード「bcp 列名」でGoogle検索すると4番目に次のページがヒットしました,ご参考まで。
http://d.hatena.ne.jp/w650/20080215/p2

Qテーブル列数とデータファイル列数の違うbcpインサートをしたい

DBのテーブルの列数は258です。
インサートするデータファイルの列数は65です。
データファイルは","で区切られたファイルです。
SQL Serverは2005です。

コマンドを下記のように書きました。
SQLCMD -S"\SQLEXPRESS" -U"user" -P"pass" -d"計測" -Q"INSERT INTO [計測データ] SELECT 4,CONVERT(datetime,年月),データ1,データ2,データ3,データ4,データ5,・・・データ256 FROM OPENROWSET(BULK 'D:\200906.csv', FORMATFILE='D:\test.fmt',FIRSTROW = 2) x"

フォーマットファイルは、
8.0
68
1SQLCHAR01""0 Firsrt_QUOTE ""
2SQLCHAR020"\","1年月 ""
3SQLCHAR010","2データ1 ""
4SQLCHAR010","3データ2 ""
5SQLCHAR010","4データ3 ""
・・・
66SQLCHAR010"\r\n"65データ64 ""

しかしエラーになってしまいます。
そもそも列数が違うと無理なのでしょうか。

DBのテーブルの列数は258です。
インサートするデータファイルの列数は65です。
データファイルは","で区切られたファイルです。
SQL Serverは2005です。

コマンドを下記のように書きました。
SQLCMD -S"\SQLEXPRESS" -U"user" -P"pass" -d"計測" -Q"INSERT INTO [計測データ] SELECT 4,CONVERT(datetime,年月),データ1,データ2,データ3,データ4,データ5,・・・データ256 FROM OPENROWSET(BULK 'D:\200906.csv', FORMATFILE='D:\test.fmt',FIRSTROW = 2) x"

フォーマットファイルは、
8.0
68
1SQ...続きを読む

Aベストアンサー

bcpとsqlcmdによるOPENROWSETの実行は別物です。
同じようなことはできますが、まずは区別して認識しましょう。

OPENROWSETは、ファイルをテーブルに見立ててSELECTする方法です。
だから書いているクエリも"INSERT INTO ~ SELECT ~"です。
列数の少ないテーブルAから列数の多いテーブルBにINSERTするとき、どうしていますか?

INSERT INTO テーブルA
(列1,列2,列3,...,列10)
SELECT 列1,列2,列3,....,列10 FROM テーブルB

って書きますよね?
やっているのは全く同じことです。

QCSVファイルをBULK INSERTでSQLserverに読み込むことは可能?

BULK INSERT というものを使って
SQLServer2000上のテーブルに読込みたいのですが可能でしょうか?

その場合どうやって記述すればよいのでしょうか?
終端文字とかいうのは何になるのでしょう?

Windows2000
RDOで接続です。

Aベストアンサー

””で区切られたファイルのインポートですが、BULK INSERTでは、”を含んでロードしてしまいます。ですので、BULK INSERT を使用する場合は”を削除してからロードしないと駄目です。

ところで、<www7.big.or.jp/~pinball/discus/sqls/index.html>でも質問をしているようですが、どのような環境で何がしたいのか状況がつかめません。

詳しく教えていただけませんか?

Q処理件数を非表示にしたい

ManagementStudioでストアドプロシージャの開発をしています

開発中、デバッグのためにプロシージャをコールすると、SQLの発行の度に
「何件処理されました。」
「何件処理されました。」
「何件処理されました。」
「何件処理されました。」
「何件処理されました。」
・・・

と処理件数が表示され、確認の際に邪魔で困っています。
処理件数を非表示にするにはどうすれば良いでしょうか?

また同様に、SELECT文の発行ごとにSELECT結果がグリッドに表示されるのを非表示にできないでしょうか?

Aベストアンサー

処理件数を非表示にする方法

⇒SELECT文の前に「set nocount on」を記載すると件数は表示されません。

SELECT文の発行ごとにSELECT結果がグリッドに表示されるのを非表示にする方法

⇒デバッグのためには表示された方が良いと思いますが、
 とりあえず…SELECT文の前に「SET NOEXEC ON」を記載するとデバッグだけ実施され、
 グリッドは表示されません。
例であげると以下のとおりです。
SET NOCOUNT ON
SET NOEXEC NO
SELECT *
FROM DB.dbo.TableName
go

QCSVファイルの中で、「 , 」カンマを使いたい

「 , 」で区切られたCSVファイルの中で、「 , 」カンマを使いたいのですが、可能でしょうか?

具体的には「これは1,500円でした。」というように、CSVファイルに収められた文章内で出てくる半角の数字(お金)の区切りに使いたいのです。
全角では代用したくないのですが、CSVファイルでデータを受け渡しする際に、困っています。

例えば、特殊文字などで対応可能でしょうか?

Aベストアンサー

受け渡しに使うと言うことは相手方のアプリケーションのことも考えなければいけないのですが・・・とりあえず対応が簡単そうな方法を。

1.各セルを""で囲む。(もちろんデータにダブルクォートがある場合はカンマと同様に困ります。
2.カンマで区切らずにタブで区切る。(比較的使われない文字ですが、やはりデータ中にタブがあるとカンマと同様です)

難しいけれど完璧に対応するためには、データ中の区切り文字は特殊な文字列に変更し、受け取り側のアプリケーションではその特殊な文字列をデータ中の区切り文字として扱うという方法が使われます。
例えば、データ中のカンマは\,にするとか。

データ作成側、受け取り側でそれぞれどこまで対応できるのか分かるともっと簡単かつ具体的な方法を回答できるかも知れません。

QINSERT時に発番を行いたい

ATBLからBTBLへのINSERT時発番を行いたい

<環境>
SQLSERVER 2008

<ATBL>
CDvarchar(3)KEY
NOintKEY
NAMEvarchar(10)

<BTBL>
CDvarchar(3)KEY
NOintKEY
NAMEvarchar(10)

<BTBLのデータ>
CD   NO  NAME
0011太郎
0013次郎
0015三郎

BTBLをATBLへINSERTしたいのですが、この時、NOを1から再付番したいのです。
INSERTクエリで一発で更新できますでしょうか?
ご教授お願いします。

Aベストアンサー

row_numberで1からの連番ふれるよ

INSERT INTO ATBL(CD, NO, NAME)
SELECT CD, row_number() OVER(ORDER BY CD) AS NO, NAME FROM BTBL

参考URL:http://msdn.microsoft.com/ja-jp/library/ms186734.aspx


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング