How to handle new line in field data, while importing .csv file data into oracle table using SQL Developer tool.?

Does using any other of the Line Terminator variations available in the Import Wizard or Import Preferences help?

For example, if you export using CR or {EOL} as the line terminator, then importing using the same should work.

did you test that Gary? i'm not sure we support it as CSV is assumed to NOT have any CR/LFs in the data

I tried both {EOL} and CR line delimited export cases, using HR.DEPARTMENTS, then importing into a DEPT_CLONE table created via...

create table dept_clone as select * from department where 1 != 1

Next, I edited the exports to insert a LF between the 'n' and 'i' in Administration (in hex, that's c/ni/x'6E0A69'), so technically I only tested for a single line. Both imports actually do work, although there seems to be a bug with the {EOL} delimiter case in properly getting the wizard to skip the heading line:

On the other hand, if we try to edit the HR.DEPARTMENTS data in the object viewer rather than using a special external editor that does precisely what we want (including telling it to use CR as the EOL character when we save the file), then the import will fail to import lines with the embedded LF character.  The wizard warns us, of course...

whereas in the externally edited case we get...

So I would never expect anyone to use an external editor to achieve this result, but if the OP's file is created with the embedded LF and the EOL (end-of-line) character is something else, then it should work.

How to handle new line in field data, while importing .csv file data into oracle table using SQL Developer tool.?

 

You won't be able to import data using ANY record delimiter if that record delimiter exists in any of the data values.

Since Sql Dev (and most other tools) don't have a generic escape mechanism they have no way of distinguishing between a record delimiter data value in a column and a record delimiter that denotes 'end of record'.

The 'standard' way to handle the general use case that allows for ANY delimiter values is to designate a desired 'escape' character.

An occurrence of a single instance of the character indicates an escape and that the next character should be considered DATA no matter what it is.

If ANY character (including the escape character, field delimiter, record delimiter) needs to be included as data then you just precede it with an escape character.

Unfortunately there are few tools that do such escaping.

Changing the scenario described in my prior post to export with a CRLF line terminator, it is true that the subsequent import fails. At least it fails for me using 17.3.1 -- you never said which release you use.  Of course, in theory it should work but, as Jeff noted, this is not something he expected to work.

As I noted in my prior post for the case of LF characters embedded in the data, there is every indication an EOL (end-of-line) character of CR will work.

Is it possible for you to change the source system export to use CR as the EOL instead of CRLF?   If not, if may be possible to find a converter program to rewrite the file, changing all CRLF EOLs to CRs.

There is a 16-bit program CRLF in PBATS32 ( http://www.horstmuc.de/horst.htm ) that will do this, but you will need a much older Windows version to run it (probably Win XP or earlier).  Maybe a combination of dos2unix > unix2mac utilities ( https://waterlan.home.xs4all.nl/dos2unix.html  ) could be of help (since pre-Mac OS X used CR as line terminator), but I am not certain.

Best wishes,

Gary

SQL Developer Team

you could try using SQL Dev to do the import scenario, but instead use the INSERT SCRIPT option.

then take the insert scripts we've created, and try to fix the entries where there's linefeeds in the strings. You might be able to do this with some fancy regex search/replace in your editor.

Or, go back to the source and ask them to unload the data into another format.

Also, this is NOT a new problem in the world

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2818047000346046084

It'd be better to go back to the source and off-load it to a different format.

What's the ultimate goal? We can provide better advice knowing exactly what your scenario is.

i am using Version 4.1.1.19. i got files from client, so they can't change the file format again. i have to do this with this file. can i change change any encoding option? or can i save this file in any other format and then import?

Did you READ what I said before?

You won't be able to import data using ANY record delimiter if that record delimiter exists in any of the data values.

That is the SAME statement that Tom gave in the blog whose link you were given.

I tried to explain the reason for that in my first reply.

You only have TWO CHOICES:

1. use a file uses the 'escape' methodology I described earlier - and find/write a tool that can process that format.

2. modify (better - have the source modify) the file to use a record delimiter that does NOT exist in the data.

If this is just a 'once in a while' problem and there are few records with embedded delimiters then you could just:

1. manually manipulate the file to change the embedded values to 'XXXaaaYYY' or something that doesn't appear in the data,

2. load the data

3. manually (with a query) convert the 'XXXaaaYYY' values back to the delimiter value.

It'd be better to go back to the source and off-load it to a different format.

+1

This is one of the FEW 'holes' in delimited file functionality that none of the big vendors (e.g. Informatica) has even been willing to address - though I've tried several times over the years.

Sql Dev prime directive isn't really helping people get data OUT of the database. And it's not like your team doesn't have BETTER things to do.

But one possibility would be to add an option to have the tool check each string being output for embedded delimiters and:

1. produce an error list/report indicating the row number and an 'embedded delimiter' message

2. put problem rows in an 'exception' file

That would at least alert the user that the file has/had issues.

What I would PERSONALLY like to see is a 'hook' I (others) could use to grab the records (record level - not just a stream) BEFORE sql dev writes them to a file. Then we could do that, and other, checking ourselves and even redirect the output anywhere we wanted.

There are a lot of places I think hooks would be useful but that's a different topic altogether.

I asked my friends over on the Ask Tom team for ideas, and they had a good one. Give this a try 

https://connor-mcdonald.com/2017/11/01/parsing-freeform-data-in-flat-files/amp/