You can eliminate separators by using
.mode list
.sepa ""

But then you need to format your values to the correct widths for the record 
description. Text processing is not the primary domain of SQLite and is best 
left to the presentation layer.

Alternatively you may consider writing a virtual table module to do the 
processing for you. Semantics could be similar to:

CREATE VIRTUAL TABLE mainframe_file USING mainframe ( '<fileformat>', 
'<filename>', [<header_data>]); --> open filename, prepare to write in 
specified format, write header
INSERT INTO mainframe_file SELECT ...; -> stuff in data, write record
DROP TABLE mainframe_file; -> close the file, write trailer record if required

-----Urspr√ľngliche Nachricht-----
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Don V Nielsen
Gesendet: Donnerstag, 13. Oktober 2016 20:51
An: General Discussion of SQLite Database <sqlite-users@mailinglists.sqlite.org>
Betreff: [sqlite] .mode column .width .separator

I am using the command line shell SQLite 3.14.2 2016-09-12. I'm working with 
mainframe data in a fixed format.

I would like to use .mode column to create my output text file in a fixed 
layout. I set all my column widths using .width. I then output my data to a 
file. Unfortunately, there are two blanks separating each column, space I don't 
want to be there. The .separator command does not provide any mechanism for 
turning it off. Is there a way?

I realize there is a printf function available. However, it appears that output 
values must come from a table column, where as below, I could use case 
statements in the sql select of the data.

Any suggestions? I think I am overlooking a .separator option that says "don't 
put spaces between output columns". I assume that John McKown has faced this 
already, given his mainframe pedigree.

Thanks for your time,
dvn



Sample output:
"H  0NZOX0001687395  83501  5827  "...

.mode column
.width 1 15 5 4 2 1 4 4 1 5 1 10 10 10 1 20 20 15 1 10 20 20 50 50 50 50 50
25 2 8 003 006 009
.output vo_pwprep.txt
select
  recid,
  z_num,
  zip,
  zip4,
  dpbc,
  ckdig,
  cart,
  lot,
  lot_order,
  walk_seq,
  walk_seq_bic,
  case when piecerate in ('AF','RF') and version_id = '81' then '81' else 
segment end as segment,
  version_id,
  message,
  seed,
  seed_id,
  seed_key,
  planet,
  ocr_acr,
  priority,
  keycode,
  custno,
  name,
  title,
  firm_id,
  addr1,
  addr2,
  city,
  state,
  seq_number,
  srvc_type,
  imb_mid,
  imb_serial
from address_txt
;
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to