Results 1 to 14 of 14
  1. #1
    Join Date
    Jun 2012
    Posts
    15

    Unanswered: Loading a |^* delimited file to a table

    I have to load a file that has delimiter |^*.
    I am using:


    LOAD FROM D:\Branches.csv OF DEL MODIFIED BY NOCHARDEL COLDEL|^* INSERT INTO branches

    This is separating columns on | while ^* remains with the data. How to make whole |^* a delimeter.


    I am using Win7 OS and DB2 V9.7
    Thanks

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    DB2 expects the deliimiter to be a single byte.
    Your delimiter appears to be three bytes.
    Pre-process the file (e.g. in Perl, or with cygwin awk or tr) to replace the three-bytes by a single byte-character that you already verified is not part of any of the data.
    You can use non-printable character for the delimiter, specifiy it with a 0x as a hex code to the COLDEL.

  3. #3
    Join Date
    Jun 2012
    Posts
    15
    If i want to use non ascii character like ж as delimiter..how can i do that. The data i am working on is of poor quality thats why had used three byte delimiter earlier. using non ascii character as alternative helps decrease chance of it being non unique.

  4. #4
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Choose a byte that is not already in the data.
    Verify with a search tool that the chosen byte is absent from the file.
    Suppose (for example only) that you chose 0xAC as the delimiter.
    You verified that this byte does not appear in the file.
    You can either re-create the CSV file using 0xAC as the delimiter
    or you can pre-process your existing file (with awk, Perl, tr , sed etc) to convert each sequence of your old 3-byte-delimiter to the new chosen single-byte-delimiter (example 0xAC).
    Then use .. MODIFIED BY...COLDEL 0xAC ...

  5. #5
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Another alternative is to not use delimited-files at all, but instead use fixed width files with column-positioning details.
    Choose which is the easiest and most practical option in your circumstances

  6. #6
    Join Date
    Jun 2012
    Posts
    15
    Thanks ...That was helpful Will ask further because i have no idea what 0xAC is and how it will appear in csv.

  7. #7
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    It is only an example, just choose any single-byte character that is verifably absent from the data, or used fixed-width (non-delimited) files indead (if you are using a fixed-size encoding, of course).

  8. #8
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    i have no idea what 0xAC is and how it will appear in csv
    Hex AC is a literal of your own choosing. You need to ensure the the value you choose does not and cannot ever be in the data.

    In the "csv" file, this will appear as a AC after the last byte of the previous field.

  9. #9
    Join Date
    Jun 2012
    Posts
    15
    Quote Originally Posted by papadi View Post
    You need to ensure the the value you choose does not and cannot ever be in the data.
    For instance for 0xac which is 172 ...you mean 172 should not be part of my data...Am i interpreting it correctly?

    Hi, I was searching about Hex Decimal Chars. I found list of few from 0xac

  10. #10
    Join Date
    Jun 2012
    Posts
    15
    I recieve CSV that are created by a tool TALEND...there |^* is specified as delimiter. So by just replacing it with 0xac will work ?
    or there is any other way to define these charaters?

  11. #11
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    If the tool that generates the CSV file allows specification of a hex byte as a delimiter, then that is the simplest way to do it.

    The hex-byte that you choose for your delimiter must not be part of your data.

  12. #12
    Join Date
    Jun 2012
    Posts
    15
    Fine...Got your point... I have got CSV with hex dec delimiter and now i to load it in DB2.

    I am using
    LOAD FROM D:\branches.csv OF DEL MODIFIED BY COLDEL0xac INSERT INTO db_a.branches

    the problem is that all data is loaded in one column including the delimeters.

    Any ideas?

  13. #13
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Most likely the file does not contain the specified delimiter.
    Examine the file with a hex editor...

  14. #14
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    try to find the dba in your company or if none, try to hire a consultant that can help and even teach you how todo this in the future.. (not for free of course)
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •