Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sybase - BCP - oracle.

Posted on 2009-05-11
12
Medium Priority
?
1,489 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 1500 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

722 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