Comma delimited list

[nate100]

With a FOR each loop, I need to create a comma delimited list for a field.
for example if a table has the following in Field1
Field1
_____
"one"
"two"
"three"

I need to have the list be: one,two,three

what is the most efficient way of creating a list that is comma delimited?

 

[Casper]

I think it will be:

define variable a as character no-undo.
for each <table>:
   assign a = a + <value> + ','.
end.
assign a = trim(a,',').

Casper.

 

[montamig]

Another options would be:

OUTPUT TO report.dat.
FOR EACH <table> NO-LOCK:
EXPORT <field1> <field2 <field3>.
END.

The result is, for example:

"A" "123" "Z1"
"B" "456" "Z2"
"C" "789" "Z3"

Bye.

 

[palthe]

Hm doesn't the TS mean a comma delimited list of only 1 field in the table?

In that case:


def var hbuf as handle.
def var ctab as char.
def var hque as handle.
def var cval as char.
def stream stre.

output stream stre to value("c:\temp\export.txt").
ctab = "<table>".
cvar = "".
create buffer hbuf for table ctab.
create query hque.
hque:set-buffers(hbuf).
hque:query-prepare(substitute("for each &1",ctab)).
hque:query-open.
hque:get-first(no-lock).
do while not hque:query-off-end:

cvar = cvar + "," + hbuf:buffer-field(1):buffer-value.

hque:get-next(no-lock).
end.

hque:query-close.
delete object hque.

cvar = trim(cvar,",").
put stream stre unformatted cvar.


In the above, the buffer-field(1) is of course the number of the field you want to export, in this case field number 1.
But if you really want it done right, a "," is not the most beautiful character to be used as a delimiter.
Why not?

Imagine you have the following values of your Field(1) column:
"one"
"two"
"three,four"
"five"

what will happen? Exactly, you get a wrong interpretation of the values in your export. Personally, i'd advocate a CHR(1) delimiter for exports, because this is a delimiter that would never be found in any character or integer string. This is of course a little off topic, but it never hurts to start some discussion on how to put things right ;-).

 

[Casper]

Reading back his post, I suppose you are right.

But he also asked the fastest way and that would be without the if statement.
Just trim or substring the end result would be fastest, and while we are talking about best ways: Never forget to cleanup all your dynamic stuff

Casper.

 

[palthe]

Reading back his post, I suppose you are right.

But he also asked the fastest way and that would be without the if statement.
Just trim or substring the end result would be fastest, and while we are talking about best ways: Never forget to cleanup all your dynamic stuff

Casper.

Already adjusted it, you're right. Oh, the dreadfulness of being to eager and posting to fast...
To the off-topic part of my post:
What are your thoughts about delimiting? Do you use an unique delimiter such as CHR(1)? It's just something that came along in some of my scripting; I discovered inconsistencies between an export and an import, because I was using a "common" delimiter, such as space or ",".
Of course, at first I was thinking about the problem, not the solution, so I thought of all kind of functions that filtered out the ","'s in the character strings as not being delimiters... learned much of that, by the way hehe

 

[Casper]

We usually use chr(1) as a delimiter.
But the main problem there is with building such lists is that you are bound to hit the 32K limit some time.
So only if we need to have it in a delimiter delimited list (like for excel) we do that, otherwise I'd rather put it in a temp-table.

Casper.

 

[nate100]

Thanks Casper. The solution you had worked great. Thanks for everybody's of your help!