FC2ブログ

記事一覧

CSVファイルを動的にインポートする方法 (ファイル名とテーブル名を指定) -Transact SQL- | SQL Server


今回は SQL Server のストアドプロシージャにおいてCSVファイルをテーブルに取り込む方法について覚え書きします。基本的には BULK INSERT で取り込みが可能ですが、通常の記述方法ですとファイル名はハードコートで、インポートするテーブルも固定で記述になってしまい、使いまわしにくいコードとなってしましいます。そこで、ファイル名を変数で渡し、テーブル構造も動的に作成する方法を考えてみました。


sqlserver_bulkinsert_01.png


前提条件
・Windows 7 professional 以降 / Windows Server 2008 R2 以降
・SQL Server 2008 Developer / Standard / Enterprise 以降



1.実装方法

CSVの取り込みは BULK INSERT で行いますが、テーブル名を変数で渡すためには、動的なSQLで実行しなければなりません。
OPENROWSET を使用してフォーマットファイルからテーブルの構造のみ作成しています。本当はデータもINSERTできるのではと思いましたが、データが正しく入りません。よって、再度 BULK INSERT でデータをインポートしています。
また、取り込む際に、文字コードを指定できますが、異なる文字コードのファイルを取り込んだとしても桁数が違わない限りは取り込めてしまいますので、文字コードをチェックをするには、別途SQLCLRで文字コードのチェック関数を作成するしかありません。
さらには、MAXERRORS = 0を指定しておかないと、デフォルト10件までのエラー行が無視され、エラーハンドリングできません。


USE [testDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

DROP PROCEDURE IF EXISTS [dbo].[usp_ImportCsvFile]
GO

CREATE PROCEDURE [dbo].[usp_ImportCsvFile]
@FilePath VARCHAR(255) --CSVファイルパス
,@FormatFilePath VARCHAR(255) --フォーマットファイルパス
,@TableName VARCHAR(100) --作成するテーブル名
,@RetCode INT OUTPUT --エラーコード
,@RetMessage VARCHAR(2000) OUTPUT --エラーメッセージ
AS
BEGIN
SET NOCOUNT ON;

---------------------------------------------------------------------------
--変数定義
---------------------------------------------------------------------------
DECLARE @Sql NVARCHAR(1000);
DECLARE @IsExists INT;

---------------------------------------------------------------------------
--初期化
---------------------------------------------------------------------------
SET @RetCode = -1;
SET @Sql = '';
SET @IsExists = 0;

---------------------------------------------------------------------------
--ファイルの存在確認
---------------------------------------------------------------------------
EXEC master.dbo.xp_fileexist @FilePath, @IsExists OUTPUT;
IF @IsExists = 0
BEGIN
RETURN;
END;

BEGIN TRY

BEGIN TRANSACTION;

---------------------------------------------------------------------------
--取込テーブルの初期化(動的SQL)
---------------------------------------------------------------------------
SET @Sql = N' DROP TABLE IF EXISTS ' + @TableName + '; ';
EXEC sp_executesql @Sql;

---------------------------------------------------------------------------
--CSVファイルをインポート
---------------------------------------------------------------------------
--BULK INSERT
--https://msdn.microsoft.com/ja-jp/library/ms188365(v=sql.120).aspx
--BULK INSERT のFROM区に変数を使用したい場合は動的SQLにすると良いです。

--CSVからテーブルを作成(動的SQL)
SET @Sql = N' SELECT * ' +
N' INTO ' + @TableName +
N' FROM OPENROWSET ' +
N' (' +
N' BULK ''' + @FilePath + ''' ' +
N' ,FORMATFILE = ''' + @FormatFilePath + ''' ' +
N' ) AS T1; '
;
EXEC sp_executesql @Sql;

--CSVを取り込み(動的SQL)
SET @Sql = N' BULK INSERT ' + @TableName +
N' FROM ''' + @FilePath + ''' ' +
N' WITH (' +
N' FIELDTERMINATOR = '','' ' + --カンマ区切り タブ区切り(TSV)の場合は'\t'を入力
N' ,ROWTERMINATOR = ''\n'' ' + --改行コードで行終端
N' ,FIRSTROW = 2 ' + --ヘッダー行がある場合
N' ,KEEPNULLS ' + --NULLをそのまま取り込む場合
N' ,MAXERRORS = 0 ' + --エラー件数の許容
N' ,DATAFILETYPE = ''widechar''' + --全角を含む UTF-8Nの場合はcharにすると取り込めます。
N' ,CODEPAGE = ''65001''' + --65001:Unicode (UTF-8) / 932:Shift_JIS
N' ); '
;
EXEC sp_executesql @Sql;

COMMIT TRANSACTION;

SET @RetCode = 0;

END TRY
BEGIN CATCH
IF @@TRANCOUNT <> 0
BEGIN
ROLLBACK TRANSACTION;
END;

SET @RetCode = ERROR_NUMBER();
SET @RetMessage = ERROR_MESSAGE();
END CATCH;
END
GO


【参考URL】
文字コード(Code Page)について
https://msdn.microsoft.com/ja-jp/library/windows/desktop/dd317756(v=vs.85).aspx



2.使用方法

CSVファイルとフォーマットファイルを用意し、他のストアドプロシージャから呼び出します。

CSVのサンプルデータ
コード,名称,日付,フラグ
1,test1,2017/12/01,0
2,test2,2017/12/02,0
3,test3,2017/12/03,0
4,test4,2017/12/04,1
5,test5,2017/12/05,0

フォーマットファイルのサンプル
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="10"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="10"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="1"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="UserCode" xsi:type="SQLINT"/>
  <COLUMN SOURCE="2" NAME="UserName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="OrderDate" xsi:type="SQLDATETIME"/>
  <COLUMN SOURCE="4" NAME="DeleteFlag" xsi:type="SQLBIT"/>
 </ROW>
</BCPFORMAT>


CSVインポートのストアドプロシージャを呼び出します。

USE [testDB]
GO

DECLARE @return_value int,
@RetCode int,
@RetMessage VARCHAR(2000),
@TableName VARCHAR(20),
@Sql NVARCHAR(1000)

---------------------------------------------------------------------------
--テーブル名の定義
---------------------------------------------------------------------------
SET @TableName = 'W_CSV_IMPORTED_' + CONVERT(NVARCHAR(8), GETDATE(), 112);

---------------------------------------------------------------------------
--CSVをインポートするSPをコールする
---------------------------------------------------------------------------
EXEC @return_value = [dbo].[usp_ImportCsvFile]
@FilePath = N'C:\csvtest.csv',
@FormatFilePath = N'C:\sqlformat.xml',
@TableName = @TableName,
@RetCode = @RetCode OUTPUT,
@RetMessage = @RetMessage OUTPUT

---------------------------------------------------------------------------
--結果を取得してみる(動的SQL)
---------------------------------------------------------------------------
SET @Sql = N'SELECT * FROM ' + @TableName + '; ';
EXEC sp_executesql @Sql;

SELECT @RetCode as N'@RetCode'
,@RetMessage as N'@RetMessage'

SELECT 'Return Value' = @return_value
GO






最後までお読みいただき、ありがとうございます。
他にも SQL Server に関する記事をご紹介しておりますのでご参考頂ければ幸いです。


関連記事

コメント

コメントの投稿

カテゴリ別記事一覧

広告

プロフィール

石河 純


著者名 :石河 純
自己紹介:素人上がりのIT技術者。趣味は卓球・車・ボウリング

IT関連の知識はざっくりとこんな感じです。
【OS関連】
WindowsServer: 2012/2008R2/2003/2000/NT4
Windows: 10/8/7/XP/2000/me/NT4/98
Linux: CentOS RedHatLinux9
Mac: macOS Sierra 10.12 / OSX Lion 10.7.5 / OSX Snow Leopard 10.6.8
【言語・データベース】
VB.net ASP.NET C#.net Java VBA
Xamarin.Forms
Oracle10g SQLServer2008R2 SQLAnywhere8/11/16
ActiveReport CrystalReport ReportNet(IBM)
【ネットワーク関連】
CCNP シスコ技術者認定
Cisco Catalyst シリーズ
Yamaha RTXシリーズ
FireWall関連
【WEB関連】
SEO SEM CSS IIS6/7 apache2

休みの日は卓球をやっています。
現在、卓球用品通販ショップは休業中です。