none
From a Horizontal List to a Vertical Table?

    Question

  • I need to create a table based on a list of names a numbers, like the example below.

    Alpha;100;55, 77, 91, 93-99
    Bravo;200;25, 29, 33, 44
    Charlie;300;10-13, 15-18, 61, 67-70

    The list is given in a semi-colon delimited text file.  

    I need to generate a table in the following format.

    Name	Code	Number
    Alpha	100	55
    Alpha	100	77
    Alpha	100	91
    Alpha	100	93
    Alpha	100	94
    Alpha	100	95
    Alpha	100	96
    Alpha	100	97
    Alpha	100	98
    Alpha	100	99
    Bravo	200	25
    Bravo	200	29
    Bravo	200	33
    Bravo	200	44
    Charlie	300	10
    Charlie	300	11
    Charlie	300	12
    Charlie	300	13
    Charlie	300	15
    Charlie	300	16
    Charlie	300	17
    Charlie	300	18
    Charlie	300	61
    Charlie	300	67
    Charlie	300	68
    Charlie	300	69
    Charlie	300	70
    

    Getting the first two columns seem easy.  I just seem to be running into problems with loops and getting the third column.   

    As you can tell, the given list specifies ranges of numbers, and I need to generate a record for each number within those given ranges.   And there can be several ranges given per line, along with individual numbers.

    Any help would be much appreciated.

    Thanks,

    Jason...

    Monday, April 22, 2019 1:57 PM

Answers

  • JR,

    Kind of brute force, but this will work if you don't have it yet.

    Steve

    CREATE TABLE Codes (Letter C(7), CodeNum I, Suffix I)
    FOR ii = 1 TO ALINES(aRec,FILETOSTR('MyTextFile.txt'),4))
     cLine = ';' + STRTRAN(aRec(m.ii),',',';') + ';'
     Letter = STREXTRACT(m.cLine,';',';',1)
     CodeNum = VAL(STREXTRACT(m.cLine,';',';',2))
     FOR jj = 3 TO OCCURS(';',m.cLine) - 1
      cItem = STREXTRACT(m.cLine,';',';',m.jj)
      IF '-' $ m.cItem
       nLo = VAL(m.cItem)
       nHi = VAL(SUBSTR(m.cItem,AT('-',m.cItem)+1))
       FOR kk = m.nLo TO m.nHi
        INSERT INTO Codes VALUES (m.Letter, m.CodeNum, m.kk)
       ENDFOR
      ELSE
       INSERT INTO Codes VALUES (m.Letter, m.CodeNum, VAL(m.cItem))
      ENDIF
     ENDFOR
    ENDFOR

    • Marked as answer by JR Cannon Wednesday, April 24, 2019 12:01 PM
    Tuesday, April 23, 2019 11:52 PM

All replies

  • The topic of parsing is not that hard since ALINES() can split with other delimiters than line feeds and besides that you always have STREXTRACT(), even simpler.

    Divide a problem into smaller ones.

    In this case first dived the input lines into simple array elements you can loop:

    ProcessFile.prg:

    Lparameters tcFilename
    tcFilename = EVL(tcFilename,"defaultinput.txt")
    Local lcFile, lnI
    lcFILE = FileToStr(tcFilename)
    FOR lnI = 1 TO ALINES(laLines)
        ProcessLine(laLines[lnI])
    ENDFOR

    One step further to the goal.

    Next step in ProcesLine.prg or a function ProcessLine you split the line into name, code and list of numbers which are separated by semicolons.

    Next step will be a function (name  your choosing) to split the list of numbers at commas, with the given name and code you can create rows now. Despite those parts of the number list not being a single number. You detect that via the "-" in the string and split at "-" to have low and high value of a loop.

    You can do that.

    Bye, Olaf.



    Monday, April 22, 2019 3:31 PM
  • Much thanks, Olaf.  

    I did have the method of finding the '-' in the list of number separated by commas, and creating the loops to go through them, but was getting confused.   And I have not used ALINES() much so researching that for a better understanding, along with some other methods.

    Thanks, Jason...

    Monday, April 22, 2019 6:33 PM
  • JR,

    Kind of brute force, but this will work if you don't have it yet.

    Steve

    CREATE TABLE Codes (Letter C(7), CodeNum I, Suffix I)
    FOR ii = 1 TO ALINES(aRec,FILETOSTR('MyTextFile.txt'),4))
     cLine = ';' + STRTRAN(aRec(m.ii),',',';') + ';'
     Letter = STREXTRACT(m.cLine,';',';',1)
     CodeNum = VAL(STREXTRACT(m.cLine,';',';',2))
     FOR jj = 3 TO OCCURS(';',m.cLine) - 1
      cItem = STREXTRACT(m.cLine,';',';',m.jj)
      IF '-' $ m.cItem
       nLo = VAL(m.cItem)
       nHi = VAL(SUBSTR(m.cItem,AT('-',m.cItem)+1))
       FOR kk = m.nLo TO m.nHi
        INSERT INTO Codes VALUES (m.Letter, m.CodeNum, m.kk)
       ENDFOR
      ELSE
       INSERT INTO Codes VALUES (m.Letter, m.CodeNum, VAL(m.cItem))
      ENDIF
     ENDFOR
    ENDFOR

    • Marked as answer by JR Cannon Wednesday, April 24, 2019 12:01 PM
    Tuesday, April 23, 2019 11:52 PM
  • Much thanks, Steve.   That helps a lot!
    Wednesday, April 24, 2019 12:02 PM