Re: SQL*Loader line breaks in fields CSV file

Hi Nigel,
I ended up using Record Streams, Andrey did indeed give me *a* solution, full credit there. Especially as I couldn't find any reference to this in the Utilities SQL*Loader reference, maybe I didn't look closely enough.
Unfortunately, this is on Windows, but I have a full Cygwin installation on my desktop, and I used awk to add a terminator to those lines that weren't "broken". 

This worked well.

I'm aware of named pipes, but haven't used them on Windows/Cygwin yet.
One thing ISTR reading about is Data Pump not being able to handle named pipes. Which is a bit of a blow, I used to use it for exports and imports. 

Regards.

Nigel Thomas wrote:

Tony
I haven't actually worked out the solution (too idle), but if you end up on Unix you could use awk or similar to do the editing for you. You can also do this as part of a processing pipeline (so the edited load file never has to hit the disc).
If you haven't come across use of fifos / named pipes with sqlldr, the basics are described here: http://dylanwan.wordpress.com/2008/06/24/use-sqlloader-with-named-pipe/ (and in a zillion other places - this trick is decades old). Some unixes use* mknod -p* rather than *mkfifo*. One particularly useful application of the pipe is to allow you to load from a compressed file without materializing the uncompressed file - and then you just add your reformat command to the pipeline: 

mknod -p mypipe.dat
gunzip -c infile.gz | awk -f awkfile.awk > mypipe.dat &
sqlldr u/p control=x.ctl data=mypipe.dat &


Cheers
On 21 March 2010 09:14, Tony Sequeira <tony@xxxxxxxxxxxxxxx <mailto:tony@xxxxxxxxxxxxxxx>> wrote: 

    Hi Adar,

    Andrey was indeed on the right lines.  However, if you look back
    at the postings I mention:

    "I believe that I will have to manipulate the datafile..."

    "without manipulating the datafile."

    And indeed Adar solution worked, *BUT* I had to edit the relevant
    datafiles, I've been given them, I have no opportunity to amend
    the way they are created.

    Regards.


    Yechiel Adar wrote:

    Read Andrey lips. The link he gave you is right on the money.
    You are looking on the data with the wrong tool.
Since you produce somehow a csv file, you should be able to "select *,'|' from table;". The spooled output file is a long string of characters that contained /n in some places. 
    Sqlldr reads and understand this string according to the parameters.
If you use the "str '|\n'" then sqlldr will treat /n as regular character and use only |/n as end of record.
When you look at the file with a regular editor, the editor treats the /n as new line and present it to you in one way. 
    Sqlldr will treat it in a different way.

    Adar Yechiel
    Rechovot, Israel



    andrey khudyakov wrote:
http://www.orafaq.com/wiki/SQL*Loader_FAQ#How_does_one_load_records_with_multi-line_fields.3F 


    --
    http://www.freelists.org/webpage/oracle-l
-- S. Anthony Sequeira 
    ++
    Now I lay me down to study,
    I pray the Lord I won't go nutty.
    And if I fail to learn this junk,
    I pray the Lord that I won't flunk.
    But if I do, don't pity me at all,
    Just lay my bones in the study hall.
    Tell my teacher I've done my best,
    Then pile my books upon my chest.
    ++





--
S. Anthony Sequeira
++
Be sure to evaluate the bird-hand/bush ratio.
++

Other related posts: