SQL Server Stored Procedure for multiple excel file load [closed]

Solution for SQL Server Stored Procedure for multiple excel file load [closed]
is Given Below:

Please help me is their procedure available to use for multiple Excel files load into SQL Server table.

I found one on google, but not working as expected:

Msg 111, Level 15, State 1, Procedure usp_ImportMultipleFiles, Line 11 [Batch Start Line 40]
‘CREATE/ALTER PROCEDURE’ must be the first statement in a query batch.

Msg 137, Level 15, State 2, Line 67
Must declare the scalar variable “@filepath”.

1a. Create a table for getting file names

IF EXISTS (SELECT *
           FROM INFORMATION_SCHEMA.TABLES
           WHERE TABLE_NAME = '[FileNames]')
    DROP TABLE [FileNames];
 
CREATE TABLE [dbo].[FileNames]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](200) NULL
) ON [PRIMARY]
 
IF EXISTS (SELECT *
           FROM INFORMATION_SCHEMA.TABLES
           WHERE TABLE_NAME = '[MultipleXLtoSQL_stage]')
    DROP TABLE MultipleXLtoSQL_stage;
 
CREATE TABLE [dbo].[MultipleXLtoSQL_stage]
(
    [Sno] [float] NULL,
    [EmpID] [float] NULL,
    [EmpName] [nvarchar](255) NULL,
    [Checkin] [datetime] NULL,
    [Checkout] [datetime] NULL,
    [Working hours] [float] NULL,
    [Status] [nvarchar](255) NULL
) ON [PRIMARY]

IF EXISTS (SELECT *
           FROM INFORMATION_SCHEMA.TABLES
           WHERE TABLE_NAME = '[MultipleXLtoSQL]')
    DROP TABLE MultipleXLtoSQL;

CREATE TABLE [dbo].[MultipleXLtoSQL]
(
    [Sno] [float] NULL,
    [EmpID] [float] NULL,
    [EmpName] [nvarchar](255) NULL,
    [Checkin] [datetime] NULL,
    [Checkout] [datetime] NULL,
    [Working hours] [float] NULL,
    [Status] [nvarchar](255) NULL,
    [File_name] [varchar](50) NULL,
    [date] [date] NULL
) ON [PRIMARY]

/* —————————————————————–
2a. Create a stored procedure for getting the file count
—————————————————————–*/

IF EXISTS (SELECT *
           FROM sys.objects
           WHERE object_id = OBJECT_ID(N'[dbo].[usp_ImportMultipleFiles]’) 
             AND type IN (N’P’, N’PC’))
    DROP PROCEDURE [dbo].[usp_ImportMultipleFiles]

CREATE PROCEDURE [dbo].[usp_ImportMultipleFiles] 
    @filepath varchar(500),
    @pattern varchar(100), 
    @TableName varchar(128) = NULL
AS
    SET QUOTED_IDENTIFIER OFF

    DECLARE @query varchar(1000)
    DECLARE @max1 int
    DECLARE @count1 int
    DECLARE @filename varchar(100)

    SET @count1 = 0

    DROP TABLE [FileNames]

    CREATE TABLE #x (name varchar(200))

    SET @query = ‘master.dbo.xp_cmdshell “dir ‘ + @filepath + @pattern + ‘ /b”‘

    INSERT #x 
        EXEC (@query)

    DELETE FROM #x
    WHERE name IS NULL

    SELECT
        IDENTITY(int, 1, 1) AS ID,
        name INTO [FileNames]
    FROM #x

    DROP TABLE #x

    /*—————————————————————————-
    2b. Create a stored procedure for inserting the excel files one by one
    —————————————————————————-*/

    IF EXISTS (SELECT *
               FROM sys.objects
               WHERE object_id = OBJECT_ID(N'[dbo].[Article_InsertMultiplexlFile]') 
                 AND type IN (N'P', N'PC'))
        DROP PROCEDURE [Article_InsertMultiplexlFile];
 
    CREATE PROCEDURE [dbo].[Article_InsertMultiplexlFile] 
        (@filepath varchar(max),
         @table_name varchar(50) = NULL)
    AS
    BEGIN
        DECLARE @v_filepath varchar(max),
                @v_delete varchar(500),
                @v_closebracket varchar(10),
                @max1 int, @count1 int,
                @filename varchar(100),
                @v_message varchar(50), 
                @v_Date date,
                @v_filename varchar(48),
                @v_sheetname varchar(500);

    SET @count1 = 0;
    SET @v_closebracket=")";
    SET @v_sheetname="Sheet1"
 
    EXEC usp_ImportMultipleFiles @filepath, '*.x*'

    SET @max1 = (SELECT MAX(ID)
                 FROM [FileNames])

    --print @max1
    --print @count1
    WHILE @count1 <= @max1
    BEGIN
        SET @count1 = @count1 + 1
        SET @filename = NULL
        SET @filename = (SELECT name
                         FROM [FileNames]
                         WHERE [id] = @count1)

        IF @filename IS NOT NULL
        BEGIN
             BEGIN TRY
                 SET @v_filepath="INSERT INTO " + @table_name + '
SELECT * FROM OPENROWSET(' + '''' + 'Microsoft.ACE.OLEDB.12.0' + '''' + ',' + '''' +
'Excel 12.0;Database=" + @filepath + @filename + "''' + ',' + '''' + 'SELECT * FROM [' + @v_sheetname + '$]''' + @v_closebracket
EXEC (@v_filepath)
End Try
BEGIN CATCH
SELECT
'ERROR WITH Filename @filename=" + @filename + " ' + ERROR_MESSAGE() AS Error_Description
END CATCH
End --End if
 
SET @v_date = CAST(SUBSTRING(@filename, 1, 10) AS date)
 
INSERT INTO MultipleXLtoSQL ([Date], [Sno], [EmpID], [EmpName], [Checkin], [Checkout], [Working hours], [Status], [File_name])
SELECT
@v_date,
[Sno],
[EmpID],
[EmpName],
[Checkin],
[Checkout],
[Working hours],
[Status],
@filename
FROM MultipleXLtoSQL_stage
 
Truncate table MultipleXLtoSQL_stage
End
--While
End
/*—————————————————————————-

Execute the Stored Procedure (Give the folder path)
—————————————————————————-*/

EXEC [dbo].[Article_InsertMultiplexlFile] 'D:MultipleExcel2SQLArticleInputFiles',
'MultipleXLtoSQL_stage'
/*—————————————————————————-

4a. To see how many records were imported and from which file (Query 1)

—————————————————————————-*/

SELECT
FILE_NAME,
COUNT(*) No_of_Records
FROM MultipleXLtoSQL
GROUP BY FILE_NAME;
/*—————————————————————————-

4b. To see all the records from table MultipleXLtoSQL (Query 2)

—————————————————————————-*/

SELECT
[Date],
[Sno],
[EmpID],
[EmpName],
[Working hours],
[Status],
[File_name]
FROM MultipleXLtoSQL;
/*—————————————————————————-

4c. To see total number of present and absent days (Query 3)

—————————————————————————-*/

SELECT
Empname,
COUNT(status) PRESENT_DAYS,
0 ABSENT_DAYS
FROM MultipleXLtoSQL
WHERE status="Present"
GROUP BY Empname UNION SELECT
Empname,
0,
COUNT(status) ABSENT_DAYS
FROM MultipleXLtoSQL
WHERE status="Absent"
GROUP BY Empname
/*—————————————————————————-

4d. To see the details of a selected employee (Query 4)

—————————————————————————-*/

SELECT
[Date],
[Sno],
[EmpID],
[EmpName],
[Working hours],
[Status],
[File_name]
FROM MultipleXLtoSQL
WHERE Empname="A"
/*—————————————————————————-

–To access the files in folders

—————————————————————————-*/

/*
SET ANSI_PADDING OFF
GO
EXEC sp_configure 'show advanced options',
1
reconfigure with override
GO
EXEC sp_configure 'xp_cmdshell',
1
reconfigure with override
GO
*/

I see two errors here:

‘CREATE/ALTER PROCEDURE’ must be the first statement in a query batch.

Must declare the scalar variable “@filepath”.

Get a very simple script to work, and then build on that. Can you try it like this?

insert into test.dbo.Categories
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;
Database=C:UsersExcelDesktopBook1.xls;
HDR=yes','Select * from [Sheet1$]')