I should probably say: don't do this at home, kids.

I'm not really sure what sroux is after. I have assumed the 'intention' from the solutions that others baked.

For fun, I open up a postgres SQL interface to the data in this file. I'll admit this postgres spelunking is not for the faint-hearted or for DB beginners. The advantage is getting access to a textfile via the power of SQL. 

# bash 

file=/tmp/flatfile.dat  t=flattable  sep='|'

echo "head1|head2|head3
val1|val2|val3
val1|val4|val5
val6|val4|val5
val2|val7|val5
val3|val7|val3" > $file

# postgres extension 'file_fdw' must be installed (but haven't we all)
+:
psql -qXa -c "create server if not exists fs foreign data wrapper file
+_fdw;" 

psql -qXa << SQL_PERL
drop foreign table if exists $t;
create foreign table $t ($( < $file perl -ne 'chomp; my $t = "'$t'";
print join("\n , ", map{$_ .= " text"} split(/['${sep}']/)); exit;'
))
server fs options(delimiter'$sep',format'csv',header'TRUE',filename'$f
+ile');
SQL_PERL

[download]

running the above, on-screen the SQL that was run, echoed from psql: 

create server if not exists fs foreign data wrapper file_fdw;

drop foreign table if exists flattable;
create foreign table flattable (head1 text
 , head2 text
 , head3 text
)
server fs options(delimiter'|',format'csv',header'TRUE',filename'/tmp/
+flatfile.dat');

[download]

Now the file is accessible as a foreign table named 'flattable'.

Once a postgres table, you can run SQL against it, for instance this SELECT: 


(select 'head1' "heads", array_agg(distinct head1 order by head1) "val
+ues" from flattable) union all 
(select 'head2' "heads", array_agg(distinct head2 order by head2) "val
+ues" from flattable) union all 
(select 'head3' "heads", array_agg(distinct head3 order by head3) "val
+ues" from flattable)

-- output:
--
--  heads |        values         
-- -------+-----------------------
--  head1 | {val1,val2,val3,val6}
--  head2 | {val2,val4,val7}
--  head3 | {val3,val5}
-- (3 rows)

[download]

In case of many columns, say hundreds (it happens!), generate the needed SQL. (see psql's \gexec -- left as an exercise for the reader)

In reply to [OT] Re: Get unique fields from file by erix
in thread Get unique fields from file by sroux

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.