请帮我看看他们的程序是否可用于将多个 Excel 文件加载到 SQL Server 表中。
我在谷歌上找到了一个,但没有按预期工作:
消息 111,级别 15,状态 1,过程 usp_ImportMultipleFiles,第 11 行 [批处理开始第 40 行]
“CREATE/ALTER PROCEDURE”必须是查询批处理中的第一条语句。
Msg 137,Level 15,State 2,Line 67
必须声明标量变量“@filepath”。
1a。创建获取文件名的表
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,[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,10) AS date)
INSERT INTO MultipleXLtoSQL ([Date],[sno],[EmpID],[EmpName],[Checkin],[Checkout],[Working hours],[Status],[File_name])
SELECT
@v_date,@filename
FROM MultipleXLtoSQL_stage
Truncate table MultipleXLtoSQL_stage
End
--While
End
/*—————————————————————————-
Execute the Stored Procedure (Give the folder path)
—————————————————————————-*/
EXEC [dbo].[Article_InsertMultiplexlFile] 'D:\MultipleExcel2SQL\ArticleInputFiles\','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],[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,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],[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
*/