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$]')