Re: SQL*Loader line breaks in fields CSV file
- From: Nigel Thomas <nigel.cl.thomas@xxxxxxxxxxxxxx>
- To: tony@xxxxxxxxxxxxxxx, Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 21 Mar 2010 11:34:46 +0000
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> 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
>
>
> --//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.
> ++
>
>
Other related posts:
