Changing DELIMITER in external table definition
Rick Fahey got error 26173 using DELIMITER '\\000' and RECORDEND '\\001' in an external table definition. Art Kagel explained a NULL (\\000) cannot be a field delimiter and suggested \\002 as field delimiter with \\001 as record end, or Informix binary or FIXED format; Paul Watson also suggested fixed width. Rick would use \\002, since fixed width does not suit an lvarchar(7500).
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
I have a need to change the Field and end of line DELIMITER in an external table definition, to Octal 000 and Octal 001 .
According to the "documentation" , this would be as such , as part of the DATAFILES definition
DELIMITER '\\000',RECORDEND '\\001'
But, when I run it, I get this error msg
26173: Incorrect value for a keyword DELIMITER '\\000'Anyone run into this before?
------------------------------
Rick Fahey
------------------------------
#Informix
Rick, Did you try \\001 and \\000 Instead of '\\001' and '\\000' The doc is not hyper clear about that, and effecteivement this is no example Eric
Rick: You cannot use a NULL (\\000) as a field delimiter. That is probably the only restricted value. What you could do is to post process the file and change the delimiter using awk or tr or a dedicated "C" or Java program. Examples: awk -F\\| '{printf "%s%c%s%c", $1, 0, $2, 1;}' infile >outfile or tr '|\\n' '\\000\\001' <infile >outfile Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.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 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.
Thanks Art, I tried setting it to something other than \\000 and it didn't give me a syntax error this time. Would have been nice if it explicitly said, "\\000 NULL Not allowed" :-) ------------------------------ Rick Fahey ------------------------------
Rick: It kind of did: " Incorrect value for a keyword DELIMITER '\\000'" It told you that \\000 is an incorrect value for the keyword DELIMITER. I guess it would have been better to say "invalid value" though. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.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 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.
Art, I have seen every printable character in user data at some point. What would you recommend using for a field delimiter? I'm assuming some non printable character. ------------------------------ Rick Fahey ------------------------------
Can you use fixed width ? Cheers Paul
You could try using \\001 for recordend and \\002 for a field delimiter. Other options are using INFORMIX binary format or using FIXED then you won't need delimiters. Art ------------------------------ Art Kagel ------------------------------
Ok, I thought maybe there was best practice for a non printable delimiter. I will try \\002 Fixed wont work to good when you got a lvarchar(7500) ------------------------------ Rick Fahey ------------------------------