OraFAQ Forum: Server Utilities » Loading data from .dat file

Home » RDBMS Server » Server Utilities » Loading data from .dat file

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Loading data from .dat file [message #283173]

Mon, 26 November 2007 05:08

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

ThomasG
Messages: 3211
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?

Report message to a moderator

Re: Loading data from dat file [message #283185 is a reply to message #283173]

Mon, 26 November 2007 05:51

Michel Cadot
Messages: 68665
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

Report message to a moderator

Re: SQL LOADER [message #283189 is a reply to message #283173]

Mon, 26 November 2007 05:55

Littlefoot
Messages: 21811
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
...

Report message to a moderator

Re: SQL LOADER [message #284261 is a reply to message #283189]

Thu, 29 November 2007 04:33

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

Report message to a moderator

Re: Loading data from .dat file [message #284305 is a reply to message #283173]

Thu, 29 November 2007 06:04

kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India

Senior Member

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

Regards,
Kiran.

Report message to a moderator

Previous Topic:	Update Oracle table data from data in Excel file
Next Topic:	Data not getting inserted to specified tablespace

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 29 05:31:08 CDT 2024