I'm tackling this as an Informix question rather than an Awk question.
Using the standard Informix SQL commands, you could create an external table in CSV format, too — but you have to know that there is an undocumented format "DB2" that you can use:
DROP TABLE IF EXISTS data_table;
CREATE TABLE data_table
(
part_no INTEGER,
date_part DATETIME YEAR TO SECOND,
history_code VARCHAR(4),
user_id VARCHAR(32),
other_information VARCHAR(64),
pool_no INTEGER
);
INSERT INTO data_table VALUES(100000001, "2010-10-13 12:12:12", "ABCD", "rsmith", "note: Monday, December 10", 101011777);
INSERT INTO data_table VALUES(100000002, "2010-10-21 12:12:12", "GHIJ", "jsmith", NULL, 101011888);
INSERT INTO data_table VALUES(100000002, "2010-10-27 12:12:12", "LMNO", "fevers", "[Mail]", 101011999);
INSERT INTO data_table VALUES(100000003, "2010-11-13 12:12:12", "QXRT", "sjohnson", "note: Tuesday, August 31", 101011111);
DROP TABLE IF EXISTS csv_data;
CREATE EXTERNAL TABLE csv_data
(
part_no INTEGER,
date_part DATETIME YEAR TO SECOND,
history_code VARCHAR(4),
user_id VARCHAR(32),
other_information VARCHAR(64),
pool_no INTEGER
)
USING (FORMAT "DB2", DELIMITER ",", DATAFILES("DISK:/tmp/data/csv_data.csv"));
INSERT INTO csv_data
SELECT part_no, date_part, history_code, user_id, other_information, pool_no
FROM data_table;
The content of /tmp/data/csv_data.csv then looks like:
100000001,2010-10-13 12:12:12,"ABCD","rsmith","note: Monday, December 10",101011777
100000002,2010-10-21 12:12:12,"GHIJ","jsmith",,101011888
100000002,2010-10-27 12:12:12,"LMNO","fevers","[Mail]",101011999
100000003,2010-11-13 12:12:12,"QXRT","sjohnson","note: Tuesday, August 31",101011111
UNLOAD format converted to CSV
The default output from DB-Access is not readily amenable to parsing in practice.
It may be doable in some limited cases, such as the one you show, but you'd do better using the UNLOAD format instead of the command-line output, and then converting the UNLOAD data format to CSV.
I have a Perl script that does that. It uses the Perl Text::CSV module to handle CSV formatting. It doesn't pretend to handle the first line with column names; those aren't present in the UNLOAD format file.
#!/usr/bin/env perl
#
# @(#)$Id: unl2csv.pl,v 1.3 2018/06/29 20:36:58 jleffler Exp $
#
# Convert Informix UNLOAD format to CSV
use strict;
use warnings;
use Text::CSV;
use IO::Wrap;
my $csv = new Text::CSV({ binary => 1 }) or die "Failed to create CSV handle ($!)";
my $dlm = defined $ENV{DBDELIMITER} ? $ENV{DBDELIMITER} : "|";
my $out = wraphandle(\*STDOUT);
my $rgx = qr/((?:[^$dlm]|(?:\\.))*)$dlm/sm;
# $csv->eol("\r\n");
while (my $line = <>)
{
print "1: $line";
MultiLine:
while ($line eq "\\\n" || $line =~ m/[^\\](?:\\\\)*\\$/)
{
my $extra = <>;
last MultiLine unless defined $extra;
$line .= $extra;
}
my @fields = split_unload($line);
$csv->print($out, \@fields);
}
sub split_unload
{
my($line) = @_;
my @fields;
print "$line";
while ($line =~ $rgx)
{
printf "%d: %s\n", scalar(@fields), $1;
push @fields, $1;
}
return @fields;
}
__END__
=head1 NAME
unl2csv - Convert Informix UNLOAD to CSV format
=head1 SYNOPSIS
unl2csv [file ...]
=head1 DESCRIPTION
The unl2csv program converts a file from Informix UNLOAD file format to
the corresponding CSV (comma separated values) format.
The input delimiter is determined by the environment variable
DBDELIMITER, and defaults to the pipe symbol "|".
It is not assumed that each input line is terminated with a delimiter
(there are two variants of the UNLOAD format, one with and one without
the final delimiter).
=head1 EXAMPLES
Input:
10|12|excessive|cost \|of, living|
20|40|bou\\ncing tigger|grrrrrrrr|
Output:
10,12,"excessive","cost |of, living"
20,40,"bou\ncing tigger",grrrrrrrr
=head1 PRE-REQUISITES
Text::CSV_XS
=head1 AUTHOR
Jonathan Leffler <jonathan.leffler@hcl.com>
=cut
You would use a command such as this (via DB-Access):
UNLOAD TO "datatable.unl" SELECT * FROM DataTable;
and then run:
perl unl2csv datatable.unl > datatable.csv
SQLCMD program
If you have my SQLCMD program (available from the IIUG web site in the software repository — and wholly unrelated to Microsoft's johnny-come-lately with the same name), then you can unload direct to CSV format:
sqlcmd -d database -F csv -e 'unload to "data_table.csv" select * from data_table'
shockeddisappointed. – glenn jackman Jun 29 '18 at 16:28