انتقال دیتای موجود در یک شیت خاص از چندین فایل اکسل به دیتابیس اسکیوال سرور

Printable View

جمعه 19 اردیبهشت 1404, 01:28 صبح
SayeyeZohor

انتقال دیتای موجود در یک شیت خاص از چندین فایل اکسل به دیتابیس اسکیوال سرور
🔰 خواندن فایل Excel در SQL Server با استفاده از OPENROWSET
سلام دوستان عزیز 👋
همون‌طور که می‌دونید، روش‌های مختلفی برای خواندن فایل‌های Excel در SQL Server وجود داره.
در این آموزش قراره از روش محبوب و کاربردی OPENROWSET استفاده کنیم.
برای این کار چند مرحله‌ی ضروری رو باید انجام بدیم:

✅ مواد لازم:
- یک دیتابیس (در مثال ما: [DB_Ejraeiyaat2025])
- فایل Excel
- دسترسی به SQL Server با سطح کافی
- نصب بودن Microsoft Access Database Engine (نسخه ACE)
🔧 پیش‌نیازهای مهم:

1. فعال‌سازی قابلیت Ad Hoc Distributed Queries:

برای اینکه SQL Server اجازه استفاده از OPENROWSET رو بده، باید تنظیمات زیر رو اعمال کنیم:
```
EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;

EXEC sp_configure 'Ad Hoc Distributed Queries', 1;

RECONFIGURE;
```
2. نصب Microsoft Access Database Engine (ACE):

روی سرور SQL باید نسخه‌ی مناسب از Microsoft Access Database Engine (مثلاً 2016 یا 2010) نصب باشه تا SQL Server بتونه فایل‌های Excel رو بخونه.

3. بررسی دسترسی سرویس SQL Server:

مطمئن بشید که سرویس SQL Server دسترسی لازم برای ساخت یک Instance از Excel داره.
دو بخش مهم در این قسمت وجود داره:

⚙️ تنظیمات Provider در SSMS:

مسیر زیر رو در SQL Server Management Studio دنبال کن:

Object Explorer → Server Objects → Linked Servers → Providers → Microsoft.ACE.OLEDB.12.0 (یا 16.0) → راست‌کلیک → Properties

حالا تیک گزینه‌های زیر رو فعال کن:
- ✅ Allow inprocess
- ✅ Dynamic parameters
یا با اجرای دستورات زیر در SQL این تنظیمات رو فعال کن:
```
EXEC sp_MSset_oledb_prop 'Microsoft.ACE.OLEDB.16.0', N'AllowInProcess', 1;

EXEC sp_MSset_oledb_prop 'Microsoft.ACE.OLEDB.16.0', N'DynamicParameters', 1;
```
🎯 در ادامه‌ی این آموزش، نحوه‌ی نوشتن کوئری OPENROWSET و بارگذاری داده‌ها از Excel رو توضیح می‌دم.

اگه سوالی دارید یا تجربه‌ای از این روش دارین، خوشحال می‌شم نظراتتون رو کامنت کنین و در صورت نیاز کد را ارتقا بدید.
جمعه 19 اردیبهشت 1404, 01:43 صبح
SayeyeZohor

نقل قول: انتقال دیتای موجود در یک شیت خاص از چندین فایل اکسل به دیتابیس اسکیوال سرور
🟩 کد اصلی

عملکرد کلی:
- تنظیمات لازم برای OPENROWSET فعال می‌شود.
- لیست فایل‌های Excel از یک پوشه خوانده و در جدول موقت ذخیره می‌شود (#MyExcelFiles).
- سپس با استفاده از یک CURSOR روی هر فایل حلقه زده می‌شود.
- برای هر فایل، ابتدا نام کامل آن ساخته شده و به stored procedure ProcessExcelData پاس داده می‌شود.
- زمان اجرای کل فرآیند نیز اندازه‌گیری و چاپ می‌شود.
🟩 تابع (Function): dbo.GetExcelFileInfo

عملکرد:
- از مسیر کامل فایل، نام فایل (بدون پسوند) و نام پوشه والد استخراج می‌شود.
- نتیجه در قالب جدول (Table-Valued Function) برگردانده می‌شود.
هدف:
- نام فایل و نام پوشه به‌صورت اطلاعات کلیدی برای نام‌گذاری جدول staging و جدول نهایی استفاده می‌شود.
🟩 Stored Procedureها

✅ 1. dbo.WriteExcelFileListToTable

عملکرد:
- با استفاده از دستور xp_cmdshell، لیست فایل‌های Excel با پسوند .xls* از مسیر داده‌شده خوانده می‌شود.
- این لیست در جدول موقتی (مانند #MyExcelFiles) درج می‌شود.
  نکته امنیتی:
- نیاز به فعال بودن xp_cmdshell دارد که در سرورهای حساس باید با احتیاط استفاده شود.
✅ 2. dbo.ProcessExcelData

عملکرد:
- از تابع GetExcelFileInfo برای استخراج نام فایل و پوشه استفاده می‌کند.
- نام جدول staging و نام شیت Excel ساخته می‌شود.
- داده‌ها با استفاده از LoadExcelToStagingTable به جدول موقت منتقل می‌شوند.
- سپس داده‌ها از جدول موقت به جدول نهایی با استفاده از InsertFromStagingToFinalTable منتقل می‌شوند.
  ویژگی برجسته:
- کاملاً ماژولار طراحی شده؛ هر بخش کاری را به SP دیگر واگذار می‌کند.
✅ 3. dbo.LoadExcelToStagingTable

عملکرد:
- اگر جدول staging قبلاً وجود دارد، آن را حذف می‌کند.
- سپس داده‌ها از شیت Excel با استفاده از OPENROWSET به جدول staging منتقل می‌شوند.
  پیش‌نیاز:
- نصب بودن OLEDB Provider (مثلاً Microsoft.ACE.OLEDB.16.0)
- تنظیمات لازم برای اجازه اجرای OPENROWSET
✅ 4. dbo.InsertFromStagingToFinalTable

عملکرد:
- اجرای کوئری dynamic برای انتقال داده از جدول staging به جدول نهایی.
- کوئری SELECT شامل لیست فیلدها و شرط‌ها بوده که از پارامترها گرفته می‌شود.

نقل قول: انتقال دیتای موجود در یک شیت خاص از چندین فایل اکسل به دیتابیس اسکیوال سرور

✅ کد اصلی

/*

    FileName ba batch file convert shavad ...

*/





USE [DB_Ejraeiyaat2025]

GO









-- OPENROWSET فعال‌سازی تنظیمات لازم برای 

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;

EXEC sp_configure 'Ad Hoc Distributed Queries', 1;

RECONFIGURE;









DECLARE @FolderPath NVARCHAR(MAX);

DECLARE @ExcelFullPath NVARCHAR(MAX);

DECLARE @sqlFIELDS NVARCHAR(MAX);            -- RWSHID, ....

DECLARE @sqlWHEREStatement NVARCHAR(MAX);





DECLARE @StartTime DATETIME2 = SYSDATETIME();





SET @FolderPath            = N'E:\- PowerBI\1404-01-30\Data4Import\08-Taghsit\';

SET @sqlFIELDS            = N'ROW_NUMBER() OVER (ORDER BY ORD_ORDNO) AS RowNum, * ';

SET @sqlWHEREStatement    = N'WHERE ORD_ORDNO IS NOT NULL';





/*

SET @sqlFIELDS  = N'

    ROW_NUMBER() OVER (ORDER BY RWSHID) AS RowNum, RWSHID, RWSHNAME, ACTIVITYCODE, WSHSTATUSDESC, NATIONDESC, 

    ACTPERIODDESC, WSHCHARDESC, WSHRATEDESC, ACTIVITYDESC, WSHTYPEDESC, DIRECTORORGDESC, RECOGMETHODDESC, 

    SNDLISTPERIODDESC, SNDLISTMETHODDESC, BRHNAME, BLD_MANG';

*/





-- مرحله 1: حذف جدول موقت اگه از قبل وجود داره

IF OBJECT_ID('tempdb..#MyExcelFiles') IS NOT NULL

    DROP TABLE #MyExcelFiles;





-- مرحله 2: ایجاد جدول موقت

CREATE TABLE #MyExcelFiles (FileName NVARCHAR(1000));





-- مرحله 3: پر کردن جدول موقت با لیست فایل‌ها

EXEC dbo.WriteExcelFileListToTable

    @FolderPath,

    @TableName  = N'#MyExcelFiles';





-- مرحله 4: تعریف متغیر برای نگهداری فایل جاری

DECLARE @CurrentFile NVARCHAR(1000);





-- cursor مرحله 5: تعریف 

DECLARE file_cursor CURSOR FOR

SELECT FileName FROM #MyExcelFiles;





--و خواندن سطر اول cursor مرحله 6: باز کردن  

OPEN file_cursor;

FETCH NEXT FROM file_cursor INTO @CurrentFile;





-- مرحله 7: حلقه روی فایل‌ها

WHILE @@FETCH_STATUS = 0

BEGIN

    PRINT '';

    PRINT '=================================================  ==================================================  ======================';

    DECLARE @Msg NVARCHAR(MAX);

    SET @Msg = N'✅ [--- NEXT --- Processing file: ' + @CurrentFile + ']';

    RAISERROR(@Msg, 10, 1) WITH NOWAIT;

    SET @ExcelFullPath = @FolderPath+@CurrentFile;

    EXEC dbo.ProcessExcelData @ExcelFullPath, @sqlFIELDS, @sqlWHEREStatement





    WAITFOR DELAY '00:00:02';





    -- خواندن فایل بعدی

    FETCH NEXT FROM file_cursor INTO @CurrentFile;

END





-- cursor مرحله 8: بستن و آزادسازی 

CLOSE file_cursor;

DEALLOCATE file_cursor;





DECLARE @EndTime DATETIME2    = SYSDATETIME();

DECLARE @ElapsedSeconds INT = DATEDIFF(SECOND, @StartTime, @EndTime);

PRINT '⏱ ALL Time elapsed: ' + CAST(@ElapsedSeconds AS NVARCHAR) + ' seconds';

نقل قول: انتقال دیتای موجود در یک شیت خاص از چندین فایل اکسل به دیتابیس اسکیوال سرور

✅تابع (Function): dbo.GetExcelFileInfo

USE [DB_Ejraeiyaat2025]

GO

/****** Object:  UserDefinedFunction [dbo].[GetExcelFileInfo]    Script Date: 19/02/1404 01:16:59 ق.ظ ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[GetExcelFileInfo]

(

    @FullPath NVARCHAR(1000)

)

RETURNS @Result TABLE

(

    FileNameWithoutExt NVARCHAR(255),

    ParentFolder NVARCHAR(255)

)

AS

BEGIN

    DECLARE @TrimmedPath NVARCHAR(1000) = @FullPath;





    -- حذف کاراکتر \ انتهایی (اگر وجود داشته باشد)

    IF RIGHT(@TrimmedPath, 1) = '\'

        SET @TrimmedPath = LEFT(@TrimmedPath, LEN(@TrimmedPath) - 1);





    -- استخراج نام فایل (مثلاً 1920.xls یا 1920.xlsx)

    DECLARE @FileNameWithExt NVARCHAR(255) = REVERSE(LEFT(REVERSE(@TrimmedPath), CHARINDEX('\', REVERSE(@TrimmedPath)) - 1));





    -- حذف پسوند فایل

    DECLARE @FileNameWithoutExt NVARCHAR(255) = 

        LEFT(@FileNameWithExt, LEN(@FileNameWithExt) - 

            CHARINDEX('.', REVERSE(@FileNameWithExt)));





    -- استخراج پوشه‌ی والد

    DECLARE @PathWithoutFile NVARCHAR(1000) = LEFT(@TrimmedPath, LEN(@TrimmedPath) - LEN(@FileNameWithExt) - 1);

    DECLARE @ParentFolder NVARCHAR(255) = REVERSE(LEFT(REVERSE(@PathWithoutFile), CHARINDEX('\', REVERSE(@PathWithoutFile)) - 1));





    -- درج در جدول خروجی

    INSERT INTO @Result (FileNameWithoutExt, ParentFolder)

    VALUES (@FileNameWithoutExt, @ParentFolder);





    RETURN;

END;

نقل قول: انتقال دیتای موجود در یک شیت خاص از چندین فایل اکسل به دیتابیس اسکیوال سرور

✅ 1. dbo.WriteExcelFileListToTable

USE [DB_Ejraeiyaat2025]

GO

/****** Object:  StoredProcedure [dbo].[WriteExcelFileListToTable]    Script Date: 19/02/1404 01:17:45 ق.ظ ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER   PROCEDURE [dbo].[WriteExcelFileListToTable]

    @FolderPath NVARCHAR(MAX),

    @TableName NVARCHAR(128)

AS

BEGIN

    DECLARE @Cmd NVARCHAR(MAX);

    DECLARE @CmdExec VARCHAR(8000);





    SET @Cmd = 'dir "' + @FolderPath + '*.xls*" /b';

    SET @CmdExec = CAST(@Cmd AS VARCHAR(8000));





    DECLARE @SQL NVARCHAR(MAX) = N'

        INSERT INTO ' + QUOTENAME(@TableName) + ' (FileName)

        EXEC xp_cmdshell ''' + @CmdExec + '''; 

        DELETE FROM ' + QUOTENAME(@TableName) + ' WHERE FileName IS NULL OR LTRIM(RTRIM(FileName)) = '''';

    ';

    EXEC sp_executesql @SQL;

END

نقل قول: انتقال دیتای موجود در یک شیت خاص از چندین فایل اکسل به دیتابیس اسکیوال سرور

✅ 2. dbo.ProcessExcelData

USE [DB_Ejraeiyaat2025]

GO

/****** Object:  StoredProcedure [dbo].[ProcessExcelData]    Script Date: 19/02/1404 01:18:27 ق.ظ ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[ProcessExcelData]

    @ExcelFullPath NVARCHAR(MAX),

    @sqlFIELDS NVARCHAR(MAX),

	@sqlWHEREStatement NVARCHAR(MAX)

AS

BEGIN

    SET NOCOUNT ON;





    DECLARE @ShobeCode NVARCHAR(255);

    DECLARE @TableName NVARCHAR(255);

    DECLARE @StageTableName NVARCHAR(255);

    DECLARE @SheetName NVARCHAR(255);





    BEGIN TRY

        -- گرفتن اطلاعات از تابع کمکی

        SELECT

            @ShobeCode = FileNameWithoutExt,

            @TableName = ParentFolder

        FROM dbo.GetExcelFileInfo(@ExcelFullPath);





        -- ساخت StageTableName

        SET @StageTableName = 'Excel_StagTable_' + @TableName + '_' + @ShobeCode;





        -- ساخت نام شیت Excel

        SET @SheetName = 'Exportxls' + REPLACE(@ShobeCode, '.', '') + 'xls$';





        PRINT '=================================================  ==================================================  ======================'

        PRINT ''

        PRINT ''

        PRINT '========================================== ' + @ShobeCode + ' ==============================================';





        RAISERROR(N'✅ [... Excel شروع خواندن ساختار و داده ها از]', 10, 1) WITH NOWAIT;





        -- Insert From ExcelFile To StagingTable

        EXEC dbo.LoadExcelToStagingTable

            @ExcelFullPath, @SheetName, @StageTableName, @ShobeCode;





        WAITFOR DELAY '00:00:02';





        RAISERROR(N'✅ [... به پایان رسید Excel خواندن ساختار و داده ها از]', 10, 1) WITH NOWAIT;





        PRINT ''

        PRINT '=================================================  ======================================'

        PRINT ''





        RAISERROR(N'✅ [... شروع انتقال داده ها از جدول موقت به جدول اصلی]', 10, 1) WITH NOWAIT;





        -- Insert From StagingTable To FinalTable

        EXEC dbo.InsertFromStagingToFinalTable

            @FieldsStatement = @sqlFIELDS,

            @INTOTable = @TableName,

            @FROMTable = @StageTableName,

            @WHEREStatement = @sqlWHEREStatement; --'WHERE RWSHID IS NOT NULL'





        WAITFOR DELAY '00:00:02';





        RAISERROR(N'✅ [... انتقال داده ها از جدول موقت به جدول اصلی به پایان رسید]', 10, 1) WITH NOWAIT;

        PRINT '^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ' + @ShobeCode + ' ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^';





    END TRY

    BEGIN CATCH

        PRINT '';

        PRINT '';

        PRINT '❌ Error occurred during process for file: ' + @ExcelFullPath;

        THROW;

    END CATCH

END

نقل قول: انتقال دیتای موجود در یک شیت خاص از چندین فایل اکسل به دیتابیس اسکیوال سرور

✅ 3. dbo.LoadExcelToStagingTable

USE [DB_Ejraeiyaat2025]

GO

/****** Object:  StoredProcedure [dbo].[LoadExcelToStagingTable]    Script Date: 19/02/1404 01:18:52 ق.ظ ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[LoadExcelToStagingTable]

    @ExcelFullPath NVARCHAR(MAX),

    @SheetName NVARCHAR(100),

    @TableName NVARCHAR(255),

    @ShobeCode NVARCHAR(255)

AS

BEGIN

    SET NOCOUNT ON;





	DECLARE @StartTime DATETIME2 = SYSDATETIME();





    BEGIN TRY

        DECLARE @SQL NVARCHAR(MAX);

        DECLARE @TableOnlyName NVARCHAR(255);





        -- OBJECT_ID حذف براکت از اسم جدول در بررسی 

        SET @TableOnlyName = REPLACE(REPLACE(@TableName, '[', ''), ']', '');





        -- حذف جدول staging اگر قبلاً وجود داشته

        IF OBJECT_ID(@TableOnlyName, 'U') IS NOT NULL

        BEGIN

            SET @SQL = 'DROP TABLE ' + QUOTENAME(@TableOnlyName) + ';';

            EXEC sp_executesql @SQL;

        END





        -- ساخت کوئری برای خواندن از فایل اکسل

        SET @SQL = '

            SELECT *

            INTO ' + QUOTENAME(@TableOnlyName) + '

            FROM OPENROWSET(

                ''Microsoft.ACE.OLEDB.16.0'',

                ''Excel 12.0;HDR=YES;Database=' + @ExcelFullPath + ''',

                ''SELECT * FROM [' + @SheetName + ']''

            );

        ';





        -- اجرای کوئری داینامیک

        EXEC sp_executesql @SQL;





		DECLARE @EndTime DATETIME2	= SYSDATETIME();

		DECLARE @ElapsedSeconds INT = DATEDIFF(SECOND, @StartTime, @EndTime);





		--PRINT '';

		--PRINT '';

        PRINT '✅ Data loaded successfully into staging table: ' + @TableOnlyName;

		PRINT '⏱ Time elapsed: ' + CAST(@ElapsedSeconds AS NVARCHAR) + ' seconds';

    END TRY

    BEGIN CATCH

		--PRINT '';

		--PRINT '';

        PRINT '❌ Error occurred while loading data into staging table: ' + @TableName;

		PRINT '⏱ Time elapsed: ' + CAST(@ElapsedSeconds AS NVARCHAR) + ' seconds';

        THROW;

    END CATCH

END

نقل قول: انتقال دیتای موجود در یک شیت خاص از چندین فایل اکسل به دیتابیس اسکیوال سرور

✅ 4. dbo.InsertFromStagingToFinalTable

USE [DB_Ejraeiyaat2025]

GO

/****** Object:  StoredProcedure [dbo].[InsertFromStagingToFinalTable]    Script Date: 19/02/1404 01:19:31 ق.ظ ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[InsertFromStagingToFinalTable]

    @FieldsStatement NVARCHAR(MAX),

    @INTOTable NVARCHAR(255),

    @FROMTable NVARCHAR(255),

    @WHEREStatement NVARCHAR(MAX) = ''

AS

BEGIN

    SET NOCOUNT ON;





    DECLARE @SQL NVARCHAR(MAX);

    DECLARE @RowsInserted INT;

    DECLARE @CountSQL NVARCHAR(MAX);

    DECLARE @TotalCount INT;

	DECLARE @StartTime DATETIME2 = SYSDATETIME();





    BEGIN TRY

        IF OBJECT_ID(@INTOTable, 'U') IS NOT NULL

        BEGIN

            SET @SQL = '

                DECLARE @RC INT;





                INSERT INTO ' + QUOTENAME(@INTOTable) + --' (' + @FieldsStatement + ')' +

                'SELECT ' + @FieldsStatement + '

                FROM ' + QUOTENAME(@FROMTable) + ' ' + @WHEREStatement + ';





                SET @RC = @@ROWCOUNT;

                SELECT @RC AS InsertedCount;';

        END

        ELSE

        BEGIN

            SET @SQL = '

                DECLARE @RC INT;





                SELECT ' + @FieldsStatement + '

                INTO ' + QUOTENAME(@INTOTable) + '

                FROM ' + QUOTENAME(@FROMTable) + ' ' + @WHEREStatement + ';





                SET @RC = @@ROWCOUNT;

                SELECT @RC AS InsertedCount;';

        END





        -- اجرای کوئری و گرفتن نتیجه

        CREATE TABLE #TempRowCount(InsertedCount INT);

        INSERT INTO #TempRowCount

        EXEC (@SQL);





        SELECT @RowsInserted = InsertedCount FROM #TempRowCount;

        DROP TABLE #TempRowCount;





        SET @CountSQL = 'SELECT @TotalCountOut = COUNT(*) FROM ' + QUOTENAME(@INTOTable);





        EXEC sp_executesql

            @CountSQL,

            N'@TotalCountOut INT OUTPUT',

            @TotalCountOut = @TotalCount OUTPUT;









        IF OBJECT_ID(@FROMTable, 'U') IS NOT NULL

        BEGIN

            SET @SQL = 'DROP TABLE ' + QUOTENAME(@FROMTable) + ';';

            EXEC sp_executesql @SQL;

        END





		DECLARE @EndTime DATETIME2	= SYSDATETIME();

		DECLARE @ElapsedSeconds INT = DATEDIFF(SECOND, @StartTime, @EndTime);









		--PRINT '';

		--PRINT '';

        PRINT '✅ Insert completed successfully. Insert From ' + @FROMTable + ' INTO ' + @INTOTable;

        PRINT '➡️ Records inserted: ' + CAST(@RowsInserted AS NVARCHAR);

		PRINT '➡️ All records in [' + @INTOTable + ']: ' + CAST(@TotalCount AS NVARCHAR);

		PRINT '⏱ Time elapsed: ' + CAST(@ElapsedSeconds AS NVARCHAR) + ' seconds';

    END TRY

    BEGIN CATCH

		--PRINT '';

		--PRINT '';

        PRINT '❌ Error occurred during insert into ' + @INTOTable;

		PRINT '⏱ Time elapsed: ' + CAST(@ElapsedSeconds AS NVARCHAR) + ' seconds';

        THROW;

    END CATCH

END

نقل قول: انتقال دیتای موجود در یک شیت خاص از چندین فایل اکسل به دیتابیس اسکیوال سرور

نوع نام توضیح

Function
dbo.GetExcelFileInfo

استخراج نام فایل و پوشه از مسیر کامل فایل

Procedure
dbo.WriteExcelFileListToTable

خواندن فایل‌های Excel در یک پوشه و نوشتن در جدول موقت

Procedure
dbo.ProcessExcelData

پردازش کامل یک فایل Excel از استخراج، انتقال تا درج نهایی

Procedure
dbo.LoadExcelToStagingTable

بارگذاری داده‌ها از Excel به جدول staging با OPENROWSET

Procedure
dbo.InsertFromStagingToFinalTable

انتقال داده‌ها از جدول staging به جدول اصلی با فیلدها و شرط دلخواه