SQL Server – Read all files in directory and store in Table

Recently I was in need to analyze Salesforce debug log for one stubborn issue which was very hard to reproduce. Was able to download 1500+ debug logs on my system, however to analyze it, I decided to take help of SQL Server.

Very soon I came into challenge to read all files in a directory and store it back in SQL Server table.

Found very good article here which helped me to come up with below queries. If you need to read more in detail about below T-SQL and stored procedures please navigate to original article.

Table where we need to load files

CREATE TABLE [dbo].[DebugLogs](
	[Id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
	[LogText] [nvarchar](max) NULL,
	[FileName] [nchar](500) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Stored procedure which reads all files in a directory

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ListPathsXML]
@FileSpec VARCHAR(2000),
@order VARCHAR (80) = '/O-D',--sort by date time oldest first
@xmlFileList XML OUTPUT
 
AS
DECLARE @myfiles TABLE (MyID INT IDENTITY(1,1) PRIMARY KEY, FullPath VARCHAR(2000))
DECLARE @CommandLine VARCHAR(4000)
IF @order IS NOT NULL -- abort if the order is silly
   BEGIN
   SELECT @CommandLine =LEFT('dir "' + @FileSpec + '" /A-D /B /S '+@order,4000)
   INSERT INTO @MyFiles (FullPath)
       EXECUTE xp_cmdshell @CommandLine
   DELETE FROM @MyFiles WHERE fullpath IS NULL
       OR fullpath = 'File Not Found'
   END
SET @xmlFileList = (SELECT fullpath FROM @MyFiles
                         FOR
                          XML PATH('thefile'),
                              ROOT('thefiles'),
                              TYPE)

Stored procedure which reads content of file one at a time

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spLoadTextFromAFile]
   @Filename VARCHAR(255),
   @Unicode INT = 0
AS 
   SET NOCOUNT ON
   DECLARE @MySpecialTempTable VARCHAR(255)
   DECLARE @Command NVARCHAR(4000)
   DECLARE @RESULT INT

--firstly we create a global temp table with a unique name
   SELECT   @MySpecialTempTable = '##temp' 
       + CONVERT(VARCHAR(12), CONVERT(INT, RAND() * 1000000))
--then we create it using dynamic SQL, 
   SELECT   @Command = 'create table [' 
       + @MySpecialTempTable + '] (MyLine '
       + CASE WHEN @unicode <>0 THEN 'N' ELSE '' END +'varchar(MAX))
 '
   EXECUTE sp_ExecuteSQL @command

   SELECT   @command = 'bulk insert [' 
       + @MySpecialTempTable + '] from' + ' ''' 
       + REPLACE(@Filename, '"', '') + '''' 
       + ' with (FIELDTERMINATOR=''|~||''' + ',ROWTERMINATOR = ''
'''        + CASE WHEN @unicode <>0 THEN ', DATAFILETYPE=''widechar'''ELSE '' END
       + ')'
-- import the data
   EXEC (@Command)
   EXECUTE ('Select * from ' + @MySpecialTempTable)
   EXECUTE ('Drop table ' + @MySpecialTempTable)

Final T-SQL query to read content of every file exists in directory and store in Table in SQL Server

DECLARE
       @ii INT,
       @iiMax INT,
       @File VARCHAR(2000)
DECLARE @files TABLE (MyID INT IDENTITY(1,1) PRIMARY KEY, [Path] VARCHAR(2000))
DECLARE @lines TABLE (MyID INT IDENTITY(1,1) PRIMARY KEY, [line] NVARCHAR(MAX), fName nVARCHAR(200))
 
DECLARE @FileList XML
EXECUTE ListPathsXML 'C:\ForceUtil\18_Jan_2017\DebugLogs\',
    DEFAULT , @XMLFileList = @FileList OUTPUT
 
INSERT INTO @files(path)
   SELECT   x.thefile.value('fullpath[1]', 'varchar(2000)') AS [path]
        FROM    @FileList.nodes('//thefiles/thefile') AS x ( thefile )
DELETE FROM @files WHERE REVERSE(path) LIKE 'golrorre%'
--don't look at the current errorlog!
SELECT @ii=1, @iiMax=MAX(MyID) FROM @Files
WHILE @ii<=@iiMax
   BEGIN
   SELECT @File= [path] FROM @files WHERE MyID=@ii
   INSERT INTO @lines(line)
       EXECUTE spLoadTextFromAFile @file, @Unicode=1

   INSERT INTO [dbo].[DebugLogs] (LogText, [FileName])
	SELECT  Line, @file FROM @lines
   
   --Empty temp table
   Delete FROM @lines

   SELECT @ii=@ii+1
   END