Help with string building

[alexis]

Hi

I have an Excel spreadsheet that contains a column of numbers (e.g. 8418, 8423, 8424,8424, 8447 ....)

I want to read this list in order to create a string of the order:

'8418*', '8423*', '8424*','8424*','8447*'

i.e. each number will have a * after it and each set enclosed in single quotes as shown and separated by a comma.

I made a start as follows:

Codes:
LOAD chr(39) & A & '*' & chr(39) As Value
FROM
CommodityCodes.xlsx
(ooxml, no labels, table is Sheet1);

How do I build a string from the table and lose the last comma?

Any ideas would be very welcome.

Regards

Alexis

[Gysbert_Wassenaar · Accepted solution]

Try

CodeList:
LOAD concat(chr(39) & A & '*' & chr(39), ',') As ValueList
FROM
CommodityCodes.xlsx
(ooxml, no labels, table is Sheet1);

[alexandros17]

let

yyy='';



for i = 0 to NoOfRows('Codes')-1

  xxx= Peek('Value', $(i), 'Codes') ;

yyy = yyy & ',' & xxx;



NEXT



let zzz = mid(yyy,2);

[alexis]

Thank you Gysbert.

The complete solution is:

CodeList:

LOAD concat(chr(39) & A & '*' & chr(39), ',') As ValueList

FROM

CommodityCodes.xlsx

(ooxml, no labels, table is Sheet1);

Let vSearch = Peek('ValueList',0,'CodeList');

Thanks

Alexis