Special char for DELIMITER/DBDELMITER

Hi there, Does anyone has an idea how to solve this: I have a file for 'dbload' where a column may contain every possible printable char (including standard '|' but also '^' etc.). I played around with unprintable chars (like 0x6 etc.) for DELIMTER in dbload command file but then dbload throws a syntax error. When I try to use for example 'µ' I get the error that only one-byte-chars are allowed. Problem is that in current state the dbload-file containing the data is written via log4j mechanism and does not escape ('\\\\') the delimter char. Any idea to get around this without changing application? Thanks and regards Joerg

Found a solution: sqlcmd -R does the trick! export DELIMTER=`echo -e '0x06'` sqlcmd -R .... -D $DELIMITER Cheers

U wrote: Does anyone has an idea how to solve this: I have a file for 'dbload' where a column may contain every possible printable char (including standard '|' but also '^' etc.). I played around with unprintable chars (like 0x6 etc.) for DELIMTER in dbload command file but then dbload throws a syntax error. When I try to use for example 'µ' I get the error that only one-byte-chars are allowed. Problem is that in current state the dbload-file containing the data is written via log4j mechanism and does not escape ('\\\\') the delimter char. Any idea to get around this without changing application? --- Something like this may be possible with the older version of dbload (Informix 7, perhaps 9 as well): FILE "/dsc_dev/dat/marks_dsciapt.dat" DELIMITER "\\\\0" 1

Forgot to say that I'm using 10.00 UC5 on Linux. When I follow your hint I get the same: FILE billingData.csv DELIMITER '\\\\0' 9; INSERT INTO billing_data; [dcs@robin-dev ~]$ dbload -d mx2@itest_tcp -c /tmp/billing_data.dbload -l /tmp/err.txt DBLOAD Load Utility INFORMIX-SQL Version 10.00.UC5 Copyright IBM Corporation 1996, 2004 All rights reserved Software Serial Number AAA#B000000 Invalid delimiter; Don't use '\\\\\\\\', SPACE or HEX or Multibyte chars. I can remember that I also used this (non-printable delimiter) many many years ago with dbaccess (or dbload) and it worked with version 7. Anyway... Thanks and regards

How about post-processing the file. First quote all of the characters that you want to use as a final delimiter then change all of the non-printable delimters to the final one. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf 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, Feb 10, 2010 at 5:26 AM, JOERG REDEMANN <joerg.redemann@sabre.com>wrote: > Hi there, > > Does anyone has an idea how to solve this: > > I have a file for 'dbload' where a column may contain every possible > printable > char (including standard '|' but also '^' etc.). I played around with > unprintable chars (like 0x6 etc.) for DELIMTER in dbload command file but > then > dbload throws a syntax error. When I try to use for example 'µ' I get the > error that only one-byte-chars are allowed. > > Problem is that in current state the dbload-file containing the data is > written via log4j mechanism and does not escape ('\\\\') the delimter char. > > Any idea to get around this without changing application? > > Thanks and regards > > Joerg > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001517475f4c37351c047f3f0851