Unload
User reported that when unloading table data to text file, columns containing spaces get backslashes prepended. Responses explained this is intentional Informix behavior: backslash-space represents empty non-null VARCHAR values, distinguishing them from NULL (shown as adjacent delimiters). LOAD understands this convention.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I am using unload to export a table into a text file. Several columns contain spaces. When these are unloaded, a backslash (\\\\) is added to the space value. Is there any way to prevent this from happening? Thank you John
I am guessing you have some zero length strings in your data so the \\\\<space> would be the normal export activity. I don't know of a way of disabling but a simple sed script would 'fix' it Cheers Paul > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > JOHN KRUPA > Sent: Wednesday, August 28, 2013 12:10 PM > To: ids@iiug.org > Subject: Unload [31290] > > I am using unload to export a table into a text file. Several columns contain > spaces. When these are unloaded, a backslash (\\\\) is added to the space > value. > Is there any way to prevent this from happening? > > Thank you > John > > > ********************************************************** > ********************* > Forum Note: Use "Reply" to post a response in the discussion forum.
Since the LOAD verb understands this convention, it should not be a problem. Unless, I guess, you are exporting to import the data into something else? 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 Wed, Aug 28, 2013 at 1:09 PM, JOHN KRUPA <jkrupa@turretsteel.com> wrote: > I am using unload to export a table into a text file. Several columns > contain > spaces. When these are unloaded, a backslash (\\\\) is added to the space > value. > Is there any way to prevent this from happening? > > Thank you > John > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7b343cd202251304e5056f76
On Wed, Aug 28, 2013 at 10:09 AM, JOHN KRUPA <jkrupa@turretsteel.com> wrote: > I am using unload to export a table into a text file. Several columns > contain > spaces. When these are unloaded, a backslash (\\\\) is added to the space > value. > Is there any way to prevent this from happening? > No. There is a distinction between a VARCHAR column containing NULL and one containing an empty string (it is not null). At the storage level, the NULL is represented by 0x01 0x00 (length 1, byte value 0), but the empty (non-null) is represented by 0x00 (length 0). In the UNLOAD-format output, the NULL is represented by adjacent delimiters; the empty non-null is represented by backslash-space. The loaders know how to handle this distinction. If you need to erase the distinction (converting non-null values into null values in the process, you can post-process the output looking for delimiter-backslash-space-delimiter and replacing it with delimiter-delimiter. -- 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." --001a11c29d221b2e3104e5058892
There is an escape sequence you can set. j. On Aug 28, 2013, at 1:36 PM, Art Kagel <art.kagel@gmail.com> wrote: > Since the LOAD verb understands this convention, it should not be a=20 > problem. Unless, I guess, you are exporting to import the data into=20 > something else?=20 >=20 > Art=20 >=20 > Art S. Kagel=20 > Advanced DataTools (www.advancedatatools.com)=20 > Blog: http://informix-myview.blogspot.com/=20 >=20 > Disclaimer: Please keep in mind that my own opinions are my own = opinions=20 > and do not reflect on my employer, Advanced DataTools, the IIUG, nor = any=20 > other organization with which I am associated either explicitly,=20 > implicitly, or by inference. Neither do those opinions reflect those = of=20 > other individuals affiliated with any entity with which I am = affiliated nor=20 > those of the entities themselves.=20 >=20 > On Wed, Aug 28, 2013 at 1:09 PM, JOHN KRUPA <jkrupa@turretsteel.com> = wrote:=20 >=20 >> I am using unload to export a table into a text file. Several columns=20= >> contain=20 >> spaces. When these are unloaded, a backslash (\\\\) is added to the = space=20 >> value.=20 >> Is there any way to prevent this from happening?=20 >>=20 >> Thank you=20 >> John=20 >>=20 >>=20 >>=20 >>=20 > = **************************************************************************= *****=20 >> Forum Note: Use "Reply" to post a response in the discussion forum.=20= >>=20 >>=20 >=20 > --047d7b343cd202251304e5056f76=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20
If all row in that columns contains spaces, you cat say in select, CAST(NULL AS CHAR) instead of name of colums with space ... On 28.08.2013 19:09, JOHN KRUPA wrote: > I am using unload to export a table into a text file. Several columns contain > spaces. When these are unloaded, a backslash (\\\\) is added to the space value. > Is there any way to prevent this from happening? > > Thank you > John > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- *Ivan Zaviç* System & database administrator *T* +381 21 68 98 608 | *M* +381 69 846 99 08 *@*ivan.zavis@mi-system.co.rs <mailto:ivan.zavis@mi-system.co.rs> *M&I Systems, Co. Group* Bulevar vojvode Stepe 16, 21000 Novi Sad *T:* +381 21 68 98 602 *F:* +381 21 68 98 604 *@:* info@mi-system.co.rs *w:* www.mi-system.co.rs <http://www.facebook.com/pages/MI-Systems-Co/263409380366499>; <http://www.linkedin.com/company/m&i-systems-co.>; <http://www.youtube.com/misystemsco>; Odricanje od odgovornosti: Ovaj dokument namenjen je samo licima kojima je upucen i za pozivanje na isti od stane bilo kog lica, neophodna je naknadna pismena potvrda njegovog sadr§aja. Shodno tome, M&I Systems, Co. Novi Sad odrice svaku odgovornost i ne prihvata bilo kakvu obavezu (ukljucujuci slucaj nepa§nje) za posledice koje mo§e pretrpeti bilo koje lice zbog cinjenja ili necinjenja na bazi takve informacije pre nego çto takva lica prime dodatnu pismenu potvrdu. Ukoliko ste greçkom primili ovu elektronsku poruku, uniçtite ili izbriçite istu sa vaçeg racunara. Svako umno§avanje, çirenje, kopiranje, obelodanjivanje, izmene, distribucija i/ili objavljivanje ove elektronske poruke je strogo zabranjeno. Sadr§aj ove elektronske poruke ne predstavlja nu§no stavove M&I Systems, Co. Novi Sad