# 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. To demonstrate the following examples we use a simple table `Account` which can be defined in a script file `account.sql` as follows:

>         create table Account (userid integer primary key, name string, lastlog date);
>         insert into Account values (101, 'Dennis Hamer', '2015-01-23 14:15:03');
>         insert into Account values (102, 'Edith Jackson', '2015-02-12 19:30:16');
>         insert into Account values (103, 'Eric Harmon', '2015-02-14 09:34:47');

> Then start xSQL with the following command from directory `eXtremeDB/samples/xsql/scripts`:

>         ..\..\..\target\bin\xsql -i

> ## 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 strings):

> >         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`, and `arrayformat`.

> > ### numformat

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

> >         XSQL>numformat "%6.2f"

> > For example, we could use the following `numformat` command to round values to two decimal places:

> >         XSQL>create table N(name string, value double);
> >         XSQL>insert into N values('Pi', 3.14159265359);
> >         XSQL>insert into N values('e', 2.71828);
> >         XSQL>select * from N;

> >         name    value
> >         ------------------------------------------------------------------------------
> >         Pi      3.14159265359
> >         e       2.71828

> >         Selected records: 2
> >         XSQL>numformat "%6.2f"
> >         XSQL>select * from N;

> >         name    value
> >         ------------------------------------------------------------------------------
> >         Pi        3.14
> >         e         2.72

> >         Selected records: 2

> > ### 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>create table A (strlen integer, s string);
>         XSQL>insert into A values (33, 'A string containing 33 characters');
>         XSQL>insert into A values (14, 'A short string');
>         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

> 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 B 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]

> Note that there are 11 elements in the array `arr`, but only the first and last 5 elements appear in the query output with "...<1 element(s)>...," in the middle. We can restrict the number of elements shown by specifying how many elements from the beginning of the array and how many from the end of the array to display.

> And 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>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.