# Step 3: Formatting output

> The output from xSQL interactive commands can be redirected to a file or suppressed by the `output` command:

>         output console|null|<filename>

> This command switches output of the SQL engine between the console, a null device (suppress output) and a disk file. For example the following commands will direct the output of the successive commands and/or SQL statements to file `S.csv`, then redirect the output to the console:

>         output S.csv
>         select ...

>         output console

> # Formatting Query Output

> As mentioned in Step 2, the output from select statements can be formatted for more readable display. In fact there are a variety of ways to style the output and format specific types of output fields.

> ## Output styles

> By default xSQL displays query results as seen in previous examples. This is termed `TEXT` format. But output can also be displayed in `HTML`, `XML`, `CSV`, or `LIST` formats.

> > ### TEXT

> > For example, run the `account.sql` script and then run the following `select`:

> >         XSQL>script account.sql
> >         XSQL>select * from Account;
> >         userid  name    lastlog
> >         ------------------------------------------------------------------------------
> >         101     Dennis Hamer    01/23/2015 14:15:03
> >         102     Edith Jackson   02/12/2015 19:30:16
> >         103     Eric Harmon     02/14/2015 09:34:47

> > This is the standard or `TEXT` format.

> > ### CSV

> > To switch the above query result to `CSV` format, we use the `format` command which has syntax:

> >         format CSV [delimiter ‘<C>’ [header on|off] ]

> > For example, to use the pipe '|' character to separate fields:

> >         XSQL>format CSV delimiter '|'
> >         XSQL>select * from Account;
> >         userid|name|lastlog|volume
> >         101|Dennis Hamer|01/23/2015 14:15:03|12.775843
> >         102|Edith Jackson|02/12/2015 19:30:16|420.885432
> >         103|Eric Harmon|02/14/2015 09:34:47|0.348857

> > And to remove the header line:

> >         XSQL>format CSV delimiter '|' header off
> >         XSQL>select * from Account;
> >         101|Dennis Hamer|01/23/2015 14:15:03|12.775843
> >         102|Edith Jackson|02/12/2015 19:30:16|420.885432
> >         103|Eric Harmon|02/14/2015 09:34:47|0.348857

> > ### LIST

> > The LIST option displays each field on a separate line, which can be convenient for long field values (i.e. arrays or sequences):

> >         XSQL>format LIST
> >         XSQL>select * from Account;
> >         userid: 101
> >         name: Dennis Hamer
> >         lastlog: 01/23/2015 14:15:03
> >         volume: 12.775843
> >         -------------------------------------------------------
> >         userid: 102
> >         name: Edith Jackson
> >         lastlog: 02/12/2015 19:30:16
> >         volume: 420.885432
> >         -------------------------------------------------------
> >         userid: 103
> >         name: Eric Harmon
> >         lastlog: 02/14/2015 09:34:47
> >         volume: 0.348857
> >         -------------------------------------------------------

> >         Selected records: 3

> > ### HTML

> > The `HTML` option represents the output as an `HTML` table:

> >         XSQL>format HTML
> >         XSQL>select * from Account;
> >         <table border>
> >         <tr><th>userid</th><th>name</th><th>lastlog</th><th>volume</th></tr>
> >         <tr><td>101</td><td>Dennis Hamer</td><td>01/23/2015 14:15:03</td><td>12.775843</td></tr>
> >         <tr><td>102</td><td>Edith Jackson</td><td>02/12/2015 19:30:16</td><td>420.885432</td></tr>
> >         <tr><td>103</td><td>Eric Harmon</td><td>02/14/2015 09:34:47</td><td>0.348857</td></tr>
> >         </table>

> > ### XML

> > The `XML` option displays tags corresponding to the table and field names:

> >         XSQL>format XML
> >         XSQL>select * from Account;
> >         <Account>
> >         <userid>101</userid>
> >         <name>Dennis Hamer</name>
> >         <lastlog>01/23/2015 14:15:03</lastlog>
> >         <volume>12.775843</volume>
> >         </ Account >
> >         < Account >
> >         <userid>102</userid>
> >         <name>Edith Jackson</name>
> >         <lastlog>02/12/2015 19:30:16</lastlog>
> >         <volume>420.885432</volume>
> >         </ Account >
> >         < Account >
> >         <userid>103</userid>
> >         <name>Eric Harmon</name>
> >         <lastlog>02/14/2015 09:34:47</lastlog>
> >         <volume>0.348857</volume>
> >         </ Account >

> ## Numerical format

> Often it is desirable to reformat numerical data in query output. This is easily done by means of the format commands: `numformat`, `dtformat`, `strformat`, `seqformat` and `arrayformat`.

> > ### numformat

> > The `numformat` command uses the standard C `printf()` type format string; for example:

> >         XSQL>numformat "%6.2f"

> > Using `numformat` to round the `close` values to cents in the previous `top_close.sql` query we would specify numeric format as follows:

> >         XSQL>script top_close.sql
> >         symbol  top_index       top_Close       volume
> >         ------------------------------------------------------------------------------
> >         SYM0    {34}    { 97.24}        {732}
> >         SYM1    {57}    { 96.53}        {406}
> >         SYM2    {2}     { 94.87}        {765}
> >         SYM3    {65}    { 98.74}        {953}
> >         SYM4    {62}    { 93.25}        {312}
> >         SYM5    {76}    { 92.52}        {829}
> >         SYM6    {59}    { 94.83}        {761}
> >         SYM7    {98}    { 99.32}        {583}
> >         SYM8    {37}    { 97.59}        {593}
> >         SYM9    {81}    { 93.78}        {257}

> >         Selected records: 10

> > (Note that the format specification string is a string suitable for the C `printf()` function and allowable options depend on the version of the `glibc` library installed on the development system.)

> > ### Date/Time format

> > By default, xSQL will display date fields, like `lastlog` in the current locale settings for the host computer. For example:

> >         XSQL>select lastlog from Account;
> >         lastlog
> >         ------------------------------------------------------------------------------
> >         01/23/2015 14:15:03
> >         02/12/2015 19:30:16
> >         02/14/2015 09:34:47

> > But we can reformat the output using command `dtformat` to display the date in international date format with the time in 12-hour notation as follows:

> >         XSQL>dtformat "%d/%m/%Y %I:%M:%S %p"
> >         XSQL>select * from Account;
> >         userid  name    lastlog
> >         ------------------------------------------------------------------------------
> >         101     Dennis Hamer    23/01/2015 02:15:03 PM
> >         102     Edith Jackson   12/02/2015 07:30:16 PM
> >         103     Eric Harmon     14/02/2015 09:34:47 AM

> > (Note that the format specification string is a string suitable for the C `strftime()` function and allowable options depend on the version of the `glibc` library installed on the development system.)

> > To restore output to the current locale settings:

> >         XSQL>dtformat "%c"

> ## String format

> We might want to restrict string length in query output to a fixed width. For example:

>         XSQL>script strings.sql
>         XSQL>select * from A;
>         strlen  s
>         ------------------------------------------------------------------------------
>         33      A string containing 33 characters
>         14      A short string

> Then restrict the width in output to 20 characters as follows:

>         XSQL>strformat width=20
>         XSQL>select * from A;
>         strlen  s
>         ------------------------------------------------------------------------------
>         33      A string containing>
>         14      A short string

> Note that field `s` in the second query is truncated to length 20 with the `>` character indicating truncation. To return to the full width of string fields, specify zero width as follows:

>         XSQL> strformat width=0

> It is also possible to use the index operator “[from:to]“ with strings to extract a range of characters. For example:

>         XSQL>select s[1:7] from A;
>         #1
>         ------------------------------------------------------------------------------
>         A strin
>         A short

> But in most cases it is preferable to use the `substr()` function (demonstrated in step 6) to extract substrings from character string fields.

> ## Sequence format

> Fields of the `sequence` data type can be ungainly in query output. By default xSQL displays the first 5 and last 5 elements of a `sequence` with an expression like “`…<N element(s)>…`” in the middle. For example, if we start xSQL with command

>         xSQL –i –f IBM-q1-2013.sql

> the script creates table QuoteIBM and initializes it with some historical data from 2013. The sequence field `high` can be displayed as follows:

>         XSQL>numformat "%6.2f"
>         XSQL>select symbol, high from Quote;
>         symbol  high
>         ------------------------------------------------------------------------------
>         IBM     {196.35, 196.29, 194.46, 193.78, 193.30, ...<50 element(s)>..., 213.17, 212.81, 212.50, 212.16, 213.44}

> With command `seqformat` we can limit the number of first and last elements to display. There are two options for how the elements are displayed. The short format displays the elements in order as above; the long format displays the index of each element followed by its value. For example we could reformat the sequence output as follows:

>         XSQL>seqformat short 3 3
>         XSQL> select symbol, high from Quote;
>         symbol  high
>         ------------------------------------------------------------------------------
>         IBM     {196.35, 196.29, 194.46, ...<54 element(s)>..., 212.5, 212.16, 213.44}

>         XSQL>seqformat long 2 1
>         XSQL>select symbol, high from Quote;
>         symbol  high
>         ------------------------------------------------------------------------------
>         IBM     {0:196.35, 1:196.29, ...<57 element(s)>..., 59:213.44}

>         XSQL>seqformat short 3 0
>         XSQL>select symbol, high from QuoteIBM;
>         symbol  high
>         ------------------------------------------------------------------------------
>         IBM     {196.35, 196.29, 194.46, ...<57 element(s)>...}

>         XSQL>seqformat short 0 0
>         XSQL>select symbol, high from QuoteIBM;
>         symbol  high
>         ------------------------------------------------------------------------------
>         IBM     {...<60 element(s)>...}

> It is also possible to use the index operator `[from:to]` with sequences to extract a range of values. For example:

>         XSQL>select symbol, high[1:3] from Quote;
>         symbol  #2
>         ------------------------------------------------------------------------------
>         IBM     {196.29, 194.46, 193.78}

>         XSQL>select symbol, high[57:] from Quote;
>         #1
>         ------------------------------------------------------------------------------
>         IBM     {212.50, 212.16, 213.44}

> ## Array format

> Similar to `seqformat` for sequences, with command `arrayformat` we can limit the number of first and last elements to display for fields of type array. For example, we could create and initialize a table A and restrict the array field output as follows:

>         XSQL>create table B (id integer primary key, arr array(char(10)));
>         XSQL>insert into B values (1, ['zero', 'one', 'two', 'three', 'four', 'five',
>         'six', 'seven', 'eight', 'nine', 'ten']);
>         XSQL>select * from B;
>         id      arr
>         ------------------------------------------------------------------------------
>         1       [zero, one, two, three, four, ...<1 element(s)>..., six, seven, eight, nine, ten]

>         XSQL>arrayformat long 3 3
>         XSQL>select * from B;
>         id      arr
>         ------------------------------------------------------------------------------
>         1       [0:zero, 1:one, 2:two, ...<5 element(s)>..., 8:eight, 9:nine, 10:ten]

>         XSQL>arrayformat short
>         XSQL>select * from B;
>         id      arr
>         ------------------------------------------------------------------------------
>         1	[zero, one, two, three, four, five, six, seven, eight, nine, ten]

> Now that we have seen how to tailor output format, it is useful to explore some of the powerful features of xSQL, like Distributed Database Sharding (step 4), Using Configuration Files (step 5), and more.