Solved

Sybase - BCP - oracle.

Posted on 2009-05-11
12
1,470 Views
Last Modified: 2012-05-06
I am extracting data from sybase using bcp tool ( building pipe delimited data file ) & there by loading into oracle database.

Issue: some fields are having newline characters and it is making the load in correct.

Is there a way to remove the newline character while pulling the data through BCP.....can anything be done at the format file....??


0
Comment
Question by:vishali_vishu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 19

Expert Comment

by:grant300
ID: 24354358
The way around this is to choose a different Line Terminator character on your BCP out and SQL*Loader in.  If necessary, choose a non-printable character that both utilities can handle.  That way you preserve the integrity of the data.

Regards,
Bill
0
 
LVL 1

Author Comment

by:vishali_vishu
ID: 24354684
is it possible to do something at the format file level on a particular column ?
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24355048
I agree with grant300's suggestion, a couple of comments:

1st approach, as grant300 suggests, however, I don't know BCP so he must help here with the Sybase part.
Make sure the text column is enclosed by quotes, then you can also use the stream format in sql loader, as grant suggests, using a combination of characters as the line terminator.

This uses |\n as terminator in the SQL Loader control file and use stream format.

LOAD DATA
INFILE 'foo.dat' "str '|\n'"
TRUNCATE INTO TABLE foo
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
... your field defs go here
)


2nd approach:
Or.. run a SQL query on sybase replacing all occurences of \n with something like "<SLASH_N>"
Extract with BCP
Load normally with SQL Loader
Run Oracle update, replacing <SLASH_N> with '\n' again

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:vishali_vishu
ID: 24355147
i thought of doing like this....


instead of \n as line terminator...i will be using <eol>.

Now i get the data file with single line ( line breaks only when there is a  \n in column values )...

Now use sed to replace \n with \t (tab).....

and use sed to replace <eol> with \n....

now i will get the data file with \n as the line terminator....


-----------------------------------------

but my question is ....... can we get it done at the format file level ....

something like.... replace ( newline with space or tab on a particular column)...?

0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24355232
Try this:

In Sybase:

update tbl set text_col = replace(text_col, char(10), '<eol>');

In Oracle:

update tbl set text_col = replace(text_col, '<eol>', chr(10));
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24355241
Note in Sybase its char() vs Oracle chr()
0
 
LVL 1

Author Comment

by:vishali_vishu
ID: 24355516
i just have read access to sybase.... and i am supposed to use BCP to pull the data and load to oracle...

I can't update the tables.
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24355573
I do not know BCP, so I will defer to grant300 on that part.
0
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 500 total points
ID: 24359738
To answer OP's question:

You can't use a BCP format file to do any updates to the data. The format file specifies the format and says nothing about the content (other than its datatype).

You could however create a view on the underlying data that does some transformations, and the BCP out of the view.
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24367438
@vishali_vishu

A more equitable point split would probably have been to include grant300's initial advice that led to the final solution, so I feel like accepting the comment that you selected is not a proper closure for this question. When closing a question, please consider the comments in the thread that actually helped you with the solution, not just the final one. Thanks.
0
 
LVL 1

Author Comment

by:vishali_vishu
ID: 24369247
mrjoltcola: sorry for that..... how to split the points ?


0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24369312
Next time you close a question, look for a button that says "Accept multiple solutions" and it will allow you to select the ones you want.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question