Hi everyone in this post i will show you -
How to read the text data present inside all the text files in a directory and sub directory and store into a table using SQL Server.
If we need to read text data from a particular text file, then use the bulk insertion as below -
bulk
insert [myfiles] from
'C:\Log Files\24-Jan-2017.log'
The above query will read a particular file and display in the Result window
We will use the technique of executing the "dir" command in the Stored procedure xp_cmdshell to get list of all files in a directory and sub directory.
The same has been explained in my prev post
SQL Query - To Read all the Text data from all the files in a directory and store it into a table
|
--CREATE THE PROCEDURE
ALTER PROCEDURE dbo.sp_GetAllTextFromFiles
@FileDirectory VARCHAR(80)
AS
BEGIN
--CREATE TEMP
TABLE AND OTHER VARIABLES
CREATE TABLE #myfiles
(ID BIGINT
IDENTITY(1,1) PRIMARY KEY,
DECLARE
@CommandLine VARCHAR(4000)
DECLARE
@DisplayOption VARCHAR(80) =
'/A-D /B /S'
DECLARE
@SortOrder VARCHAR(80) = '/O-D'
DECLARE
@varCount BIGINT
DECLARE
@varFilePathAndName NVARCHAR(80)
DECLARE
@varTempQuery NVARCHAR(MAX)
--DOS COMMAND
TRYING TO EXECUTE - dir "C:\Log Files" /A-D /B /S /O-D
SELECT
@CommandLine ='dir
"' + @FileDirectory+'" ' + @DisplayOption + ' ' +
@SortOrder ;
--GET ALL
FILES INTO TEMP TABLE #myfiles -
--SIMILARLY
CAN STORE IN PERMANENT TABLE AS WELL
INSERT INTO #myfiles (FullPath)
EXECUTE xp_cmdshell @CommandLine
--LAST RESULT
WILL BE NULL HENCE DELETE THE NULL RESULT
DELETE FROM #myfiles WHERE
fullpath IS NULL
OR fullpath='File Not Found'
--READ TEXT
FROM ALL FILES
SELECT
@varCount = MAX(ID) FROM #myfiles
--TEMP TABLE
#MYFILESTEXT WILL BE USED TO STORE TEXT FROM FILES
CREATE TABLE #myfilesText
(FullTextInfo VARCHAR(4000))
--LOOP TILL
ALL FILES ARE READ
WHILE
@varCount > 0
BEGIN
SELECT
@varFilePathAndName = [FullPath] FROM #myfiles WHERE
ID = @varCount;
--USE
BULK INSERT TO INSERT ALL TEXT FROM THE SINGLE FILE
SET
@varTempQuery = 'BULK
INSERT [#myfilesText] FROM '''+@varFilePathAndName+''''
EXEC
sp_executesql @varTempQuery
--DECREMENT
TO GO TO NEXT FILE
SET
@varCount = @varCount -1
END
--DO SELECT
OPERATION TO DISPLAY ALL THE TEXT FROM THE TEMPORARY TABLE
--IF WE WANT TO PERSIST THE DATA THEN STORE INTO A PHYSICAL TABLE
SELECT * From #myfilesText
END
|
EXECUTING
EXEC dbo.sp_GetAllTextFromFiles 'C:\Log Files\'
|
Output Analysis
– As you can the Text from all files are stored into the Temp table #myfilesText
 |
No comments:
Post a Comment