Today Morning I went to Office as usual, But unusually I got a call from my friend and asked me for a help on SQL. He has 2000 excel files in one folder and he want to load all these files into Database and he doesn't have schema for the tables for upfront. So he asked me to create schema for each table automatically/accordingly while loading data into each table.
This below Stored proc will solve that problem.
This below Stored proc will solve that problem.
CREATE PROCEDURE [dbo].[IMPORTING_FILES_FROM_FOLDER]
(
@FOLDER_NAME VARCHAR(1024),----FOLDER PATH WHERE your files resides
@NEW_FOLDER VARCHAR(1024)=NULL,---Folder path, this is optional.Once files loaded it will move to this folder
@FILE_PATTERN VARCHAR(50)='',----what type of files you want to load
@CREATE_TABLE_AND_LOAD BIT=0,----create table and load the data
@TRUNCATE BIT=0,-----Before load truncate the table
@MOVE_TO_NEWFOLDER BIT=0----Move to new folder or not
)
/*
USAGE:::
EXEC IMPORTING_FILES_FROM_FOLDER
@FOLDER_NAME=D:\Folder0,
@NEW_FOLDER='D:\Folder0\folder01',
@FILE_PATTERN='*.xls'
@CREATE_TABLE_AND_LOAD=1,
@MOVE_TO_NEWFOLDER=1
*/
AS
BEGIN
BEGIN TRY
SET NOCOUNT ON
DECLARE @DATE VARCHAR(20),@NLN VARCHAR(4), @NRROWS INT
SELECT @DATE = CAST(GETDATE() AS VARCHAR(20)),@NLN=''+CHAR(10)+' '
DECLARE @ERRMSG NVARCHAR(4000), @ERRSEV INT, @ERRST INT, @ERRPROC VARCHAR(126),@ERRLN INT, @ERRNR INT, @ERRLOC NVARCHAR(300)
DECLARE @COMMAND VARCHAR(200),@FILE_NAME VARCHAR(100),@FULL_PATH VARCHAR(1054),@SQL VARCHAR(4000),@TABLE_NAME VARCHAR(256)
IF @FOLDER_NAME IS NULL or @FILE_PATTERN IS NULL
RAISERROR('Input parameters foldername or filepattern cannot be null',16,-1) with nowait
IF RIGHT(@FOLDER_NAME, 1)<>'\'
SET @FOLDER_NAME =@FOLDER_NAME +'\'
IF OBJECT_ID(N'TEMPDB..#FILELIST') IS NOT NULL
DROP TABLE #FILELIST
CREATE TABLE #FILELIST([FILENAME] NVARCHAR(500))
SELECT @COMMAND= 'DIR "'+ @FOLDER_NAME + @FILE_PATTERN +'" /B /A:-D /B /O:N'
INSERT INTO #FILELIST EXEC XP_CMDSHELL @COMMAND
DELETE FROM #FILELIST WHERE [FILENAME] IS NULL OR [FILENAME] = 'FILE NOT FOUND'
IF (CURSOR_STATUS('GLOBAL', 'C_LOAD') >= 0 OR CURSOR_STATUS('LOCAL', 'C_LOAD') >= 0)
BEGIN CLOSE C_LOAD; DEALLOCATE C_LOAD END
DECLARE C_LOAD CURSOR FOR SELECT [FILENAME] FROM #FILELIST
OPEN C_LOAD
FETCH NEXT FROM C_LOAD INTO @FILE_NAME
WHILE @@FETCH_STATUS=0
BEGIN
RAISERROR('PROCESSING FILE %S',0,1,@FILE_NAME) WITH NOWAIT
SET @FULL_PATH=@FOLDER_NAME+@FILE_NAME
SELECT @TABLE_NAME=SUBSTRING(@FILE_NAME,1,CHARINDEX('.',@FILE_NAME)-1)
IF @CREATE_TABLE_AND_LOAD=1
BEGIN
IF CHARINDEX('.xls',@FILE_NAME)>0
BEGIN
SELECT @SQL ='
SELECT * INTO '+@TABLE_NAME+' FROM OPENDATASOURCE
(''Microsoft.jet.OLEDB.4.0'',''data source='+@FULL_PATH+';Extended properties=EXCEl 5.0'')...[Sheet1$]'
END
IF CHARINDEX('.csv',@FILE_NAME)>0 or
CHARINDEX('.txt',@FILE_NAME)>0
BEGIN
SELECT @SQL ='
SELECT * INTO '+@TABLE_NAME+' FROM OPENDATASOURCE
(''Microsoft.jet.OLEDB.4.0'',''data source='+@FOLDER_NAME+';Extended properties=Text;'')...['+Replace(@FILE_NAME,'.','#')+']'
END
PRINT @SQL
EXEC(@SQL)
END ELSE BEGIN
IF @TRUNCATE=1
BEGIN
SELECT @SQL='TRUNCATE TABLE '+@TABLE_NAME
PRINT @SQL
EXEC(@SQL)
END
SELECT @SQL = '
BULK INSERT TGI_DBSNP.DBO.'+@TABLE_NAME+' FROM '''+@FULL_PATH+'''
WITH (CODEPAGE=''OEM'',
BATCHSIZE=100000,
FIELDTERMINATOR =''\T'',
FIRSTROW=2,
MAXERRORS=0,
ROWTERMINATOR='''+CHAR(10)+''')
'
PRINT @SQL
EXEC(@SQL)
END
IF @MOVE_TO_NEWFOLDER=1
BEGIN
IF @NEW_FOLDER IS NULL
BEGIN
RAISERROR('NewFolder Parameter cannot null for moving to new folder',16,-1) WITH NOWAIT
END
IF RIGHT(@NEW_FOLDER, 1)<>'\'
SET @NEW_FOLDER =@NEW_FOLDER +'\'
SELECT @COMMAND= 'MOVE '+ @FOLDER_NAME+@FILE_NAME +' '+ @NEW_FOLDER+@FILE_NAME+''
PRINT 'EXEC XP_CMDSHELL ''' +@COMMAND+'''
EXEC XP_CMDSHELL @COMMAND
END
FETCH NEXT FROM C_LOAD INTO @FILE_NAME
END
CLOSE C_LOAD
DEALLOCATE C_LOAD
END TRY
BEGIN CATCH
SELECT @ERRMSG =ERROR_MESSAGE(), @ERRSEV =ERROR_SEVERITY(), @ERRST =ERROR_STATE(), @ERRLN = ERROR_LINE(), @ERRNR = ERROR_NUMBER(),@FILE_NAME=ISNULL(@FILE_NAME,'')
RAISERROR('FAILED TO LOAD FILE %s :::ERROR NR %d, STATE %d, SEV %d; LINE %d %s WITH MSG: %s',16,-1, @FILE_NAME, @ERRNR,@ERRST,@ERRSEV,@ERRLN,@NLN,@ERRMSG)
END CATCH
END
