We are using DataSync to load data into our Oracle database from a source .csv file. This file comes from a third party, so we have very limited control over it.
There are 3 records in that .csv file that contain a backslash (\) character as the last character in a field. This is causing the subsequent character in the .csv file, a double-quote, to be ignored, which screws up every record afterwards, or at least until we hit another backslash.
All rows of the .csv file prior to the first backslash are loaded correctly into the database.
All rows between the first and second backslashes are not loaded into the database.
All rows between the second and third backslashes are loaded correctly into the database.
All rows after the third backslash are not loaded into the database.
Question: How can we instruct DataSync to ignore those backslashes?
I have tried placing a transformation into the processing stream, using the REPLACE function, but by the time Oracle can try to apply the REPLACE function, it already has a messed up record. In other words, the REPLACE occurs in Oracle (which makes sense), so Oracle never sees the backslash - it isn't presented to Oracle at all, because it has already been treated as an escape character.
Any and all ideas are welcome.