Importing CSV to Table with new line in a field using sql developer

Can you share the error message (all of it unless it's 60 pages)?  Good luck!

I'm assuming SQL Developer uses external tables or SQL loader to process CSV files, if so, you'll need to do some special handling for files containing fields with embedded carriage returns.

Here's an article by Thomas Kyte about it : https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2818047000346046084

As Paul says, SQL Loader and External Tables do not handle CSV data that contains newline characters within the data (unfortunately, but that's the way it is).

I've just had to deal with similar myself, and had to write my own loader process for it.

Did you preprocess the file, search and replace the CR for an alternative character, using regular expressions or similar? Then process afterwards with SQL Loader / External Tables?

Specifically, I would pre-process the data file. Replace the new line and/or carriage return with a suitably uncommon character, maybe "~". That means you would have to determine when a new line is embedded in a quote, but hopefully that's not too awful (e.g. it gets worse if you have to deal with escaped quotation marks too).

Then import that via sqlldr or an external table, and finally update the resulting data to replace the ~ with chr(13) and/or chr(10).

Scott

i wonder if you could do anything in excel itself to pre-process the data...i.e. create a macro or formula on the row and if a newline character is found in the cell, remove it or replace it with a space?  If this is an ongoing load it probably wouldn't be ideal but if it's a one time load it may be worth exploring...the trick would be the syntax to figuring out which newline character was present in the cell

Janel

That was one option, but in this case performance wasn't a concern (we were happy to leave it running for a couple of hours) so rather than mix database code with o/s commands etc. I just did the quick and dirty loading of the data file into a clob and then parsed straight through it keeping track of the double-quote depth, to know if I was inside the field or not when I encountered a newline.  Lots of inserts, slow process, but it got the job done as a one-off.

Maybe using the external tables preprocessor feature.

Should be not that hard considering the field containing new_line(s) is enclosed in double quotes.

Please don't ask me how as it's almost ten years I'm dealing with SQL & PL/SQL only.

How easy is giving advice when you don't have to do it.

Regards

Etbin

You masochist! Yeah, who cares if it's a one off.

I might write a pre processor utility at some point for embedded special chars. RFC published an Augmented Backus–Naur Form grammar for CSV, it's simple so ideal for a simple parser search and replacer. Post processing would be simple at querying time.

I have a CSV with ~200-500K records. Few of the Records  have newline inside a field which is enclosed in Double Quotes.

For Example

One record looks like this.

name,id,lname,"Comments

Comments cont",puid,date

 

Am trying to load it into a table in Oracle. when i load as CSV, am getting error and if i ignore and continue, the second part of the record is getting inserted as a new record.

 

If load from xls it works ok, but as i have 200K records xls breaks it to 65K. Is there a way to handle it? I am sure many might have seen this and addressed the same. Would appreciate any help.

 

I did some search and did not find anything as a solution though found few discussions on the thread.

I've discussed this issue several times in the past in the Sql Dev forum and each time the product manager basically says that is just the way it works. They don't consider it a bug.

See my extensive comments in this sql dev thread from March.

CSV file field is having multiple lines of data, while importing through SQL Developer its givving error that "Insert failed for row  14  Line contains invalid enclosed character data or delimiter at position 52."

See also that they no longer even want to respond to comments about the issue let alone fix it.

Not that it is any consolation but I have discussed this same issue with several of the major tool makers (e.g. Informatica) and even after several years they have still chosen NOT to do anything about it.

The consequence is that such tools will readily export data that they cannot then import again.

I consider that a bug.

It is easy to do the parsing properly. All you need is to recognize a SINGLE ESCAPE character and read the file by characters. Excel does this and some other third party tools do it.

As I say in my link comments you can NOT do it by trying to read 'line at a time' since, as you posted, whatever line terminator you choose could very well be in the middle of a data field.

The best, noninvasive, solution IMHO is to create a simple utility that preprocesses the files and produces files that don't have that embedded issue.

Agree with you rp, it's something that Oracle should consider including as it's a very common thing for CSV data to be an export of data from a spreadsheet, where it's very likely that users will have put newlines in the data of a single cell.

As you point out, things like Excel have no problem parsing and loading such data, and it's not rocket science to achieve it.  Just a simple switch in the Control File/External Table Definition could let us indicate that we want to include the additional processing required to check for newlines inside quoted (or whatever is specified by the optionally enclosed by directive) data.

For the time being though we al have to manually do this either via PL/SQL code (which is slow for this) or by Preprocessing the data with command line options (awk, sed and suchlike)