ITブログ時々なんでもブログ

１．実装方法

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

２．使用方法

<?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>

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