load data with embeded New Line character

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Prince Kumar
    #1

    load data with embeded New Line character

    What is the best way of loading data which has ENTER/new line (ie, ^M)
    character as the part of the data?

    say I have data as follows for table TAB1 (cust#, datetime, memo,
    child)

    7579, "2001-09-14 15:06:26", "sample memo with ^M
    New Line^M
    ",1234

    Thanks,
    Tags: None
    • Ian
      #2
      Re: load data with embeded New Line character

      Prince Kumar wrote:
      [color=blue]
      > What is the best way of loading data which has ENTER/new line (ie, ^M)
      > character as the part of the data?
      >
      > say I have data as follows for table TAB1 (cust#, datetime, memo,
      > child)
      >
      > 7579, "2001-09-14 15:06:26", "sample memo with ^M
      > New Line^M
      > ",1234
      >[/color]

      Use the load option MODIFIED BY DELPRIORITYCHAR




      -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
      http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
      -----== Over 100,000 Newsgroups - 19 Different Servers! =-----

        Comment

        • Prince Kumar
          #3
          Re: load data with embeded New Line character

          Thanks Ian.

          That should fix the issue.

          Prince.

          Ian <ianbjor@mobile audio.com> wrote in message news:<3fcc11a2$ 1_1@corp.newsgr oups.com>...[color=blue]
          > Prince Kumar wrote:
          >[color=green]
          > > What is the best way of loading data which has ENTER/new line (ie, ^M)
          > > character as the part of the data?
          > >
          > > say I have data as follows for table TAB1 (cust#, datetime, memo,
          > > child)
          > >
          > > 7579, "2001-09-14 15:06:26", "sample memo with ^M
          > > New Line^M
          > > ",1234
          > >[/color]
          >
          > Use the load option MODIFIED BY DELPRIORITYCHAR
          >
          >
          >
          >
          > -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
          > http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
          > -----== Over 100,000 Newsgroups - 19 Different Servers! =-----[/color]

            Comment

            • Prince Kumar
              #4
              Re: load data with embeded New Line character

              Thanks Ian, that worked perfect.

              Now I have another issue when loading "" to a char field.

              table test (
              cust# integer
              rec# integer
              status char(1) -- allows only 'T', 'F' or null
              )

              11557,1,"T"
              11611,1,"T"
              11612,1,"" --> has to be loaded as NULL, but getting loaded as
              chr(32), a blank space
              11613,1,"F"

              When loading the data, the "" values are getting loaded as ' ' [ie
              chr(32)]. Is there any modifier to direct the LOAD util to load this
              as NULL?

              Thanks,
              Prince.

              Ian <ianbjor@mobile audio.com> wrote in message news:<3fcc11a2$ 1_1@corp.newsgr oups.com>...[color=blue]
              > Prince Kumar wrote:
              >[color=green]
              > > What is the best way of loading data which has ENTER/new line (ie, ^M)
              > > character as the part of the data?
              > >
              > > say I have data as follows for table TAB1 (cust#, datetime, memo,
              > > child)
              > >
              > > 7579, "2001-09-14 15:06:26", "sample memo with ^M
              > > New Line^M
              > > ",1234
              > >[/color]
              >
              > Use the load option MODIFIED BY DELPRIORITYCHAR
              >
              >
              >
              >
              > -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
              > http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
              > -----== Over 100,000 Newsgroups - 19 Different Servers! =-----[/color]

                Comment

                • Ian
                  #5
                  Re: load data with embeded New Line character

                  Prince Kumar wrote:

                  [color=blue]
                  > 11557,1,"T"
                  > 11611,1,"T"
                  > 11612,1,"" --> has to be loaded as NULL, but getting loaded as
                  > chr(32), a blank space
                  > 11613,1,"F"
                  >
                  > When loading the data, the "" values are getting loaded as ' ' [ie
                  > chr(32)]. Is there any modifier to direct the LOAD util to load this
                  > as NULL?[/color]

                  This is a data format issue, not a LOAD issue.

                  DB2 treats "" as a 0-length string because it has character delimiters.
                  A value with no character delimiters will be loaded as NULL. Your
                  record should look like this:

                  11612,1,


                  If you had another int column after STATUS, your record might look like:

                  11612,1,,14


                  Good luck,





                  -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
                  http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
                  -----== Over 100,000 Newsgroups - 19 Different Servers! =-----

                    Comment

                    • Prince Kumar
                      #6
                      Re: load data with embeded New Line character

                      Thanks Ian,

                      Thats what I end up doing finally. I had a program which unloads the
                      data from oracle. I fixed the progam, not to enclose the data within
                      quotes if it is null.

                      Prince.

                      Ian <ianbjor@mobile audio.com> wrote in message news:<3fcf5b99$ 1_1@corp.newsgr oups.com>...[color=blue]
                      > Prince Kumar wrote:
                      >
                      >[color=green]
                      > > 11557,1,"T"
                      > > 11611,1,"T"
                      > > 11612,1,"" --> has to be loaded as NULL, but getting loaded as
                      > > chr(32), a blank space
                      > > 11613,1,"F"
                      > >
                      > > When loading the data, the "" values are getting loaded as ' ' [ie
                      > > chr(32)]. Is there any modifier to direct the LOAD util to load this
                      > > as NULL?[/color]
                      >
                      > This is a data format issue, not a LOAD issue.
                      >
                      > DB2 treats "" as a 0-length string because it has character delimiters.
                      > A value with no character delimiters will be loaded as NULL. Your
                      > record should look like this:
                      >
                      > 11612,1,
                      >
                      >
                      > If you had another int column after STATUS, your record might look like:
                      >
                      > 11612,1,,14
                      >
                      >
                      > Good luck,
                      >
                      >
                      >
                      >
                      >
                      > -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
                      > http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
                      > -----== Over 100,000 Newsgroups - 19 Different Servers! =-----[/color]

                        Comment

                        Previous template Next
                        Working...