vishali_vishu
asked on
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....??
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....??
ASKER
is it possible to do something at the format file level on a particular column ?
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
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
ASKER
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)...?
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)...?
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));
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));
Note in Sybase its char() vs Oracle chr()
ASKER
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.
I can't update the tables.
I do not know BCP, so I will defer to grant300 on that part.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@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.
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.
ASKER
mrjoltcola: sorry for that..... how to split the points ?
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.
Regards,
Bill