How to Import Excel File in SQL Server Using Stored Procedure?

You can import Excel files into SQL Server using a stored procedure by leveraging the BULK INSERT statement. This statement allows you to load data from a flat file, including Excel files, into a SQL Server table. Here's a step-by-step guide:

1. Configure SQL Server for Bulk Import

Enable the "xp_cmdshell" extended stored procedure: This procedure allows you to execute operating system commands from within SQL Server. You can enable it using the following T-SQL command:

EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

Ensure the "SQL Server Agent" service is running: This service is responsible for executing scheduled jobs, including bulk imports.

2. Prepare the Excel File

Save the Excel file as a CSV file: This format is typically recognized by the BULK INSERT statement.
Ensure the CSV file has a header row: This row will be used to map the columns in the file to the columns in your SQL Server table.

3. Create the Stored Procedure

CREATE PROCEDURE ImportExcelData
    @FilePath VARCHAR(255)
AS
BEGIN
    -- Check if the file exists
    IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'your_table_name')
    BEGIN
        -- Delete existing data from the table
        TRUNCATE TABLE your_table_name;
    END
    ELSE
    BEGIN
        -- Create the table if it doesn't exist
        CREATE TABLE your_table_name (
            -- Define table columns and data types
        );
    END

    -- Import data from the CSV file
    BULK INSERT your_table_name
    FROM @FilePath
    WITH (
        FIELDTERMINATOR = ',',
        ROWTERMINATOR = '\n',
        FIRSTROW = 2
    );
END;

Explanation:

@FilePath: This parameter holds the path to your CSV file.
BULK INSERT: This statement imports data from the file.
your_table_name: Replace this with the actual name of your SQL Server table.
FIELDTERMINATOR: Specifies the character that separates columns in the file (usually a comma).
ROWTERMINATOR: Specifies the character that separates rows in the file (usually a newline character).
FIRSTROW: Indicates the row number where data starts (2 in this case, as the first row is assumed to be the header row).

4. Execute the Stored Procedure

EXEC ImportExcelData 'C:\your_folder\your_file.csv';

Replace 'C:\your_folder\your_file.csv' with the actual path to your CSV file.

5. Verify the Import

Check your SQL Server table to see if the data has been imported successfully.

6. Security Considerations

Ensure that the user account executing the stored procedure has the necessary permissions to access the file and the SQL Server database.
Use appropriate security measures to protect your sensitive data.

Practical Insights:

You can use the DATA_SOURCE option in the BULK INSERT statement to specify a different data source, such as a network share.
For larger files, consider using the TABLOCK option to improve performance.
You can use the FORMAT option to specify the file format if it is not a standard CSV file.
The ERRORFILE option allows you to redirect any errors encountered during the import to a separate file.

A2oz