Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Chicagoland
SQL Output Options
has no ratings.
Published: 02 Jun 2014
Revised: 16 Dec 2015 - 646 days ago
Last viewed on: 21 Sep 2017 (2446 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

SQL Output Options Published by: Bob Cozzi on 02 Jun 2014 view comments(7)

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: Ringer
Premium member *
Comment on: SQL Output Options
Posted: 3 years 3 months 20 days 22 hours 14 minutes ago

How about *TAB delmited? Parsers can get confused when strings contain quotes but if the CSV is tab delimited, it's a piece of cake to parse, since users cannot enter tabs in any screens (web or 5250). 

Chris Ringer

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: SQL Output Options
Posted: 3 years 3 months 20 days 21 hours 58 minutes ago
Edited: Tue, 03 Jun, 2014 at 09:21:44 (1207 days ago)

Chris. The existing coztools' CPYTOCSV allows TABS, but would you want it to be simply a text file (like a text version of a Report) but instead of the blanks between columns, there would be a TAB character, without any quotes or commas?

Posted by: Ringer
Premium member *
Comment on: SQL Output Options
Posted: 3 years 3 months 20 days 20 hours 8 minutes ago

Correct. Tab delmited instead of comma delimited.

The way CPYTOIMPOF does it with STRDLM(*NONE) STRESCCHR(*NONE) FLDDLM(*TAB). 

"Bob""s","Data" would instead just look like Bob"s<tab>Data

Chris 

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: SQL Output Options
Posted: 3 years 3 months 20 days 18 hours 51 minutes ago

Okay. Got it. Thanks!

Bye the way, the Cozzi appTools has a csvparse() function that allows you to retrieve each "field" of a CSV record either by column name or by relative column number. I use it to import CSV data all the time.

Posted by: tdaly
Premium member *
Comment on: SQL Output Options
Posted: 3 years 3 months 20 days 12 hours ago
Edited: Tue, 03 Jun, 2014 at 18:41:42 (1207 days ago)

Along with *CSV I'd add *DELIMITED

I've encountered two kinds of delimited files:

1) Delimited with quoted strings
 User specified delimiter, anything can be the delimiter: vertical bar, equal sign, tab, whitespace, null, tilde, caret, some other user defined character or hex

 Same idea but more flexible than CSV


2) Delimited with Escaped Delimiter
 User specified delimiter  (for example, backslash)
 Doubling the delimiter escapes the delimiter
 
 sometimes files from *nix systems can be like this


So maybe *DELIMITED would be *QOUTEDELIM and *ESCDELIM, or something like that.

It would also be useful to specify a Record Delimiter: null, cr, lf, some other user defined character or hex byte(s)

Posted by: clbirk
Premium member *
Comment on: SQL Output Options
Posted: 3 years 3 months 18 days 15 hours 2 minutes ago

how about it putting out a new table OUTPUT(*TABLE)? 

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: SQL Output Options
Posted: 3 years 3 months 18 days 14 hours 26 minutes ago

We already do that, clbirk.