If you're using the CLI exclusively you can go into shell.c and get rid of the
double spaces. In the copy I'm looking at that's bundled with the 3.14.2
amalgamation it looks like there're 5 lines to alter and you'll be good.
They're in
static int shell_callback(
...
case MODE_Column: {
...
Lines 1007, 1010, 1026 look like they deal with displaying the header line
Lines 1050 and 1054 deal with each row.
All of those lines are the end of a utf8_printf( call and have...
i==nArg-1 ? rowSep : " ");
I tried turning the 2 spaces there into "" recompiled it, and it seems to
display the way you're wanting it to.
Of course since I don't know C, by doing that I have no clue what else I might
just have broken which relied on the old format, but hey :)
-----Original Message-----
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Don V Nielsen
Sent: Friday, October 14, 2016 7:39 AM
To: SQLite mailing list
Subject: Re: [sqlite] .mode column .width .separator
Thank you, Gunter. I will investigate virtual tables; it's a bit over my
head at the moment.
My final implementation was to route the select via .output, employ a CTE
to prepare the data, use printf to setup the record format, and then select
the data from the CTE. Again, I do not enjoy the redundancy of the two
selects, but it gets the job done. A script will handle the statement in
the future.
with pre_process as (
select
... columns blah blah ...
case when piecerate in ('AF','RF') and version_id = '81' then '81' else
segment end as segment,
... columns blah blah ...
from address_txt
)
select
printf(
'%-1s%-15s%-5s%-4s%-2s%-1s%-4s%-4s%-1s%-5s%-1s%-10s%-10s%-10s%-1s%-20s%-20s%-15s%-1s%-10s%-20s%-20s%-50s%-50s%-50s%-50s%-50s%-25s%-2s%-8s%-3s%-6s%-9s',
... columns, blah blah ...
)
from pre_process
;
Works like a champ.
Have a good one.
On Fri, Oct 14, 2016 at 2:58 AM, Hick Gunter <h...@scigames.at> wrote:
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
