comparing 2 text files and adding fields

[ok1397]

Helllo everyone. Not if this is possible? I'm working on a project that has 3 text files. File 1 is already formatted to be printed,(it contains information from 2 warehouses) but before ready to print need to compare the first 16 characters of each line with text in File 2 & File 3, then add (for example quantity availabe in file 1,file 2&fle 3) and saved in file 1 for printing. Example:

SKUABC {ON HAND=100} {QTY AVAIL=50} {WH1 AVG=20} ;(1st line in File 1 warehouse1)
SKUABC {ON HAND=25} {QTY AVAIL=10} {WH2 AVG=5} ;(2nd line in File 1 warehouse2)

SKUABC 50 25 10 ;(field values to be added from file 2 to file 1 (for warehouse1)

SKUABC 10 5 5 ;(field values to be added from file 3 to file 1 (for warehouse2)

I appreciate any help..

Thank you

 

[knob]

I have a couple example scripts on the samples page of my site that should get you going. Basically what you would need to do is:

1. Read the Nth line from file 1 and get the length of that line using strlen.
2. Get the values from file2 and file 3 (strextract would work well here by keying of what looks like is hopefully a constant number of space or tabs).
3. The next part may vary depending on what you are needing to do with the data. If you just need to update each line with the data from file2 and file3, I would use strfmt to create a new line using a constant format string and plug in the values from file2 and file3. If the length of this new string is equal to the length of the original string, you would then use the rewind command to back up the file by the number of characters in strlen plus two additional characters to account for the carriage return and line feed at the end of each line. If the lengths are different, you would need to use the finsblock or fdelblock commands to add or remove space as necessary from the file. There should be a couple scripts on my site that show how to do thisas well.

http://www.aspectscripting.com

 

[ok1397]

Hello everyone, i've spent sometime trying to figure above post and have no had any success. I even tried modifying some samples and no luck. Although knob's reccomendations got me started but have not gone too far, would appreciate any help....

Thank you

 

[knob]

Can you post what you have thus far?

http://www.aspectscripting.com

 

[ok1397]

Hello knob, sorry for the delay, what i need is to read the 2-lines with the same Stock # add the integers after QTY AVAIL= and ON ORDER= on each line and add a line right after the 2-lines with same stock # and results,re-saved in the same file and continue reading. For example:

SKU1234 { ON HAND= 23,214 } { QTY ALOC= 2,696 } { QTY AVAIL= 20,518 } { ON ORDER= 12,000 } { TOT AVG= 5,611 }
SKU1234 { ON HAND= 11,540 } { QTY ALOC= 1,984 } { QTY AVAIL= 9,556 } { ON ORDER= 0 } { JUP }
SKU1234 VEND# 123456 ON ORDER 12,000 PO# 93148 ORD: 07/07/06 DUE: 11/20/06

Need to add the results before the line with VEND#

This is the file i'm reading from:

Stock Status as of: 11/10/06



STOCK DEPARTMENT

SKU1234 { ON HAND= 23,214 } { QTY ALOC= 2,696 } { QTY AVAIL= 20,518 } { ON ORDER= 12,000 } { TOT AVG= 5,611 }
SKU1234 { ON HAND= 11,540 } { QTY ALOC= 1,984 } { QTY AVAIL= 9,556 } { ON ORDER= 0 } { JUP }
SKU1234 VEND# 123456 ON ORDER 12,000 PO# 93148 ORD: 07/07/06 DUE: 11/20/06

SKU4567 { ON HAND= 12,760 } { QTY ALOC= 7,963 } { QTY AVAIL= 4,797 } { ON ORDER= 57,540 } { TOT AVG= 8,797 }
SKU4567 { ON HAND= 5,056 } { QTY ALOC= 128 } { QTY AVAIL= 4,928 } { ON ORDER= 0 } { JUP }
SKU4567 VEND# 78912 ON ORDER 30,240 PO# 96553 ORD: 10/23/06 DUE: 02/06/07
SKU4567 VEND# 78912 ON ORDER 27,300 PO# 96598 ORD: 10/24/06 DUE: 02/08/07

SKU7891 { ON HAND= 9,922 } { QTY ALOC= 0 } { QTY AVAIL= 9,922 } { ON ORDER= 4,416 } { TOT AVG= 1,168 }
SKU7891 { ON HAND= 8,305 } { QTY ALOC= 34 } { QTY AVAIL= 8,271 } { ON ORDER= 0 } { JUP }
SKU7891 VEND# 78912 ON ORDER 4,416 PO# 87628 ORD: 01/26/06 DUE: 04/28/06

SKU2234 { ON HAND= 1,548 } { QTY ALOC= 6,902 } { QTY AVAIL= - 5,354 } { ON ORDER= 49,892 } { TOT AVG= 9,530 }
SKU2234 { ON HAND= 13,827 } { QTY ALOC= 7,433 } { QTY AVAIL= 6,394 } { ON ORDER= 0 } { JUP }
SKU2234 VEND# 78912 ON ORDER 3,092 PO# 90663 ORD: 04/20/06 DUE: 07/22/06
SKU2234 VEND# 78912 ON ORDER 9,400 PO# 93146 ORD: 07/07/06 DUE: 10/12/06
SKU2234 VEND# 78912 ON ORDER 18,700 PO# 94371 ORD: 08/10/06 DUE: 11/18/06
SKU2234 VEND# 78912 ON ORDER 18,700 PO# 95429 ORD: 09/18/06 DUE: 12/31/06

Below is the code i've got so far, which of course reads each line that i need. I used usermsg to make sure it is reading correctly. Also on the result line i need to add integers from a second file with the same Stock # (haven't gotten that far yet). Here it goes:

proc main
string sLine
;string sPrevLine ;Check if Stock # Matches Previous Stock #
;string sOutPut
string sVar ;Stock # for warehouse1
string sVar1 ;Stock # for warehouse2
string sVar2
;string sVar3
;string sVar4 ;Stock # for 3rd Line (TO ORDER=)
string sQtyAvl1 = "QTY AVAIL=" ;QTY AVAIL for warehouse1
string sOnOrd1 = "ON ORDER=" ;ON ORDER for warehouse1
;string sQtyAvl2 = "QTY AVAIL=" ;QTY AVAIL for warehouse2
;string sOnOrd2 = "ON ORDER=" ;ON ORDER for warehouse2
;string sBocaAvg = "BOCA AVG=" ;BOCA AVG for warehouse1
string sToOrd = "TO ORDER="
integer iPos ;Pointer position QTY AVAIL for warehouse1
;integer iPos2 ;Pointer position ON ORDER for warehouse1
;integer iPos3 ;Pointer position QTY AVAIL for warehouse2
;integer iPos3 ;Pointer position ON ORDER for warehouse2
;integer iPos4 ;Pointer position ORD BOCA
;integer iQTYAVL1 ;Integer for QTY AVAIL warehouse1
;integer iONORD1 ;Integer for ON ORDER warehouse1
;integer iQTYAVL2 ;Integer for QTY AVAIL warehouse2
;integer iONORD2 ;Integer for ON ORDER warehouse2
;integer iBOCAAVG ;Integer for BOCA AVG warehouse1
;integer iTOTAVL ;Integer for QTY AVAIL warehouse1 + QTY AVAIL warehouse2
;integer iTOTONORD ;Integer for ON ORDER warehouse1 + ON ORDER warehouse2
;integer iTOORD ;Integer for TO ORDER = iBOCAAVG - (iTOTAVL - iTOTONORD)

if isfile "L:\MENUS\CODES\MERGEPC\TEST1.TXT"
if fopen 0 "L:\MENUS\CODES\MERGEPC\TEST1.TXT" READWRITE TEXT
fgets 0 sLine
fgets 0 sLine
fgets 0 sLine
fgets 0 sLine
fgets 0 sLine
fgets 0 sLine
fgets 0 sLine

while not feof 0
fgets 0 sLine
fread 0 sVar 16
usermsg "%s" sVar
if strfind sLine sQtyAvl1 iPos
substr sVar1 sLine iPos+10 9
usermsg "%s" sVar1
if strfind sLine sOnOrd1 iPos
substr sVar2 sLine iPos+9 8
usermsg "%s" sVar2
endif
endif
endwhile
fclose 0
endif
endif

endproc

 

[knob]

OK, so the first two SKU1234 lines are in the text file you're reading from and you need to add the third modified line to the same text file, correct? Or is the third line already in the text file and just needs to be updated?

Below is a part of the script I extended a little bit. It does a little cleanup of the data (gets rid of spaces and commas), then converts the string values to integers so they can be added to the totals later:

          if strfind sLine sQtyAvl1 iPos
            substr sVar1 sLine iPos+10 9
            sVar1 = StripSpaces(sVar1)
            strreplace sVar1 "," ""
             usermsg "%s" sVar1
               if strfind sLine sOnOrd1 iPos
                 substr sVar2 sLine iPos+9 8
                 sVar2 = StripSpaces(sVar2)
                 strreplace sVar2 "," ""
                   usermsg "%s" sVar2
               endif
          endif
          atoi sVar1 iQTYAVL1
          atoi sVar2 iONORD1

Note that you would usually be able to just use the same code above for the second line, but since you are following up the fgets with an fread, the offsets in the sLine string will not match up. Thus, you'll either need to get rid of that fread to get the next variable number, or the code to process the second line read would need to have the offsets for the substr commands changed. Once you decide on what you want to do there and have converted the strings in the second line to integers, you can then add the values from the two different warehouses together and begin to work on the combined line to write to the file.

You'll also need to add the code below to the end of your script (after the endproc). It's a function from my site that strips leading spaces.

func StripSpaces : string
   param string targetline
   integer str_length

   strlen targetline str_length    ;Get length of string

   while str_length > 0            ;While the string has at least one character in it...
      if rstrcmp targetline " " 1  ;Compare the first character in the string to a space
         strdelete targetline 0 1  ;If the first character is a space, delete it from the string
      else
         exitwhile                 ;Else exit the while loop
      endif
   endwhile

   return targetline               ;Return modified string
endfunc

http://www.aspectscripting.com

 

[ok1397]

Knob, Thank you for responding. Yes i need to add the third line. I'll add your code and see what happens, I'll keep u updated. I do need to read the second line as well, and add the values of first line. I'll give it a try. Thank you a bunch.