Home » RDBMS Server » Server Utilities » Using Sql Loader issue (sqlldr.exe, 10.2.0, Window)
Using Sql Loader issue [message #513696] Wed, 29 June 2011 00:24 Go to next message
rsager
Messages: 17
Registered: June 2011
Location: Sydney, Australia
Junior Member
Hi folks...

I have an issue where I am using Sql Loader to import fields from a csv file that is delimited by commas. There are a number of fields per line in the csv file, such as>
3500B9880611180257084XXX,99050096146,-30.162415,149.636484,3:34:02,4,13/04/2011,101,Widgen,9,brf71,5988
and the Oracle table is defined as
CREATE TABLE PTLIVE.SUBMODULES
( SUBMODULEID            NUMBER (20),
  SUBMODULETAGNUMBER     VARCHAR2(100),
  SUBMODULESERIALNUMBER  number(20,0),
  LATITUDE               NUMBER(20,10),
  LONGITUDE              NUMBER(20,10),
  GMT_TIME               VARCHAR2(10),
  TAGCOUNT               NUMBER,
  DATEPICKED             DATE, 
  GROWER                 VARCHAR2(10 ),
  FARM                   VARCHAR2(30 ),
  FIELD                  VARCHAR2(30 ),
  VARIETY                VARCHAR2(30 ),
  PICKER                 VARCHAR2(10 ),
  IMPORT_CSV_FILE_NAME   VARCHAR2(256 ),
  DATEIMPORTED           DATE  default SYSDATE
)

where the SUBMODULEID is completed by Oracle's nextval sequence,
the IMPORT_CSV_FILE_NAME is not loaded, and the DATEIMPORTED is defaults to sysdate when rows are inserted.

Now the control file that is input into SQl Loader looks like :
load data  
infile 	'J:\Grower\Round_Modules\Crop2011\Processed\Batch_2011Jun29_TEST.csv'  
BADFILE 'J:\Grower\Round_Modules\Crop2011\Logs\Batch_2011Jun29_TEST.bad'  
append into table  PTLIVE.SUBMODULES  
FIELDS TERMINATED BY ","  
( SUBMODULETAGNUMBER,     
  SUBMODULESERIALNUMBER,  
  LATITUDE,               
  LONGITUDE,              
  GMT_TIME,               
  TAGCOUNT,               
  DATEPICKED    "to_date(:DATEPICKED||:GMT_TIME, 'DD/MM/YYYYHH24:MI:SS')",  
  GROWER  	"to_char(:GROWER)||'/00' ",        
  FARM,                   
  FIELD,                  
  VARIETY,                
  PICKER                  
)

Question: how do I populate the Oracle column IMPORT_CSV_FILE_NAME (with the name of the csv infile) with a string = to "Batch_2011Jun29_TEST.csv"
when each field from the csv file corresponds to the SUBMODULES table, but the only column not populated is IMPORT_CSV_FILE_NAME???

Help
Cheers
Roger

[mod-edit: code tags added by bb; next time please add them yourself]

[Updated on: Wed, 29 June 2011 11:35] by Moderator

Report message to a moderator

Re: Using Sql Loader issue [message #513816 is a reply to message #513696] Wed, 29 June 2011 09:00 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm not sure that you can store filename itself using SQL*Loader. It should (probably) be done in several steps. You'd create a batch script whose input parameter is a file name. You'd then dynamically create a control file (because CSV file name is different every time). You'd load records and, once it is done, you'd call a SQL script that would update all records and set missing CSV file name.

Alternatively, create a CSV file so that it contains file name in every record - doing so, it would be just another column you'd have to load.

Report message to a moderator

Re: Using Sql Loader issue [message #513844 is a reply to message #513816] Wed, 29 June 2011 12:09 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
Senior Member
Your question is unclear. It appears that you already have the "Batch_2011Jun29_TEST.csv" in your control file as the infile. If you are just trying to figure out how to populate the import_csv_file_name with that value, then you can do so by making it the last field, populating it with a constant, as shown below.

load data
infile 'J:\Grower\Round_Modules\Crop2011\Processed\Batch_2011Jun29_TEST.csv'
BADFILE 'J:\Grower\Round_Modules\Crop2011\Logs\Batch_2011Jun29_TEST.bad'
append into table PTLIVE.SUBMODULES
FIELDS TERMINATED BY ","
( SUBMODULETAGNUMBER,
SUBMODULESERIALNUMBER,
LATITUDE,
LONGITUDE,
GMT_TIME,
TAGCOUNT,
DATEPICKED "to_date(:DATEPICKED||:GMT_TIME, 'DD/MM/YYYYHH24:MI:SS')",
GROWER "to_char(:GROWER)||'/00' ",
FARM,
FIELD,
VARIETY,
PICKER
IMPORT_CSV_FILE_NAME CONSTANT "Batch_2011Jun29_TEST.csv"
)

Otherwise, if the date is always the sysdate, you can dynamically generate the control file, using sysdate. If not, then you can read the files from the directory to get the file name, then use that to generate the control file dynamically.

Report message to a moderator

Re: Using Sql Loader issue [message #513877 is a reply to message #513696] Wed, 29 June 2011 17:32 Go to previous message
rsager
Messages: 17
Registered: June 2011
Location: Sydney, Australia
Junior Member
Hi Barbara....

Your suggestion was a cracker! I searched the SQL Loader
manual > download.oracle.com/docs/cd/B10501_01/server.920/a96652/part2.htm#436160
...and even created a SR with Oracle metalink support and got no joy with either. Sometimes the soultion is right in front of
you but I couldn't see it...your advice make the different. So now I can drop the SQL*Plus step (that only updates the
IMPORT_CSV_FILE_NAME column)
and this SQL*Plus step follows the SQL Loader step (to populate most columns in the Oracle table from the csv file).

My concern was the the SQL Loader step 1 and the SQL*Plus step 2 were not in the same Oracle transaction so there was
the possibility that some of the rows created by multip[le csv files processed at the same time could have the wrong
IMPORT_CSV_FILE_NAME value...but the following ensure that the correct constant is assigned...as in
load data
infile 'J:\Grower\Round_Modules\Crop2011\Processed\Batch_2011Jun29_TEST.csv'
BADFILE 'J:\Grower\Round_Modules\Crop2011\Logs\Batch_2011Jun29_TEST.bad'
append into table PTLIVE.SUBMODULES
FIELDS TERMINATED BY ","
( SUBMODULETAGNUMBER,
SUBMODULESERIALNUMBER,
LATITUDE,
LONGITUDE,
GMT_TIME,
TAGCOUNT,
DATEPICKED "to_date(:DATEPICKED||:GMT_TIME, 'DD/MM/YYYYHH24:MI:SS')",
GROWER "to_char(:GROWER)||'/00' ",
FARM,
FIELD,
VARIETY,
PICKER,
IMPORT_CSV_FILE_NAME CONSTANT "Batch_2011Jun29_TEST.csv"
)

Much appreciated your advice Barbara!

Cheers
Roger

Report message to a moderator

Previous Topic: Token longer than max allowable length of 258 chars
Next Topic: Trapping SQL Loader summary counts
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Dec 23 10:58:27 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.