ایجاد و بارگذاری جداول SQL Server از CSV

اتوماسیون ایجاد و بارگذاری جداول SQL Server از فایل‌های CSV

مدیریت و بارگذاری فایل‌های CSV به داخل SQL Server، به‌ویژه زمانی که ساختار فایل‌ها (نام ستون‌ها یا ترتیب آن‌ها) به‌طور مکرر تغییر می‌کند، می‌تواند به فرآیندی تکراری و زمان‌بر تبدیل شود. این مقاله رویکردی را برای اتوماسیون کامل این فرآیند با استفاده از یک رویه ذخیره شده (Stored Procedure) در SQL Server ارائه می‌دهد که هم جدول را بر اساس سربرگ فایل CSV ایجاد می‌کند و هم داده‌ها را به آن بارگذاری می‌کند.

برای شروع، رویه ذخیره شده‌ای را تعریف می‌کنیم که مسئول انجام تمام مراحل است. این رویه سه پارامتر اصلی را دریافت می‌کند:

• @fullFilePath: مسیر کامل فایل CSV شامل نام فایل و پسوند.
• @tableName: نام جدولی که قرار است در پایگاه داده ایجاد یا داده‌ها در آن بارگذاری شوند.
• @firstRowHasHeaders: یک پرچم (بیتی) که مشخص می‌کند آیا ردیف اول فایل CSV حاوی سربرگ ستون‌ها است یا خیر.

در ادامه، کد رویه ذخیره شده را مشاهده می‌کنید که شامل بخش‌های مختلفی برای مدیریت خطا، ایجاد جدول به‌صورت پویا و بارگذاری داده‌ها است.

CREATE PROCEDURE [dbo].[usp_CreateAndLoadTableFromCSV]
    @fullFilePath NVARCHAR(MAX),
    @tableName NVARCHAR(255),
    @firstRowHasHeaders BIT = 1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(MAX);
    DECLARE @ColumnNames NVARCHAR(MAX);
    DECLARE @HeaderLine NVARCHAR(MAX);
    DECLARE @Delimiter NVARCHAR(1) = ','; -- فرض بر این است که جداکننده کاما است

    -- این دستورات را برای مشاهده مقادیر در حین اشکال زدایی می‌توانید فعال کنید
    -- SELECT @fullFilePath AS FullFilePath, @tableName AS TableName, @firstRowHasHeaders AS FirstRowHasHeaders;

    BEGIN TRY
        -- دریافت خط اول (سربرگ) از فایل CSV
        SELECT @HeaderLine = BulkColumn
        FROM OPENROWSET(BULK @fullFilePath, SINGLE_CLOB) AS x
        WHERE BulkColumn LIKE '%'; -- این شرط به ما اطمینان می‌دهد که خطی را انتخاب کنیم

        -- استخراج نام ستون‌ها و ساختار جدول
        IF @firstRowHasHeaders = 1
        BEGIN
            -- فرض می‌کنیم همه ستون‌ها در ابتدا NVARCHAR(MAX) هستند
            SELECT @ColumnNames = STUFF(
                (SELECT ',' + QUOTENAME(value) + ' NVARCHAR(MAX)'
                 FROM STRING_SPLIT(@HeaderLine, @Delimiter)
                 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''
            );
        END
        ELSE
        BEGIN
            -- اگر سربرگ نداریم، نام ستون‌ها را به صورت Col1, Col2 و ... ایجاد می‌کنیم
            DECLARE @i INT = 1;
            DECLARE @TempColumnNames TABLE (ColumnName NVARCHAR(255));
            INSERT INTO @TempColumnNames (ColumnName)
            SELECT 'Col' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS NVARCHAR(10))
            FROM STRING_SPLIT(@HeaderLine, @Delimiter);

            SELECT @ColumnNames = STUFF(
                (SELECT ',' + QUOTENAME(ColumnName) + ' NVARCHAR(MAX)'
                 FROM @TempColumnNames
                 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''
            );
        END;

        -- ساخت دستور DROP TABLE (اختیاری: اگر جدول از قبل وجود دارد آن را حذف کند)
        SET @SQL = 'IF OBJECT_ID(N''[dbo].' + QUOTENAME(@tableName) + ''', N''U'') IS NOT NULL DROP TABLE [dbo].' + QUOTENAME(@tableName) + ';';
        EXEC sp_executesql @SQL;

        -- ساخت دستور CREATE TABLE
        SET @SQL = 'CREATE TABLE [dbo].' + QUOTENAME(@tableName) + ' (' + @ColumnNames + ');';
        EXEC sp_executesql @SQL;

        -- بارگذاری داده‌ها با استفاده از BULK INSERT
        SET @SQL = 'BULK INSERT [dbo].' + QUOTENAME(@tableName) +
                   ' FROM ''' + @fullFilePath + '''' +
                   ' WITH (
                       FIELDTERMINATOR = ''' + @Delimiter + ''',
                       ROWTERMINATOR = ''' + CHAR(10) + '''' + -- برای خط جدید لینوکس/یونیکس
                       CASE WHEN @firstRowHasHeaders = 1 THEN ', FIRSTROW = 2' ELSE '' END +
                   ');';
        EXEC sp_executesql @SQL;

        PRINT 'جدول ' + QUOTENAME(@tableName) + ' با موفقیت ایجاد و داده‌ها از فایل ' + @fullFilePath + ' بارگذاری شدند.';

    END TRY
    BEGIN CATCH
        -- مدیریت خطا
        DECLARE @ErrorMessage NVARCHAR(MAX), @ErrorSeverity INT, @ErrorState INT;
        SELECT
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE();

        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH;
END;

این رویه ذخیره شده به شرح زیر عمل می‌کند:

1. تنظیمات اولیه و مدیریت خطا:
   • SET NOCOUNT ON; برای جلوگیری از ارسال پیام‌های مربوط به تعداد ردیف‌های تحت تأثیر.
   • بلوک BEGIN TRY...END TRY برای اجرای کد اصلی و BEGIN CATCH...END CATCH برای مدیریت هرگونه خطا.
2. خواندن سربرگ فایل CSV:
   • با استفاده از OPENROWSET(BULK ...)، اولین خط فایل CSV به عنوان @HeaderLine خوانده می‌شود. این خط برای استخراج نام ستون‌ها استفاده خواهد شد.
3. ساخت پویای دستور CREATE TABLE:
   • اگر @firstRowHasHeaders برابر با 1 باشد (یعنی فایل سربرگ دارد)، رویه از STRING_SPLIT برای تقسیم @HeaderLine بر اساس جداکننده (کاما) استفاده می‌کند. سپس برای هر بخش، یک نام ستون با نوع داده NVARCHAR(MAX) و به صورت نقل قول شده (با QUOTENAME) ایجاد می‌کند. این کار تضمین می‌کند که نام ستون‌ها حتی اگر حاوی کاراکترهای خاص باشند، معتبر باقی بمانند.
   • اگر فایل سربرگ نداشته باشد، رویه نام ستون‌ها را به صورت پیش‌فرض (مثلاً Col1, Col2 و …) ایجاد می‌کند.
   • دستورات DROP TABLE و CREATE TABLE به صورت پویا ساخته و اجرا می‌شوند تا جدول با ساختار دلخواه ایجاد شود. این تضمین می‌کند که هر بار که رویه فراخوانی می‌شود، یک جدول تمیز و منطبق با ساختار جدید فایل CSV ایجاد شود.
4. بارگذاری داده‌ها با BULK INSERT:
   • پس از ایجاد جدول، دستور BULK INSERT به صورت پویا ساخته می‌شود. این دستور مسئول بارگذاری سریع و کارآمد تمام داده‌ها از فایل CSV به جدول تازه ایجاد شده است.
   • گزینه FIELDTERMINATOR = ',' جداکننده فیلدها را مشخص می‌کند.
   • ROWTERMINATOR = CHAR(10) پایان‌دهنده ردیف را به کاراکتر خط جدید (استاندارد لینوکس/یونیکس) تنظیم می‌کند. ممکن است برای فایل‌های ویندوزی نیاز به CHAR(13) + CHAR(10) باشد.
   • اگر @firstRowHasHeaders برابر با 1 باشد، FIRSTROW = 2 تنظیم می‌شود تا ردیف سربرگ نادیده گرفته شود و بارگذاری از ردیف دوم شروع شود.
5. گزارش‌دهی موفقیت:
   • در صورت موفقیت‌آمیز بودن عملیات، یک پیام تأیید چاپ می‌شود.
6. مدیریت خطا:
   • در بلوک CATCH، جزئیات خطا (پیام، شدت و وضعیت) استخراج شده و با استفاده از RAISERROR مجدداً مطرح می‌شوند تا مشکل به کاربر گزارش شود.

برای فراخوانی و استفاده از این رویه ذخیره شده، کافی است پارامترهای لازم را به آن ارسال کنید. در اینجا مثالی از نحوه استفاده آورده شده است:

EXEC [dbo].[usp_CreateAndLoadTableFromCSV]
    @fullFilePath = 'C:\Temp\MyData.csv', -- مسیر فایل CSV شما
    @tableName = 'MyNewTable',             -- نام جدول مورد نظر
    @firstRowHasHeaders = 1;               -- اگر فایل CSV دارای سربرگ است، 1 باشد در غیر این صورت 0

با اجرای این دستور، رویه ذخیره شده به‌طور خودکار جدول MyNewTable را در پایگاه داده شما ایجاد کرده و داده‌ها را از فایل MyData.csv به آن بارگذاری می‌کند. این روش راهکاری قدرتمند برای اتوماسیون فرآیندهای ETL (Extract, Transform, Load) ساده در محیط SQL Server است.