Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2004
    Posts
    31

    Unanswered: Problem uploading Sybase bcp data to Oracle DB

    I bcp'ed some data (lots of data) from a Sybase 12.5.2 server to a flat file, beow is an example of the a line in that file:


    1234,1234,May 19 2003 12:00:00:000AM,,,Apr 22 2005 9:08:36:000AM,ABC01,1000,,,1,,


    The problem is the data needs to be uploaded to an Oracle DB however it is failing because of the above portion of the date-time field, is there away for me to bcp out the data such that portion of the feild is omitted so that I now have something like this:


    1234,1234,May 19 2003 12:00:00AM,,,Apr 22 2005 9:08:36AM,ABC01,1000,,,1,,


    or better yet is there a way for me to get that protion of the feild in an Oracle DB column defined as date-time ?
    Last edited by bluwulf; 10-03-05 at 14:40.

  2. #2
    Join Date
    Aug 2004
    Posts
    38
    Probably not the solution that you're looking for, but have you thought about running your output file through sed? Something like

    sed 's/:000AM/AM' myfile.bcp > newfile.bcp

    should give you the format that you want.

  3. #3
    Join Date
    Oct 2004
    Posts
    31
    I kno I could do that, but I definately don't want to do that, at this point that isn't even consideration.
    Last edited by bluwulf; 10-03-05 at 18:38.

  4. #4
    Join Date
    Aug 2004
    Posts
    38
    Quote Originally Posted by bluwulf
    I kno I could do that, but I definately don't want to do that, at this point that isn't even consideration.
    ??

    Not one for a pragmatic solution then

  5. #5
    Join Date
    Oct 2004
    Posts
    31
    It just isn't practical.
    Last edited by bluwulf; 10-05-05 at 10:58.

  6. #6
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    sed is a viable solution, anyway another option requires you to bcp out again.

    Setup a view on those tables that contain the timestamp with the timestamp column converted to standard Oracle format using the CONVERT function.

    Now bcp out of the view.

  7. #7
    Join Date
    Oct 2004
    Posts
    31
    Ok, ok...suppose I was using sed (or other unix shell command), what would be the easiest way to do the replace, considering that in :000AM the 000 can be any number from 000 to 999 likewise the AM can also be PM (and we are talking about serval millions of rows).

  8. #8
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1

    Delete portion of the date-time field using sed

    Code:
    # cat /tmp/f1.txt                        
    1234,1234,May 19 2003 12:00:00:000AM,,,Apr 22 2005 9:08:36:000AM,ABC01,1000,,,1,,
    1234,1234,May 19 2003 12:00:00:000PM,,,Apr 22 2005 9:08:36:036AM,ABC01,1000,,,1,,
    1234,1234,May 19 2003 12:00:00:000AM,,,Apr 22 2005 9:08:36:080PM,ABC01,1000,,,1,,
    # sed 's/:[0-9][0-9][0-9]//g' /tmp/f1.txt
    1234,1234,May 19 2003 12:00:00AM,,,Apr 22 2005 9:08:36AM,ABC01,1000,,,1,,
    1234,1234,May 19 2003 12:00:00PM,,,Apr 22 2005 9:08:36AM,ABC01,1000,,,1,,
    1234,1234,May 19 2003 12:00:00AM,,,Apr 22 2005 9:08:36PM,ABC01,1000,,,1,,
    # sed 's/:[0-9][0-9][0-9][AP]M//g' /tmp/f1.txt
    1234,1234,May 19 2003 12:00:00,,,Apr 22 2005 9:08:36,ABC01,1000,,,1,,
    1234,1234,May 19 2003 12:00:00,,,Apr 22 2005 9:08:36,ABC01,1000,,,1,,
    1234,1234,May 19 2003 12:00:00,,,Apr 22 2005 9:08:36,ABC01,1000,,,1,,
    Last edited by pdreyer; 10-11-05 at 03:54.

  9. #9
    Join Date
    Oct 2004
    Posts
    31
    Hey, thanks for that.

Posting Permissions

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