Answered by:
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, 9399 Bravo;200;25, 29, 33, 44 Charlie;300;1013, 1518, 61, 6770
The list is given in a semicolon 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...
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
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.

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

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
