Fixed width text files are special cases of text files. In a fixed width file, the format is specified by column widths, pad character and left or right alignment. In this format column width are in terms of units of characters. Fixed-width files has field (column) withe same predefined width in each file row, similar to a spreadsheet table. In this article, we will check how to extract Netezza table into fixed-width file format with a working examples.
The examples of fixed width files would be telecom CDR (call detail record) files. Telecom companies extensively uses fixed width files.
Fixed-Width File Overview
There are no field delimiters associated with the fields, and there may or may not be end-of-record delimiter (if present it would be (\n) newline character). Data in fixed width format do not use any date delimiter as the file is fixed-length and it does not require delimiter. The fixed-width file format does not space in between data too. If you want proper date format then you have to explicitly type convert the data and store in database table while importing fixed-width file format data.
Read:
- Export Netezza Data with Quoted Values into Flat File and Example
- Netezza nzsql command and its Usage
- Netezza fixed-width file loading and Examples
Extract Netezza Table into Fixed-width file format
You can Extract Netezza table into fixed-width file format using IBM provided script – nz_select_fixed_data. Usually, the script is available at /nz/support/bin location. The location may vary based on the company folder structure standards. Ask your Netezza admin for exact location of the scripts.
Below is the usage of script:
nz_select_fixed_data [-nullValue <str>] -db <database> -t <table> -width <WIDTHS ...>
Where
-nullValue <str>: This argument is optional. If a column contains a NULL, this is the string that will be used to represent that. By default, a “.” will be used.
-db <database>: This argument is required. The name of the database.
-t <table>: This argument is required. The name of the table.
-width <WIDTHS …>: The widths of each of the columns. If a negative number, the column will be left justified. If a positive number, the column will be right justified. In case If you specify a width of 0 (or don’t specify enough widths) then the column(s) will be skipped.
Check out below example for the sample fixed-width file extract in Netezza database.
Extract Netezza Table into Fixed-width file format Example
Below is the example to Extract Netezza Table into Fixed-width file format:
[nz@netezza bin]$ ./nz_select_fixed_data -db training -t demo_fixed_width_extract -width 1 10 10 ; 1 100 ABC 5 105 EFG 4 104 DEF 2 102 BCD 6 106 FGH 3 104 CDE [nz@netezza bin]$
If you want to save the output to the file, simply redirect the results to file. You can follow below steps:
[nz@netezza bin]$ ./nz_select_fixed_data -db training -t demo_fixed_width_extract -width 1 10 10 > /export/home/nz/demo_fixed_width_extract.dat;
Now verify the file for fixed width data
[nz@netezza ~]$ more /export/home/nz/demo_fixed_width_extract.dat 2 102 BCD 6 106 FGH 4 104 DEF 1 100 ABC 5 105 EFG 3 104 CDE [nz@netezza ~]$