Unanswered: Stripping CRLF chars while exporting into text
I periodically receive dumps in text format from the IT team at my office for doing analysis. I import these set of text files in Access and do further processing there. The original DB is maintained in Oracle 8i and the IT person here provide dumps to me from the backup servers as they are not given access to production server. He also uses TOAD for handling queries.
Now the problem is that a particular field generally contains some nonprintable characters, due to which when the data from Oracle is spooled into the text format, CRLF symbols are inserted and the row breaks into two, resulting in defective import in Access. Since the number of fields and rows are huge, it is not feasible that I do manual work to identify the broken rows and correct them.
So the question is, are there any technique/function in Oracle by way of which while exporting the data, all non printable characters are stripped off and I get clean data and secondly can data be exported in Access MDB format directly instead of the now 2 step process of first into text format and then import it into Access.
If any of these queries have a positive answer then I can advise my IT person accordingly and solve my headache.
I don't have Access around at the moment but it can certainly query Oracle over ODBC. Sounds like it would make more sense to pull the data directly into Access rather than using TOAD to make text files.
SELECT translate(text_field, chr(13)||chr(10)||chr(9), ' ')
to select the data in TOAD.
This would replace all CR/LF and TAB characters with a blank. You can add more whitespace characters to the list if needed.
Note that the replacement string contains a single space, otherwise the result of the translate function would be an empty string.