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
Leave a Reply to MikeCancel reply