FC2ブログ

記事一覧

Excelをインポートする方法 -Transact SQL- | SQL Server


今回は SQL Server でTransact-SQLのみでプロシージャとして使用が可能なエクセルインポート方法を覚え書きします。
Excelを取り込む方法といっても検索するとウィザードでとかVBでとかばかりで参考にならないものが多かったです。
業務系のDBサーバーではExcelをバッチで取り込みたいなど要望が多いと思います。VBで作成するのも手ですが、今一つお手軽感がありません。
 sqlserver_import_excel_01.png



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



1.AccessDatabaseEngine

以下のURLからAccessDatabaseEngineをインストールします。
https://www.microsoft.com/ja-jp/download/details.aspx?id=13255

Officeがインストールされている場合は、Officeが32Bitの場合、AccessDatabaseEngineも32Bitでインストールしなければなりません。
※コマンドプロンプトから/passive オプションを指定して強制的に64Bitをインストールすることも可能の様ですが、私の環境ではWindows10(64Bit)&SQLServer2016(64Bit)にOffice2003(32Bit)でしたが、AccessDatabaseEngineの32Bitで動作しました。



2.SQL Serverの設定変更

次に、以下のようにMicrosoft.ACE.OLEDB.12.0が使用できるようにSQL Serverの設定を変更します。

--SQL Server によって、コンポーネント 'Ad Hoc Distributed Queries' の 
--STATEMENT 'OpenRowset/OpenDatasource' に対するアクセスがブロックされました。
--このサーバーのセキュリティ構成で、このコンポーネントが OFF に設定されているためです。
--システム管理者は sp_configure を使用して、'Ad Hoc Distributed Queries' の使用を有効にできます。
--'Ad Hoc Distributed Queries' を有効にする手順の詳細については、SQL Server オンライン ブックで、
--'Ad Hoc Distributed Queries' を検索してください。
USE master
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess', 1;
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters', 1;
RECONFIGURE;
GO



3.実装方法

OPENROWSETを使用します。テーブル名やエクセルブック名・シート名・取込範囲・取込条件など、文字列で渡す必要があるため、動的SQLを発行します。

 
USE testDB
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP PROCEDURE IF EXISTS dbo.usp_ImportExcel;
GO
CREATE PROCEDURE dbo.usp_ImportExcel
     @TableName NVARCHAR(255)   --テーブル名:'WK_IMPORT_XLS'
    ,@FilePath  NVARCHAR(255)   --エクセルファイルフルパス:'C:\excelworkbook.xlsx'
    ,@SheetName NVARCHAR(255)   --エクセルシート名:'Sheet1'
    ,@Range     NVARCHAR(5)     --取込範囲:'A2:Z'
    ,@Where     NVARCHAR(1000)  --条件指定:'OrderCode IS NOT NULL OR CustomerName IS NOT NULL '
    ,@RetCode   INT             OUTPUT
    ,@RetMsg    VARCHAR(MAX)    OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
   
    DECLARE @SQL NVARCHAR(1000);
    SET @SQL = '';
   
    BEGIN TRY

        --既存ワークテーブルの削除
        SET @SQL = 'DROP TABLE IF EXISTS ' + @TableName + ';';
        EXECUTE sp_executesql @SQL;

        --OPENROWSET
        --https://docs.microsoft.com/ja-jp/sql/t-sql/functions/openrowset-transact-sql
       
        SET @SQL = ''
        + ' SELECT * '
        + ' INTO  ' + @TableName
        + ' FROM   OPENROWSET ( '
        + '      ''Microsoft.ACE.OLEDB.12.0''    '
        + '     ,''Excel 12.0;Database=' + @FilePath + ''' '
        + '     ,''SELECT * '
        + '        FROM   [' + @SheetName + '$' + @Range + ']'
        ;
            IF ISNULL(@Where, '') <> ''
                BEGIN
                    SET @SQL += ' WHERE ' + @Where ;
                END;
        SET @SQL += ' '' ); ';
               
        --OLE DB プロバイダー "Microsoft.ACE.OLEDB.12.0" が登録されていません。
        --AccessDatabaseEngine.exe 32bit/64bit
        --https://www.microsoft.com/ja-jp/download/details.aspx?id=13255

        EXECUTE sp_executesql @SQL;

    END TRY
    BEGIN CATCH
        SET @RetCode = ERROR_NUMBER();
        SET @RetMsg  = ERROR_MESSAGE();
    END CATCH;
END
GO



4.使用方法

以下のようにストアドプロシージャに引数を指定して実行します。

USE [testDB]
GO
DECLARE
@return_value int
       ,@RetCode    int
       ,@RetMsg       varchar(max)

EXEC @return_value = [dbo].[usp_ImportExcel]
        @TableName  = N'WK_IMPORT_XLS',
        @FilePath     = N'D:\エクセル取込サンプルデータ.xls',
        @SheetName = N'取込シート',
        @Range        = N'A2:G',
        @Where        = N'OrderCode IS NOT NULL OR CustomerCode IS NOT NULL',
        @RetCode      = @RetCode OUTPUT,
        @RetMsg       = @RetMsg OUTPUT

SELECT
        @RetCode as N'@RetCode',
        @RetMsg  as N'@RetMsg'

SELECT  'Return Value' = @return_value
GO


エクセルはこんな感じです。

sqlserver_import_excel_02.png




最後までお読みいただき、ありがとうございます。
他にも 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

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