# 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

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