unload data from informix

Posted in 2013

Summary

Poster wanted to UNLOAD an Informix table using a multi-character field separator ("|") rather than a single character, to satisfy auditors. Art Kagel confirmed the built-in UNLOAD delimiter must be a single character, and others suspected a quoted pipe-separated (CSV-style) file was really wanted. Several workarounds were offered: sqlcmd from the IIUG repository for CSV output, Marco Greco's SQSL with its FORMAT clause, concatenating quotes into the SELECT columns while unloading with '|', or post-processing the .unl file with sed/vi. No confirmation back from the poster.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: Migration, Import/Export & Data Conversion

PUSHPA KUMARA — Tue, 18 Jun 2013 04:35:46 — source: IIUG Forums & Mailing Lists

Hi All, I need to unload data from one informix table using multi delimiters is there any possibility to this. any can help me as my idea unload data should be as follows 123"|"jane"|"sales dept"|" like that 123,jane,sales dept is data from table and "|" should be the delimiter that in need in unload separater Thanks Pushpa

↪ replying to PUSHPA KUMARA

Art Kagel — Tue, 18 Jun 2013 04:42:04 — source: IIUG Forums & Mailing Lists

No can do. The unload separator has to be a single character. Now, instead of asking us how to accomplish the idea you had to solve a problem but couldn't make work, please post the original problem you are trying to solve! I'm sure we can help with that instead. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Jun 18, 2013 at 12:35 AM, PUSHPA KUMARA <pushpa@cybersoft.lk> wrote: > Hi All, > > I need to unload data from one informix table using multi delimiters is > there > any possibility to this. any can help me > > as my idea unload data should be as follows > > 123"|"jane"|"sales dept"|" like that > > 123,jane,sales dept is data from table and > > "|" should be the delimiter that in need in unload separater > > Thanks > Pushpa > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c1a1eafae11c04df6654e0

↪ replying to Art Kagel

PUSHPA KUMARA — Tue, 18 Jun 2013 04:51:51 — source: IIUG Forums & Mailing Lists

Hi, Thanks for the reply, audit people asking they need unload text file with double quote and pipe both delimiters as filed separator so that my requirement as like gave my early post. Thanks Pushpa

↪ replying to PUSHPA KUMARA

Jonathan Leffler — Tue, 18 Jun 2013 05:06:11 — source: IIUG Forums & Mailing Lists

Are you sure they don't want a 'PSV' file like CSV or comma-separated values, but with pipes instead of commas as the field separators and with a quote at the start of the first field and at the end of the last field? It seems odd to want "|" separating fields without a double quote at the start and end of the line. There are open source programs with unload facilities that are available for download. One such is SQLCMD. It has not (yet) been modified to handle multi-character field separators, but it would not be outrageously hard to extend it to do so. It does handle multi-character end-of-line strings so that it can do CRLF line endings on Unix machines, so all the technology is present; it just needs to be fettled to use multi-character strings instead of single characters. That's not entirely trivial, but neither is it outrageously hard. One issue that would have to be checked over is 'what happens when the separator sequence appears in the data'? Backslashes? Doubling up? What if the separator is "" or any other repeated character? What happens with newlines embedded in the fields? Etc. There are semi-standard answers for these questions, but you need to know what is expected before you can get the data in the correct format. Of course, you also have to worry about the loading process too...that can give bigger headaches than unloading, but presumably this is the format required for consumption by some other system, so the Informix part of the code does not need to worry about the reading (until someone decides to massage the data in the other system and then export it in that rather odd format to the Informix system). On Mon, Jun 17, 2013 at 9:51 PM, PUSHPA KUMARA <pushpa@cybersoft.lk> wrote: > Hi, > > Thanks for the reply, audit people asking they need unload text file with > double quote and pipe both delimiters as filed separator so that my > requirement as like gave my early post. > > Thanks > Pushpa > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2013.0521 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --001a1132f848e231f404df66aae8

↪ replying to PUSHPA KUMARA

Marco Greco — Tue, 18 Jun 2013 10:27:50 — source: IIUG Forums & Mailing Lists

On 18/06/13 05:35, PUSHPA KUMARA wrote: > Hi All, > > I need to unload data from one informix table using multi delimiters is there > any possibility to this. any can help me > > as my idea unload data should be as follows > > 123"|"jane"|"sales dept"|" like that > > 123,jane,sales dept is data from table and > > "|" should be the delimiter that in need in unload separater > > Thanks > Pushpa > In terms of Informix tools, no, but my scripting tool SQSL (http://www.sqsl.org) can do that. Assuming that you have built it and install it, the sort of script that you would need to achieve that is OUTPUT TO "myfile"; SELECT emp_id, emp_name, emp_dept FROM mytable FORMAT FULL "\\\\"%i\\\\"\\\\|\\\\"%s\\\\"\\\\|\\\\"%s\\\\"\\\\|"; (admittedly, it's not the format you have asked for, but me thinks that "..."|"..."|"..."| is better than ..."|"..."|"..."|") -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm

↪ replying to PUSHPA KUMARA

Art Kagel — Tue, 18 Jun 2013 12:05:04 — source: IIUG Forums & Mailing Lists

I have never heard of requiring a multi-character field delimiter. Perhaps what they want is a CSV file which would have quotes around each field value and a single character delimiter so that they can load the unload file into an Excel spreadsheet easily? Such a file would look like: "field the first"|"second field"|"3.983" Typically, Informix will append an "extra" field separator at the end of this record, but it will not perform the quoting. You could use Jonathan Leffler's sqlcmd utility to get a CSV file, however. You can download sqlcmd from the IIUG Software Repository and build it. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Jun 18, 2013 at 12:51 AM, PUSHPA KUMARA <pushpa@cybersoft.lk> wrote: > Hi, > > Thanks for the reply, audit people asking they need unload text file with > double quote and pipe both delimiters as filed separator so that my > requirement as like gave my early post. > > Thanks > Pushpa > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae93d8ede2f8f5704df6c8565

↪ replying to PUSHPA KUMARA

Mike Aubury — Tue, 18 Jun 2013 12:44:33 — source: IIUG Forums & Mailing Lists

Sounds like a job for sed, or awk, or perl Heres one way : cat somefile.unl | sed 's/^/"/g' | sed 's/|/"|"/g' | sed 's/|"$/|/g' (sed's done separately for verbosity - first one adds a " at the beginning of each line, second transforms a | to "|" last one removes the trailing " - you might also want to remove the trailing | using sed 's/|"$//g' as the last 'sed' command) On 18 June 2013 05:51, PUSHPA KUMARA <pushpa@cybersoft.lk> wrote: > Hi, > > Thanks for the reply, audit people asking they need unload text file with > double quote and pipe both delimiters as filed separator so that my > requirement as like gave my early post. > > Thanks > Pushpa > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

↪ replying to PUSHPA KUMARA

Bevis Kennedy — Tue, 18 Jun 2013 12:49:17 — source: IIUG Forums & Mailing Lists

export 'filename' delimiter '|' select col1||'"','"'||col2||'"','"'||col3.... On Mon, Jun 17, 2013 at 10:51 PM, PUSHPA KUMARA <pushpa@cybersoft.lk> wrote: > Hi, > > Thanks for the reply, audit people asking they need unload text file with > double quote and pipe both delimiters as filed separator so that my > requirement as like gave my early post. > > Thanks > Pushpa > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Bevis Kennedy IT Analyst II (Public Safety) Department of Technical Services 801-641-8192 --047d7b6dc4ca667b6a04df6d2313

↪ replying to Bevis Kennedy

Everett Mills — Tue, 18 Jun 2013 12:58:20 — source: IIUG Forums & Mailing Lists

You didn't say what your OS is, but assuming it ends with "nix", you could do this: Using dbaccess, UNLOAD TO 'myfile.unl' DELIMITER '|' SELECT * FROM myTable Exit dbaccess and do this: vi myfile.unl :g/|//s/\\\\"|\\\\"/g :wq That should do it. --EEM > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Bevis Kennedy > Sent: Tuesday, June 18, 2013 7:49 AM > To: ids@iiug.org > Subject: Re: unload data from informix [30567] > > export 'filename' delimiter '|' > select col1||'"','"'||col2||'"','"'||col3.... > > On Mon, Jun 17, 2013 at 10:51 PM, PUSHPA KUMARA <pushpa@cybersoft.lk> > wrote: > > > Hi, > > > > Thanks for the reply, audit people asking they need unload text file > > with double quote and pipe both delimiters as filed separator so that > > my requirement as like gave my early post. > > > > Thanks > > Pushpa > > > > > > > > > *********************************************************************** > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Bevis Kennedy > IT Analyst II (Public Safety) > Department of Technical Services > 801-641-8192 > > --047d7b6dc4ca667b6a04df6d2313 > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Everett Mills

Everett Mills — Tue, 18 Jun 2013 13:04:46 — source: IIUG Forums & Mailing Lists

Oops, the vi statement should have been: :g/|/s//\\\\"|\\\\"/g :wq > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Everett Mills > Sent: Tuesday, June 18, 2013 7:58 AM > To: ids@iiug.org > Subject: RE: unload data from informix [30568] > > You didn't say what your OS is, but assuming it ends with "nix", you > could do > this: > > Using dbaccess, UNLOAD TO 'myfile.unl' DELIMITER '|' > SELECT * FROM myTable> > Exit dbaccess and do this: > > vi myfile.unl > > :g/|//s/\\\\"|\\\\"/g > > :wq > > That should do it. > > --EEM > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > Bevis Kennedy > > Sent: Tuesday, June 18, 2013 7:49 AM > > To: ids@iiug.org > > Subject: Re: unload data from informix [30567] > > > > export 'filename' delimiter '|' > > select col1||'"','"'||col2||'"','"'||col3.... > > > > On Mon, Jun 17, 2013 at 10:51 PM, PUSHPA KUMARA <pushpa@cybersoft.lk> > > wrote: > > > > > Hi, > > > > > > Thanks for the reply, audit people asking they need unload text > file > > > with double quote and pipe both delimiters as filed separator so > > > that my requirement as like gave my early post. > > > > > > Thanks > > > Pushpa > > > > > > > > > > > > > > > ********************************************************************** > > * > > ******** > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > -- > > Bevis Kennedy > > IT Analyst II (Public Safety) > > Department of Technical Services > > 801-641-8192 > > > > --047d7b6dc4ca667b6a04df6d2313 > > > > > > > ********************************************************************** > > * > > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.