Informix

 View Only

  • 1.  Changing DELIMITER in external table definition

    Posted Mon April 27, 2020 12:30 PM
    ​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


  • 2.  RE: Changing DELIMITER in external table definition

    Posted Mon April 27, 2020 12:51 PM

    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

     




    Original Message


  • 3.  RE: Changing DELIMITER in external table definition

    IBM Champion
    Posted Mon April 27, 2020 12:52 PM
    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


    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.






    Original Message


  • 4.  RE: Changing DELIMITER in external table definition

    Posted Mon April 27, 2020 02:55 PM
    ​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
    ------------------------------

    Original Message


  • 5.  RE: Changing DELIMITER in external table definition

    IBM Champion
    Posted Mon April 27, 2020 03:06 PM
    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


    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.






    Original Message


  • 6.  RE: Changing DELIMITER in external table definition

    Posted Mon April 27, 2020 03:09 PM
    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
    ------------------------------

    Original Message


  • 7.  RE: Changing DELIMITER in external table definition

    IBM Champion
    Posted Mon April 27, 2020 03:21 PM

    Can you use fixed width ?

     

    Cheers

    Paul

     




    Original Message


  • 8.  RE: Changing DELIMITER in external table definition

    IBM Champion
    Posted Mon April 27, 2020 03:25 PM
    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
    ------------------------------

    Original Message


  • 9.  RE: Changing DELIMITER in external table definition

    Posted Mon April 27, 2020 03:31 PM
    ​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
    ------------------------------

    Original Message