Outputting a %SQL.StatementResult to CSV

Question

Question

Timothy Leavitt · Dec 14, 2021

#ObjectScript #SQL #Caché #InterSystems IRIS

Is there some generic way to output a %SQL.StatementResult to CSV?

Discussion (6)3

Log in or sign up to continue

José Pereira · Dec 14, 2021

Hi!

Please, check out if this answer from @Eduard Lebedyuk works for you: https://community.intersystems.com/post/query-result-save-csv#comment-11...

1 0

score 1 · Answer 1 · 2021-12-14T16:23:22-05:00

Thanks - simple example is:

do ##class(%SQL.Statement).%ExecDirect(,"select top 10 'foo'").%DisplayFormatted("CSV",,,.filesUsed)
do ##class(%Stream.FileCharacter).%OpenId(filesUsed(1)).OutputToDevice()

score 1 · Answer 2 · 2021-12-15T15:15:37-05:00

As it says in the Class Reference, "NOTE: pFormat of CSV/100 is not a true comma separated value, but instead uses TAB separated values."

score 0 · Answer 3 · 2021-12-15T15:22:19-05:00

Timothy Leavitt · Dec 15, 2021

Yeah... kind of annoying.

0 0

score 0 · Answer 4 · 2021-12-27T18:15:01-05:00

Hi

I think that there is a 'DOS CSV' file format that dows use COMMAS but I am not sure how you would specify it. In Excel, it is one of the options when exporting a spreadsheet to a delimited text file.

Nigel

score 0 · Answer 5 · 2022-01-06T17:43:24-05:00

Would be nice to add a parameter to %DisplayFormatted for specifying a separator character/string when the first parameter is "CSV". Until then, I'll go through the result set and use %Print("|") to use a non-tab, non-comma separator when exporting results with embedded commas as requested by a customer (they don't like tabs).

Using Dynamic SQL | Using InterSystems SQL | InterSystems IRIS Data Platform 2021.1