Question Importing Text File Into Temp Table

jamie_moore

New Member
Hi,

I'm attempting to import a text file into a temp table so I can perform some calculations on data held within it. I'm struggling as I've only managed to upload a file in the past where only 1 delimiter was needed and the columns were laid out nicely.

Basically I have a temp table at the bottom of the code whereby I need to import this kind of text into the fields. I have tried to capture the data but I've not been successful thus far. I'm only bothered about the lines I've put in Bold and Underlined and I basically need to capture the Date Time and the number after "Lines" I'll then take these and use the interval function to calculate the time it took to process the amount of lines stated.

Any help would be greatly appreciated as I'm really struggling on this one.

Extract of Text File:
09/05/17 04:15:18 -- Processing Order
09/05/17 04:15:18 -- Account: 66730 WELL Lines: 164
09/05/17 04:15:24 -- Before ProcessCentOrd

09/05/17 04:15:31 -- File already processed: 20170509041524-49-1162519-ORD.xml
09/05/17 04:15:33 -- After ProcessCentOrd
09/05/17 04:15:33 -- Order 1162519 created
09/05/17 04:15:33 -- Data processed
09/05/17 04:15:33 -- Creating Reply File
09/05/17 04:15:33 -- Order Processing Complete
09/05/17 04:15:33 -- Archiving File: 66730-13428-011491.xml
09/05/17 04:15:33 -- File 66730-13428-011491.xml archived
09/05/17 07:54:44 -- Processing Order
09/05/17 07:54:44 -- Account: 09318 WELL Lines: 28
09/05/17 07:54:45 -- Before ProcessCentOrd

09/05/17 07:54:50 -- After ProcessCentOrd
09/05/17 07:54:50 -- Order 1162772 created
09/05/17 07:54:50 -- Data processed
09/05/17 07:54:50 -- Creating Reply File
09/05/17 07:54:50 -- Order Processing Complete
09/05/17 07:54:50 -- Archiving File: 09318-12269-034877.xml

Code:
/************************* Variables *************************/

DEFINE VARIABLE orderLine AS CHAR LABEL "Order Line" FORMAT "x(5)" NO-UNDO.
DEFINE VARIABLE logDate AS CHAR LABEL "Log Date" FORMAT "x(6)" NO-UNDO.
DEFINE VARIABLE logFile AS CHAR FORMAT "x(100)" NO-UNDO.
DEFINE VARIABLE sysSelect AS CHAR LABEL "Select System" FORMAT "x(7)" NO-UNDO.
DEFINE VARIABLE jobNo AS INT FORMAT 99999 NO-UNDO.
DEFINE VARIABLE jobNoLength AS INT FORMAT 9 NO-UNDO.
DEFINE VARIABLE calcJobNo AS CHAR FORMAT "x(5)" NO-UNDO.
DEFINE VARIABLE sysPath AS CHAR FORMAT "x(76)" NO-UNDO.
DEFINE VARIABLE text-string AS CHARACTER FORMAT "x(76)" NO-UNDO.

/************************* User Menu + Log File Search *************************/

UPDATE  orderLine HELP "Enter in Order Line (Must = Job Ref in BM Case-Sensitive)"
        logDate HELP "Enter Date DD/MM/YYYY"
        sysSelect HELP "Type Live or Upgrade"
        WITH NO-VALIDATE 1 COLUMNS FRAME ORDER-LINE-SPEED TITLE "ORDER-LINE-SPEED-ANALYSIS"
        CENTERED ROW FRAME-ROW(ORDER-LINE-SPEED-ANALYSIS) + 6.

FOR EACH batchjob NO-LOCK WHERE ref = orderLine.
  ASSIGN jobNo = job-no.
  ASSIGN jobNoLength = LENGTH(STRING(jobNo), "CHARACTER").   
END.

IF sysSelect = "Live" THEN sysPath = "/apps/live/awards/tmp/".
IF sysSelect = "Upgrade" THEN sysPath = "/upgrade/awards/tmp/".

IF jobNoLength = 1 THEN
  logFile = sysPath + "logbj0000" + STRING(jobNo) + "." + STRING(logDate).
IF jobNoLength = 2 THEN
  logFile = sysPath + "logbj000" + STRING(jobNo) + "." + STRING(logDate).
IF jobNoLength = 3 THEN
  logFile = sysPath + "logbj00" + STRING(jobNo) + "." + STRING(logDate).
IF jobNoLength = 4 THEN
  logFile = sysPath + "logbj0" + STRING(jobNo) + "." + STRING(logDate).

MESSAGE "Job No. = " jobNo SKIP(1)
        "Log File = " logFile VIEW-AS ALERT-BOX.

/************************* Text File Calculator *************************/

DEFINE TEMP-TABLE ttOrderSpeed
  FIELD tDateTime AS DATETIME
  FIELD tText AS CHAR
  FIELD tLines AS INT FORMAT 999999.
 
 
INPUT FROM VALUE(logFile).

REPEAT:
CREATE ttOrderSpeed.
IMPORT DELIMITER "--":U tDateTime.
END.

FOR EACH ttOrderSpeed:
DISPLAY ttOrderSpeed.
END.
 

Cringer

ProgressTalk.com Moderator
Staff member
If the file remains so small then use copy-lob to put it in a longchar. Then you can use the line feed character, chr(13) probably, but depends on OS, to loop through each entry. So, DO i = 1 to num-entries(mylongchar,chr(13). Then you can do a simple pattern match on each line.
 

Cringer

ProgressTalk.com Moderator
Staff member
It depends more on the size of the file in terms of bytes, and how much memory you have available. 5000 lines shouldn't be too much of a problem, but test it first.
 

jamie_moore

New Member
Thanks I've got this far which is the first bit you mentioned but I'm not sure where to go next as I'm not familiar with pattern matching and longchar's.

I want to do something like if line contains "Lines:" grab the value immediately preceding that. It also need to grab the Date & Time from the start of the line into a DATETIME variable to I can peform an interval function on it? Any ideas?

Code:
DEFINE VARIABLE textFile AS LONGCHAR NO-UNDO.

COPY-LOB FROM FILE (logFile) TO textFile.
DISPLAY
  textFile VIEW-AS EDITOR LARGE INNER-LINES 150 INNER-CHARS 150
  WITH
  FRAME x1
  WIDTH 160
 

Cringer

ProgressTalk.com Moderator
Staff member
You can treat the textFile as a big character field in terms of looping through it.

Code:
do i = 1 to num-entries(textFile,chr(13)):
  myline = entry(i,textFile,chr(13)).
  if myline matches "*Lines:*" then 
    do something.
end.
 

Stefan

Well-Known Member
For reading the lines, just:

Code:
define variable cline as character no-undo.

repeat:
   import unfomatted cline.
end.

Be very careful with:
Code:
do i = 1 to num-entries(textFile,chr(13)):

This will count the number of lines for every iteration. Since this is a longchar this can unnecessarily be very slow.
 

Cringer

ProgressTalk.com Moderator
Staff member
Yes good point Stefan. It was merely pseudo code to help, but worth pointing out. Store num-entries in a variable and iterate on that.
 

TomBascom

Curmudgeon
The COPY-LOB technique is sometimes useful but, IMHO, this is not a good use-case.

In this case I would do something like:
Code:
define variable lineInDateString as character no-undo.
define variable lineInBody as character no-undo.

input from value( "filename.txt" ).
repeat:
  import unformatted lineIn.
  lineInDateString = entry( 1, lineIn, " "  ).
  lineInBody = substring( lineIn,   22 ).
  if lineInBody matches "*Lines:*" then ...
end.
 

jamie_moore

New Member
Thanks Tom works a treat!

Sorry to keep adding to it how would I get the line immediately successing the line that matches, I've tried the below but it's not liking it.

Code:
DEFINE VARIABLE lineInDateString AS CHAR NO-UNDO.
DEFINE VARIABLE lineInBody AS CHAR NO-UNDO.
DEFINE VARIABLE lineAfterMatch AS CHAR NO-UNDO.
 
INPUT FROM VALUE(logfile).
REPEAT:
  IMPORT UNFORMATTED logfile.
  lineInDateString = SUBSTRING( logFile, 1 , 17 ).
  lineInBody = SUBSTRING( logFile,   22 ).
  IF lineInBody MATCHES "*Lines:*" THEN SKIP(1)
  lineAfterMatch = STRING(logfile).
  MESSAGE "lineInDateString = " lineInDateString SKIP(1)
          "lineInBody = " lineInBody SKIP(1)
          "lineAfterMatch = " lineAfterMatch VIEW-AS ALERT-BOX.
END.
 

RealHeavyDude

Well-Known Member
The technique reads the file line per line sequentially - you can't jump back and forth. In each iteration you have only access to one line. If you need context information, like matching two lines, you must do that in your code by storing the context information for later use. If you are certain that it is the next line you need, than just set a flag and clear that flag when you've processed the next line.

Code:
If lineInBody matches "*Lines:*" then assign foundIt = true.
if foundIt then do:
  /* Your stuff */
  assign foundIt = false.
end.

RealHeavyDude.
 

Stefan

Well-Known Member
Which century are you guys living in using code blocks for code? ;-)

Look a fully functionally example :)

ABL Dojo

Note that I added a fake 'file archived' line to the end of the file (+ the mandatory extra return) to provide something meaningful to indicate end of processing.
 
Last edited:
Top