Issue in SQL Loader scripts

Shell Programming and Scripting
1

Hi,

I'm planning to load the data from FLAT files into tables.

Source file: more input.txt

LRNO|Bale|Horsepower|NumberOfBarges|BollardPull|NumberOfCars|GasCapacity|GrainCapacity|IndicatedHorsepower|LiquidCapacity|
NumberOfPassengers|NumberRefrigeratedContainers|NumberOfTEU|NumberOfTrucks|NumberOfVehicles|NumberOfRailWagons
5002170|15884|11001||0||0|17647|11001|0|4|0|40|||
5004099|19022|18151||0||0|19385|18151|0|0|0|0|||
5004245|19022|18151||0||0|19385|18151|0|0|0|0|||

But when i'm opening same file through VI editor it showing new line feed character as well.

LRNO|Bale|Horsepower|NumberOfBarges|BollardPull|NumberOfCars|GasCapacity|GrainCapacity|IndicatedHorsepower|LiquidCapacity|
NumberOfPassengers|NumberRefrigeratedContainers|NumberOfTEU|NumberOfTrucks|NumberOfVehicles|NumberOfRailWagons^M
5002170|15884|11001||0||0|17647|11001|0|4|0|40|||^M
5004099|19022|18151||0||0|19385|18151|0|0|0|0|||^M
5004245|19022|18151||0||0|19385|18151|0|0|0|0|||^M

Hence I'm getting error as

Record 2: Rejected - Error on table DFN_IHS_CAPACITIES, column NUMBEROFRAILWAGONS.
ORA-01722: invalid number

Even, SQL loader script as also follwos.

load data
infile '../in/ input.txt'
truncate
into table dfn_IHS_Capacities
fields terminated by '|' --optionally enclosed by '"'
trailing nullcols
(
LRNO "ltrim(rtrim(:LRNO))",
Bale "ltrim(rtrim(:Bale))",
Horsepower "ltrim(rtrim(:Horsepower))",
NumberOfBarges "ltrim(rtrim(:NumberOfBarges))",
BollardPull "ltrim(rtrim(:BollardPull))",
NumberOfCars "ltrim(rtrim(:NumberOfCars))",
GasCapacity "ltrim(rtrim(:GasCapacity))",
GrainCapacity "ltrim(rtrim(:GrainCapacity))",
IndicatedHorsepower "ltrim(rtrim(:IndicatedHorsepower))",
LiquidCapacity "ltrim(rtrim(:LiquidCapacity))",
NumberOfPassengers "ltrim(rtrim(:NumberOfPassengers))",
NumberRefrigeratedContainers "ltrim(rtrim(:NumberRefrigeratedContainers))",
NumberOfTEU "ltrim(rtrim(:NumberOfTEU))",
NumberOfTrucks "ltrim(rtrim(:NumberOfTrucks))",
NumberOfVehicles "ltrim(rtrim(:NumberOfVehicles))",
NumberOfRailWagons "ltrim(rtrim(:NumberOfRailWagons))",
SQLLoaddate SYSDATE)
[/CODE]
Could you please advise me how to remove that new feed line character ?

Please let me know if any information needed from My side.

2

To get rid of the DOS carriage return:

perl -pi -e 's/\r\n/\n/g' input.txt
3

or convert the file dos2unix..it will remove the control+m characters.....

4

Is there any possible handle the things through SQL loader scripts ?

5

yes,it is possible..

6

BMk,Can you please let me know whatZ command can add in this control file ?

[

load data
infile '../in/ input.txt'
truncate
into table dfn_IHS_Capacities
fields terminated by '|' --optionally enclosed by '"'
trailing nullcols
(
LRNO                            "ltrim(rtrim(:LRNO))",
Bale                            "ltrim(rtrim(:Bale))",
Horsepower                      "ltrim(rtrim(:Horsepower))",
NumberOfBarges                  "ltrim(rtrim(:NumberOfBarges))",
BollardPull                     "ltrim(rtrim(:BollardPull))",
NumberOfCars                    "ltrim(rtrim(:NumberOfCars))",
GasCapacity                     "ltrim(rtrim(:GasCapacity))",
GrainCapacity                   "ltrim(rtrim(:GrainCapacity))",
IndicatedHorsepower             "ltrim(rtrim(:IndicatedHorsepower))",
LiquidCapacity                  "ltrim(rtrim(:LiquidCapacity))",
NumberOfPassengers              "ltrim(rtrim(:NumberOfPassengers))",
NumberRefrigeratedContainers    "ltrim(rtrim(:NumberRefrigeratedContainers))",
NumberOfTEU                     "ltrim(rtrim(:NumberOfTEU))",
NumberOfTrucks                  "ltrim(rtrim(:NumberOfTrucks))",
NumberOfVehicles                "ltrim(rtrim(:NumberOfVehicles))",
NumberOfRailWagons              "ltrim(rtrim(:NumberOfRailWagons))",
SQLLoaddate                     SYSDATE)

](UNIX and Linux Forums - Smilies)

7

Go to this site,Try and let me know
OraFAQ Forum: Server Utilities � sql loader data isse