Topic Thread

Expand all | Collapse all

Changing DELIMITER in external table definition

  • 1.  Changing DELIMITER in external table definition

    Posted 30 days ago
    ​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
    ------------------------------


  • 2.  RE: Changing DELIMITER in external table definition

    Posted 30 days ago

    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

     






  • 3.  RE: Changing DELIMITER in external table definition

    Posted 30 days ago
    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.








  • 4.  RE: Changing DELIMITER in external table definition

    Posted 30 days ago
    ​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
    ------------------------------



  • 5.  RE: Changing DELIMITER in external table definition

    Posted 30 days ago
    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.








  • 6.  RE: Changing DELIMITER in external table definition

    Posted 30 days ago
    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
    ------------------------------



  • 7.  RE: Changing DELIMITER in external table definition

    Posted 30 days ago

    Can you use fixed width ?

     

    Cheers

    Paul

     






  • 8.  RE: Changing DELIMITER in external table definition

    Posted 30 days ago
    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
    ------------------------------



  • 9.  RE: Changing DELIMITER in external table definition

    Posted 30 days ago
    ​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
    ------------------------------