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

[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 رو توضیح می‌دم.

اگه سوالی دارید یا تجربه‌ای از این روش دارین، خوشحال می‌شم نظراتتون رو کامنت کنین و در صورت نیاز کد را ارتقا بدید.

[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 شامل لیست فیلدها و شرط‌ها بوده که از پارامترها گرفته می‌شود.

[SayeyeZohor]

✅ کد اصلی


/*
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';

[SayeyeZohor]

✅تابع (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;

[SayeyeZohor]

✅ 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

[SayeyeZohor]

✅ 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

[SayeyeZohor]

✅ 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

[SayeyeZohor]

✅ 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

[SayeyeZohor]

نوع
نام
توضیح


Function


dbo.GetExcelFileInfo










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





Procedure


dbo.WriteExcelFileListToTable





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





Procedure


dbo.ProcessExcelData





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





Procedure


dbo.LoadExcelToStagingTable





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





Procedure


dbo.InsertFromStagingToFinalTable





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