Home » RDBMS Server » Server Utilities » Loading data from .dat file
Loading data from .dat file [message #283173] Mon, 26 November 2007 05:08 Go to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Hi,
I have to create a loader file. It should truncate and reload the table and should load data from a .dat file.
I have been using the below script to create control files.

LOAD DATA
TRUNCATE
INTO TABLE inventory_work
( qty POSITION(01:15) CHAR "(:qty/10)"
, branch_plant POSITION(16:27) CHAR "TRIM(:branch_plant)"
, category POSITION(68:73) CHAR "UPPER(:category)"
)

But now the requirement is to load data from .dat file.

Please advice as what changes I need to do so that it loads the data from .dat file.

Thanks,
Soni

[MERGED and RENAMED by LF]

[Updated on: Mon, 26 November 2007 06:08] by Moderator

Report message to a moderator

Re: Loading data from dat file [message #283180 is a reply to message #283173] Mon, 26 November 2007 05:40 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I have seen about 2.135 different data formats in files that are called "*.dat" in my career so far, I think.

So what sort of "*.dat" file are you talking about?
Re: Loading data from dat file [message #283185 is a reply to message #283173] Mon, 26 November 2007 05:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You need to change the parameters to fit the new .dat format.

Regards
Michel
Re: SQL LOADER [message #283189 is a reply to message #283173] Mon, 26 November 2007 05:55 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Where do you take input data from now? What is that '.dat' file? Something different from a usual '.txt' file? If not, just mention it in control file:
LOAD DATA

INFILE 'your_file.dat'

TRUNCATE
...

Re: SQL LOADER [message #284261 is a reply to message #283189] Thu, 29 November 2007 04:33 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Hi,
Thanks for your answer. I have been given a table layout as :

cre-table-cust-si_sop_fgitem.SOP
Column definitions for this table are as follows:
Item	Varchar2(30)	Sqlloader from Si_jde_raw_item.sku
Itemdescr	Varchar2(30)	Sqlloader from Si_jde_raw_item.itemdescr
Primfob	Varchar2(30)	Sqlloader from Si_jde_raw_item.primfob
Size	Varchar2(30)	Sqlloader from Si_jde_raw_item.finwidthshort
Now I have to use the above table layout to create loader file. The script should TRUNCATE and reload the table and should load the data from the file named:
imp-si-sop-fgitem.dat.

So the input data comes from the dat file 'imp-si-sop-fgitem.dat'. This is a flat file.

Please help me with syntax .... I have written something below...pls suggest me if I am wrong.

LOAD DATA
  INFILE imp-si-sop-fgitem.dat
  TRUNCATE   
  INTO TABLE si_jde_raw_item                     
  ( Item       POSITION(01:30)   CHAR "TRIM(:item))"             
   ,Itemdescr      POSITION(31:60) CHAR "TRIM(:Itemdescr)"  
  , Primfob        POSITION(61:90) CHAR "TRIM(:Primfob)"
  ,Size            POSITION(91:120) CHAR "TRIM(:Size)" 
)




Thanks,
Soni
Re: Loading data from .dat file [message #284305 is a reply to message #283173] Thu, 29 November 2007 06:04 Go to previous message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

http://www.orafaq.com/forum/t/93028/115564/

Regards,
Kiran.
Previous Topic: Update Oracle table data from data in Excel file
Next Topic: Data not getting inserted to specified tablespace
Goto Forum:
  


Current Time: Mon Dec 05 09:18:38 CST 2016

Total time taken to generate the page: 0.13995 seconds