Parsing a text log file

• CraCorB

  SSC Journeyman

  Points: 94

  More actions

  August 15, 2008 at 6:22 pm

  #382288

  [font="Tahoma"]The task that I need to accomplish is to parse a text log file and get the directory path that a set of files were archived to. The line that the information is on looks like this:[/font]

  [font="Courier New"][8/15/2008 10:44:14 AM] [MESSAGE] Batch Archived:[869] nameOfBatch;Archived Path:\\servername\directory\subdirectory[/font]

  [font="Tahoma"]I set up the following query to try to accomplish this:[/font]

  [font="Courier New"]CREATE TABLE #tmpTbl (textfile VARCHAR(MAX))

  INSERT INTO #tmpTbl

  SELECT * FROM OPENROWSET(BULK '\\servername\directory\subdirectory\filename.txt', SINGLE_BLOB) AS x

  DECLARE @tmpString VARCHAR(MAX)

  ,@tmpPath VARCHAR(100)

  SELECT@tmpString = textfile

  FROM#tmpTbl

  WHILE PATINDEX('%Batch Archived:%', @tmpString)>0

  BEGIN

  SET @tmpString = SUBSTRING(@tmpString, PATINDEX('%;Archived Path:%', @tmpString), LEN(@tmpString)-PATINDEX('%;Archived Path:%', @tmpString))

  SET @tmpPath = SUBSTRING(@tmpString, CHARINDEX(':', @tmpString, 1)+1, CHARINDEX('[', @tmpString, 1)-CHARINDEX(':', @tmpString, 1)-3)+'\'

  INSERT INTO tableName(archivePath

  ,insertDate)

  SELECT@tmpPath, GETDATE()

  SET @tmpString = SUBSTRING(@tmpString, CHARINDEX(':', @tmpString, 1)+1, LEN(@tmpString)-CHARINDEX(':', @tmpString))

  END[/font]

  [font="Tahoma"]This Works great with only a few (1-3) records. The problem is, after about 5 or 6 records are added to the log, it goes through the loop okay until it gets to the last record. Then it gives an error just before last record. Which error I get depends on how many records I have added. I'm currently getting the following error with 5 records added to the log file:[/font]

  [font="Courier New"]Msg 536, Level 16, State 5, Line 46

  Invalid length parameter passed to the SUBSTRING function.[/font]

  [font="Tahoma"]Even after the error, it still pulls correct information for last record. After about 10 records are added to the log file, it doesn't give an error any more but it stops getting the last record at all.

  To troubleshoot I added a[/font] [font="Courier New"]"SELECT @tmpString"[/font] [font="Tahoma"]just before the[/font] [font="Courier New"]"SET @tmpPath"[/font] [font="Tahoma"]line so I could see why I was getting an error.

  Currently, with just the 5 records, this line gives the following result just before the last record:[/font]

  [font="Courier New"];Arc[/font]

  [font="Tahoma"]But yet, after this it still gives the full path in the last record which isn't shown in this field.

  My SWAG at this is, this is the point that the data reaches the end of a page and the rest of the field is stored on another page. It also seems that the SUBSTRING command is only looking at the current page.

  So my questions are:

  Is this what it is actually doing, or am I totally off-base?

  If my assumptions are somewhat correct, is there a way to get the query to look at data beyond the current page it is on so it will get the data for the last record?

  Or, is there an even better way to accomplish this task that will avoid this problem?[/font]

• Jeff Moden

  SSC Guru

  Points: 1004592

  More actions

  August 15, 2008 at 8:46 pm

  #859076

  What's wrong with just...

  --===== Create a test variable (not part of the solution)

  DECLARE @Test VARCHAR(8000)

  SELECT @Test = '[8/15/2008 10:44:14 AM] [MESSAGE] Batch Archived:[869] nameOfBatch;Archived Path:\\servername\directory\subdirectory'

  --===== Return the path

  SELECT SUBSTRING(@Test,CHARINDEX('\\',@Test),8000)

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)