Rows having pipe symbols rejected when loading.

I recently unloaded and re-loaded a customer table from an Informix DB and several rows were rejected because the customer name column contained vertical bars (pipe symbol) character, which is the default delimiter in the source db. I found out that the input field in their customer form has a picture format allowing any alphanumeric character to be entered, which can include any letters, numbers or symbols. So I persuaded the user to run a blanket update on that column to change the pipe symbol to a semicolon. I also discovered other rows containing asterisks, commas, backslashes and tabs in different columns. I could imagine what would happen if this table were to be unloaded in csv format or what damage the other characters could do! What is the best character to define as a delimiter? If tables are already tainted with pipes, commas, asterisks, tabs, backslashes, etc., what's the best way to clean them up?

Hello. The best way is always to avoid these strange characters in input fields. But, as we do not live in a perfect world, you can force quotes in output job, concating one right before the char/varchar field, and one right after it. If any strange chars are included (of course, if none of them are quotes), this should fix your needs. If there is any quotes inside this field, you must remove it manually. Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Informix Senior DBA - Orizon Brasil BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: frankcomputer@ymail.com > Subject: Rows having pipe symbols rejected when loading. [31138] > Date: Mon, 12 Aug 2013 13:36:58 -0400 > > I recently unloaded and re-loaded a customer table from an Informix DB and > several rows were rejected because the customer name column contained vertical > bars (pipe symbol) character, which is the default delimiter in the source db. > I found out that the input field in their customer form has a picture format > allowing any alphanumeric character to be entered, which can include any > letters, numbers or symbols. So I persuaded the user to run a blanket update > on that column to change the pipe symbol to a semicolon. I also discovered > other rows containing asterisks, commas, backslashes and tabs in different > columns. I could imagine what would happen if this table were to be unloaded > in csv format or what damage the other characters could do! > > What is the best character to define as a delimiter? If tables are already > tainted with pipes, commas, asterisks, tabs, backslashes, etc., what's the > best way to clean them up? > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

The unload should be quoting embedded delimiters with a backslash. If users can enter any character, then there is no safe delimiter. 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 Mon, Aug 12, 2013 at 1:36 PM, FRANCISCO USER/DEVELOPER < frankcomputer@ymail.com> wrote: > I recently unloaded and re-loaded a customer table from an Informix DB and > several rows were rejected because the customer name column contained > vertical > bars (pipe symbol) character, which is the default delimiter in the source > db. > I found out that the input field in their customer form has a picture > format > allowing any alphanumeric character to be entered, which can include any > letters, numbers or symbols. So I persuaded the user to run a blanket > update > on that column to change the pipe symbol to a semicolon. I also discovered > other rows containing asterisks, commas, backslashes and tabs in different > columns. I could imagine what would happen if this table were to be > unloaded > in csv format or what damage the other characters could do! > > What is the best character to define as a delimiter? If tables are already > tainted with pipes, commas, asterisks, tabs, backslashes, etc., what's the > best way to clean them up? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c379ec2d2e1f04e3c4a1b5

Any competent UNLOAD code should be escaping any pipe symbols in the unloaded data fields with a backslash, and also escaping any backslashes in the unloaded fields with a backslash. If the UNLOAD code is not doing that, it is inherently broken. The best fix is to ensure that UNLOAD code does its job properly. There's a fairly comprehensive description of the format in the file unload.format in the source code of SQLCMD. There's also code to handle most of the odd-ball case you might need handled (including CSV format output and input) in the SQLCMD code base (mainly in output.c). On Mon, Aug 12, 2013 at 10:36 AM, FRANCISCO USER/DEVELOPER < frankcomputer@ymail.com> wrote: > I recently unloaded and re-loaded a customer table from an Informix DB and > several rows were rejected because the customer name column contained > vertical > bars (pipe symbol) character, which is the default delimiter in the source > db. > I found out that the input field in their customer form has a picture > format > allowing any alphanumeric character to be entered, which can include any > letters, numbers or symbols. So I persuaded the user to run a blanket > update > on that column to change the pipe symbol to a semicolon. I also discovered > other rows containing asterisks, commas, backslashes and tabs in different > columns. I could imagine what would happen if this table were to be > unloaded > in csv format or what damage the other characters could do! > > What is the best character to define as a delimiter? If tables are already > tainted with pipes, commas, asterisks, tabs, backslashes, etc., what's the > best way to clean them up? > > > > ******************************************************************************* > 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." --001a11c1af4c272d5504e3c9a4f2

As it was not stated what unload/load utility was used, there= is one tricky item with external tables in version 11. For speed pu= rposes the default option of the escape option when creating the external t= able is OFF. This means that escapes will not be used. In ve= rsion 12.10 we turned this option on by default and if you want the extra s= peed and you are sure you do not have escapes in your unload file then t= his option if for you. Other than that the informix load utiliti= es should handle escape (i.e. delimiters in the data) without problems. = John F. Miller III STSM, Lead Architect miller3@us.ibm.c= om -----ids-bounces@iiug.org wrot= e: ----- To: ids@iiug.org Fro= m: "Jonathan Leffler" Sent by: ids-bounces@= iiug.org Date: 08/12/2013 05:53PM Subject: Re: Rows having pipe symbo= ls rejected when loa.... [31161] Any competent UNLOAD code should be escaping any= pipe symbols in the unloaded data fields with a backslash, and also es= caping any backslashes in the unloaded fields with a backslash. If the = UNLOAD code is not doing that, it is inherently broken. The bes= t fix is to ensure that UNLOAD code does its job properly. There's a fa= irly comprehensive description of the format in the file unload.format = in the source code of SQLCMD. There's also code to handle most of the o= dd-ball case you might need handled (including CSV format output and in= put) in the SQLCMD code base (mainly in output.c). On Mon, Aug 12, = 2013 at 10:36 AM, FRANCISCO USER/DEVELOPER < frankcomputer@ymail.com= > wrote: > I recently unloaded and re-loaded a customer table= from an Informix DB and > several rows were rejected because the cu= stomer name column contained > vertical > bars (pipe symbol) = character, which is the default delimiter in the source > db. &g= t; I found out that the input field in their customer form has a picture > format > allowing any alphanumeric character to be entered, w= hich can include any > letters, numbers or symbols. So I persuaded t= he user to run a blanket > update > on that column to change = the pipe symbol to a semicolon. I also discovered > other rows conta= ining asterisks, commas, backslashes and tabs in different > columns= . I could imagine what would happen if this table were to be > unloa= ded > in csv format or what damage the other characters could do! > > What is the best character to define as a delimiter? If tab= les are already > tainted with pipes, commas, asterisks, tabs, backs= lashes, etc., what's the > best way to clean them up? > &= gt; > > *************************************************= ****************************** > Forum Note: Use "Reply" to post a r= esponse in the discussion forum. > > -- Jonathan = Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2013.0521 - [1]= http://dbi.perl.org "Blessed are we who can laugh at ourselves= , for we shall never cease to be amused." --001a11c1af4c272d550= 4e3c9a4f2 *****************************************************= ************************** Forum Note: Use "Reply" to post = a response in the discussion forum. = References 1. 3D"http://dbi.perl.org"/

The table was unloaded with dbaccess, using the built-in UNLOAD SQL statement of an SE 7.25.UC5 server. The pipe symbols were not escaped with backslashes, thus failing to load into an SE 4.10 table. In addition to not escaping the pipes, it also inserted an additional newline character after the last unloaded row. Questions: Has anyone else encountered this same anomalistic behavior? Since I have no authorization to directly clean the source table, should I download as is and use the stream editor to replace the pipes with commas? Can more than one character be used as a DBDELIMITER? Thanks in advance!