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
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.