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ベストアンサー
- 回答日時:
試してないけど
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 …
参照。
この回答への補足
ご回答ありがとうございます。
いただいたアドバイスから、いろいろ試したので
ですが、うまくいっていません。
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のパスの指定部分が違っているのでエラーになっていると思うのですが、
この場合どのように書いたらいいのでしょうか?
補足を投稿した後にいろいろと試していて、
formatfileのパスは'''で囲むことでインポートすることができました。
解決できたのもexec ()の事を教えていただいたおかげです
ありがとうございました。
ALTER PROCEDURE [dbo].[test1]
AS
BEGIN
DECLARE @maxseq as int;
DECLARE @str001 as varchar(100);
DECLARE @str002as 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
No.1
- 回答日時:
頓珍漢な回答をしていたらスミマセン。
やはり、プログラムからSQLを発行しているのですよね。
そして、csvファイルは特定のものではないわけですね。
もしそうでしたら、毎回、csvファイルを別の場所へ最終行だけ取り除いてコピーすればいいかと思うんですがどうでしょう。
そのコピー先のcsvをBULK INSERTすればいいですし。
10000行くらいならなんとかなるのでは。
データのフォーマットチェックも同時に出来ますし。
ご回答ありがとうございます。
今回の問題が解決しない場合は、csvファイルをコピーして
最終行を取り除いて処理しようと思いますが
解決できるようにもう少し取り組もうと思います。
ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Access(アクセス) access,vbaでフォルダ内のファイルをテーブルにインポート、ファイル名もフィールドに追加したい 1 2022/08/31 11:11
- Excel(エクセル) エクセルのVBAについて とあるサイトのコードを参考に、CSVの文字化けを直すVBAを作成しているの 7 2022/11/04 14:15
- その他(プログラミング・Web制作) python 気象データの取得 2 2023/06/20 23:54
- Visual Basic(VBA) 複数csvを横に追加していくマクロについて 2 2023/04/25 09:19
- Visual Basic(VBA) 複数のcsvファイルをExcelに一括変換したい 2 2023/03/03 12:44
- Excel(エクセル) CSVファイルがカンマ区切りにならない。対処法を教えていただきたいです。 仕事でSMS一斉送信ができ 2 2022/07/01 21:24
- その他(プログラミング・Web制作) VScodeでpythonプログラムの関数を実行したい 2 2022/07/13 19:24
- MySQL うまくいきません教えてくださいお願いしますSQLです。クエリ構文です。 1 2023/07/07 12:39
- MySQL SQLです。こんな感じですか?あってますか? うまくいきません教えてくださいお願いします 1 2023/07/08 15:27
- MySQL 次の時間帯の勝率の合計を求めるSQL文 1 2023/07/04 17:12
このQ&Aを見た人はこんなQ&Aも見ています
-
プロが教える店舗&オフィスのセキュリティ対策術
中・小規模の店舗やオフィスのセキュリティセキュリティ対策について、プロにどう対策すべきか 何を注意すべきかを教えていただきました!
-
SQLSever 一括インポートについて
その他(パソコン・スマホ・電化製品)
-
BULK INSERT時のNull許容について
SQL Server
-
テーブル列数とデータファイル列数の違うbcpインサートをしたい
SQL Server
-
-
4
BCPでCSV内の文字列をテーブルのDatetime型カラムに取り込む方法
その他(データベース)
-
5
Transact-SQLのBULK INSERTでエラーをキャッチしたい
SQL Server
-
6
BULK INSERTのエラー取得は可能なのでしょうか?
SQL Server
-
7
CSVファイルをBULK INSERTでSQLserverに読み込むことは可能?
その他(データベース)
-
8
BCPユーティリティの使用法_ヘッダー情報の取得_
その他(プログラミング・Web制作)
-
9
サーバーと実行端末が違う場合のsqlcmdの書き方
SQL Server
-
10
MS SQLServer のSQLで文字列の前にN:
その他(データベース)
-
11
T-SQLで任意の箇所で強制終了する方法
SQL Server
-
12
テキストファイルの最終行を削除したいのですが
その他(プログラミング・Web制作)
-
13
bcpインサートでのフォーマットファイルの書き方
SQL Server
-
14
14桁の日付(YYYYMMDDHHMMSS)を日付(YYYY/MM/DD)に変換できますか?
Oracle
-
15
DBの定義のサイズを大きくし過ぎると問題ある?
その他(データベース)
-
16
コマンドプロンプトの「%1」と「%~1」の違いがわからない
その他(プログラミング・Web制作)
-
17
処理件数を非表示にしたい
SQL Server
-
18
SQL SERVERの BULK INSERT
SQL Server
-
19
datetime型でNULL値を入れたい。
SQL Server
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
SQLServerでtime型への変換
-
BULKINSERTのWITHオプションに...
-
テーブルの結合(GROUP BY句の制...
-
【SQL】指定期間の合計、MAX...
-
差し込み後、元データを変更し...
-
フィルターかけた後、重複を除...
-
外部参照してるキーを主キーに...
-
for whichの使い方
-
エクセルで最後の文字だけ置き...
-
SQLで特定の項目の重複のみを排...
-
処理件数を非表示にしたい
-
SQL文で、合計が0のレコードを...
-
ExcelのVBAコードについて教え...
-
速度が低下し無効になったアド...
-
SELECT 文 GROUP での1件目を...
-
副問合せの書き方について
-
HTML電卓で1文字消す方法
-
sqlで、600行あるテーブルを100...
-
SQLで列名を変数にできないでし...
-
Exel VBA 別ブックから該当デ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
BULKINSERTのWITHオプションに...
-
片方のテーブルに無いデータを...
-
Access 2000 サブクエリとJOIN
-
日付型項目のNULLについて(Pos...
-
SQLServerでtime型への変換
-
VBA 100億になると#が自動...
-
【SQL】指定期間の合計、MAX...
-
データ突合のよい方法を教えて...
-
指定値を否定した条件で、NULL...
-
テーブルの結合(GROUP BY句の制...
-
プロシージャがみつかりません...
-
バッチでのバックアップに関して
-
差し込み後、元データを変更し...
-
フィルターかけた後、重複を除...
-
エクセルで最後の文字だけ置き...
-
SELECT 文 GROUP での1件目を...
-
SQLで特定の項目の重複のみを排...
-
for whichの使い方
-
外部参照してるキーを主キーに...
-
SQLServerで文字列の末尾からあ...
おすすめ情報