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
