using Informix unload command
natebsi asked how to make UNLOAD put double quotes around every field and add a header row. bozon gave a UNION-based SQL, including a way to generate it from syscolumns. Jonathan Leffler suggested the SQLCMD utility from the IIUG archive: -F quote and -H options, optionally -T and -D, noting embedded quotes and commas can cause problems.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Can someone tell me if it is easily possible to do the following with the unload command: 1. Put double quotes around every field in the file. 2. Put header row at the top of every file, so you know what each field is. I am guessing the answer is probably some creative scripting, but I was hoping someone might have an easier answer. Thanks!
Here is the SQL to do it. You can also generate the SQL from systables
so you don't have so much typing.
unload to junk.csvselect
0, -So it will sort at the top.
<header1>,
<header2>,
<header3>,
...
<headerN>
from
systables
where
tabid = 1
union
select
1,
"'" || <field1> || "'",
"'" || <field2> || "'",
"'" || <field3> || "'",
...
"'" || <fieldN> || "'"
from
<tablet>
order by
1 -- Put header at top
;
To generate this SQL for any table without blobs and where all fields
can be cast to a varchar type this:
unload to test_sql.out delimiter '<put tab character here>'select
"select 0, " || replace(replace(substr(multiset(
select item
'"' || colname || '"'
from
syscolumns c
where
c.tabid = t.tabid
)::lvarchar,
10),
"'",
""),
"}") ||
" from systables where tabid = 1 union " ||
"select 1, " ||
replace(
replace(
replace(
substr(
multiset(
select
colname
from
syscolumns c
where
c.tabid = t.tabid
)::lvarchar,
10
),
"}"
),
"ROW('" ,
"'""' || "
),
"')",
" || '""'"
) ||
" from " ||
t.tabname ||
" order by 1 ; "
from
systables t
where
tabname = "<your table name here>";
You can then format it appropriately.
OH, Yeah and this fricking gave me a headache lining up all of the replaces and quotes so you owe me big time.
natebsi@gmail.com wrote: > Can someone tell me if it is easily possible to do the following with > the unload command: > > 1. Put double quotes around every field in the file. > > 2. Put header row at the top of every file, so you know what each field > is. > > I am guessing the answer is probably some creative scripting, but I was > hoping someone might have an easier answer. sqlcmd -U -d dbase -t table -o outfile -F quote -H Unload mode, given database, table and file name, use double quotes, include column name headers. You can add -T to get the types as well. You don't say what you want as the field separator - you can add -D , to get commas. You also don't specify how to deal with embedded double quotes and commas in the data, which causes problems. SQLCMD is available from the IIUG Software Archive. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/