Sybase - BCP - oracle.

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....??


LVL 1
vishali_vishuAsked:
Who is Participating?
 
Joe WoodhouseConnect With a Mentor Principal ConsultantCommented:
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
 
grant300Commented:
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
 
vishali_vishuAuthor Commented:
is it possible to do something at the format file level on a particular column ?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
mrjoltcolaCommented:
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
 
vishali_vishuAuthor Commented:
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
 
mrjoltcolaCommented:
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
 
mrjoltcolaCommented:
Note in Sybase its char() vs Oracle chr()
0
 
vishali_vishuAuthor Commented:
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
 
mrjoltcolaCommented:
I do not know BCP, so I will defer to grant300 on that part.
0
 
mrjoltcolaCommented:
@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
 
vishali_vishuAuthor Commented:
mrjoltcola: sorry for that..... how to split the points ?


0
 
mrjoltcolaCommented:
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
All Courses

From novice to tech pro — start learning today.