Re: SQL Ldr Control File Question

Thomas Gaines (Thomas.Gaines_at_noaa.gov) wrote:
: Tim -

: A few thoughts come to mind:

: 1. Delimit the input file with double quotes instead of single quotes. I
: haven't tried this, but it should work.

: INFILE "C:\TIM'SF~1\ACCESS~1\SPACEB~1\NEW\ROOM_N~1.TXT"

: 2. Put the actual data in with the control file and specify:

: infile *

: 3. Map a new drive to your existing directory and refer to it:

: infile 'z:\room_n~1.txt'

: 4. Don't use a single quote in a directory name!

: Bye,
: TG

(two more _untested_ ideas)

5. put the file name as a parameter to the sqlldr command (other

    filenames can be specified this way, not sure about infile)

6. try using two quotes instead of one (that's how you escape a quote in

    oracle SQL, no idea about sqlldr) 'C:\TIM''SF\etc'

Assuming that 4 is out, personally, I like idea 3 best - it's a dos trick that I've found useful in many other situations (though I didn't think of it for this case). Use `subst' for local drives (also works on NT network drives). On networks you can do similar things but the details depend on the network.

: Tim Marshall wrote:

: > I've stumbled across a bit of a problem in that the control file I've
: > created for sqlldr80 has a single quote in it, ie:
: >
: > LOAD DATA
: > INFILE 'C:\TIM'SF~1\ACCESS~1\SPACEB~1\NEW\ROOM_N~1.TXT'
: > APPEND INTO TABLE TEMP_TIM_AREA FIELDS TERMINATED X '09'
: > (TMA_AREA, TMA_AREA_SQ_FT)
: >
: > You'll see that the INFILE text file specification has a main directory
: > with a ' in it.
: >
: > Can anyone suggest how best to deal with this, other than renaming the
: > main directory?
: >
: > Thanks very much in advance.
: > --
: > Tim - http://www.ucs.mun.ca/~tmarshal/
: > ^o<
: > /#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
: > /^^ "Want some?" - Ditto

--