BULKINSERTの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' 付近に不適切な構文があります。
となりました。
どのように記述したら構文エラーにならずに実行できるかご回答をよろしくお願いします。

回答No.2

Siegrune · ベストアンサー

試してないけど
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
参照。

お礼

補足を投稿した後にいろいろと試していて、
formatfileのパスは'''で囲むことでインポートすることができました。

解決できたのもexec ()の事を教えていただいたおかげです
ありがとうございました。

ALTER PROCEDURE [dbo].[test1]
AS
BEGIN
DECLARE @maxseq as int;
DECLARE @str001 as varchar(100);
DECLARE @str002 as varchar(100);
DECLARE @cmdstr as varchar(2000);

DROP TABLE test2
CREATE TABLE test2 ([F1] varchar(max),[F0] int IDENTITY(1,1) NOT NULL)
BULK INSERT dbo.test2
FROM 'c:\test.csv'
WITH
(
firstrow = 2,
formatfile = 'c:\ImportOrg_Fmt2.xml'
);

SET @maxseq = (SELECT max(F0) FROM test2 );
SET @maxseq = @maxseq -1;
set @str001 = 'lastrow = ' + cast(@maxseq as varchar(6));
set @str002 = 'formatfile = ''c:\ImportOrg_Fmt.xml''';

SET @cmdstr =
'BULK INSERT dbo.test_table
FROM "c:\test.csv"
WITH
(
firstrow = 2,
' + @str001 + ',
' + @str002 +'
)';

exec (@cmdstr);
END

補足

ご回答ありがとうございます。

いただいたアドバイスから、いろいろ試したので
ですが、うまくいっていません。
ALTER PROCEDURE [dbo].[test1]
AS
BEGIN
DECLARE @maxseq as int;

DROP TABLE test2
CREATE TABLE test2 ([F1] varchar(max),[F0] int IDENTITY(1,1) NOT NULL)
BULK INSERT dbo.test2
FROM 'c:\test.csv'
WITH
(
firstrow = 2,
formatfile = 'c:\ImportOrg_Fmt2.xml'
);

SET @maxseq = (SELECT max(F0) FROM test2 );
SET @maxseq = @maxseq -1;

BULK INSERT dbo.test_table
FROM 'c:\test.csv'
WITH
(
firstrow = 2,
lastrow = @maxseq,
formatfile = 'c:\tb_ImportOrg_Fmt2.xml'
);

END
で実行しましたが
メッセージ 102、レベル 15、状態 1、プロシージャ test0、行 24
'@maxseq' 付近に不適切な構文があります。

となりましたので、execを使用してみようと
ALTER PROCEDURE [dbo].[test1]
AS
BEGIN
DECLARE @maxseq as int;
DECLARE @cmdstr as varchar(2000);

DROP TABLE test2
CREATE TABLE test2 ([F1] varchar(max),[F0] int IDENTITY(1,1) NOT NULL)
BULK INSERT dbo.test2
FROM 'c:\test.csv'
WITH
(
firstrow = 2,
formatfile = 'c:\ImportOrg_Fmt2.xml'
);

SET @maxseq = (SELECT max(F0) FROM test2 );
SET @maxseq = @maxseq -1;

SET @cmdstr =
'BULK INSERT dbo.test_table
FROM "c:\test.csv"
WITH
(
firstrow = 2,
lastrow = ' + @maxseq + ',
formatfile = "c:\tb_ImportOrg_Fmt2.xml"
)';

exec (@cmdstr);

END
に変更して実行したところ
FROM "c:\test.csv"
WITH
(
firstrow = 2,
lastrow = ' をデータ型 int に変換できませんでした。
というエラーになりました。

もしかしてlastrow=@maseqを文字列としてセットすればいいのかと思い

ALTER PROCEDURE [dbo].[test1]
AS
BEGIN
DECLARE @maxseq as int;
DECLARE @str001 as varchar(100);
DECLARE @cmdstr as varchar(2000);

DROP TABLE test2
CREATE TABLE test2 ([F1] varchar(max),[F0] int IDENTITY(1,1) NOT NULL)
BULK INSERT dbo.test2
FROM 'c:\test.csv'
WITH
(
firstrow = 2,
formatfile = 'c:\ImportOrg_Fmt2.xml'
);

SET @maxseq = (SELECT max(F0) FROM test2 );
SET @maxseq = @maxseq -1;
set @str001 = 'lastrow = ' + cast(@maxseq as varchar(6));
print @str001;
--ここでlastrow = 2790が表示されます。

SET @cmdstr =
'BULK INSERT dbo.test_table
FROM "c:\test.csv"
WITH
(
firstrow = 2,
' + @str001 + ',
formatfile = "c:\ImportOrg_Fmt.xml"
)';

exec (@cmdstr);
END
で実行したところ
メッセージ 102、レベル 15、状態 1、行 7
'c:\ImportOrg_Fmt.xml' 付近に不適切な構文があります。
となりました。

formatfileのパスの指定部分が違っているのでエラーになっていると思うのですが、
この場合どのように書いたらいいのでしょうか？

回答No.1

arihina

頓珍漢な回答をしていたらスミマセン。

やはり、プログラムからSQLを発行しているのですよね。
そして、csvファイルは特定のものではないわけですね。
もしそうでしたら、毎回、csvファイルを別の場所へ最終行だけ取り除いてコピーすればいいかと思うんですがどうでしょう。
そのコピー先のcsvをBULK INSERTすればいいですし。
10000行くらいならなんとかなるのでは。
データのフォーマットチェックも同時に出来ますし。

お礼

ご回答ありがとうございます。

今回の問題が解決しない場合は、csvファイルをコピーして
最終行を取り除いて処理しようと思いますが
解決できるようにもう少し取り組もうと思います。

ありがとうございました。