Re: oracle bulk loader

From: <campbell_r_at_sbcglobal.net>
Date: Tue, 11 Mar 2003 18:59:28 GMT
Message-ID: <kmqba.312$j43.102@newssvr16.news.prodigy.com>

You can specify SQL*Loader with positions in the files. I'm including a sample below:

load data

-- infile '/home/nappadm/data/eb_snap.dat'
-- badfile '/home/nappadm/data/eb_snap_bad.dat'
-- discardfile '/home/nappadm/data/eb_snap_dis.dat'

truncate
into table ub_snapshot
(

  END_USER_NUM                  position (1:8),
  FIRST_NAME                    position (9:28),
  LAST_NAME                     position (29:48),
  UX_MAIL_ID                    position (49:176),
  TELNET_NUM                    position (177:184),
  WK_STATUS                     position (185:192),
  rpt_mgr_emp_NUM               position (193:200),
  rpt_mgr_first_NAME            position (201:220),
  rpt_mgr_last_NAME             position (221:240),
  rpt_mgr_ux_mail_id            position (241:368),
  rpt_mgr_telnet_NUM            position (369:376),
  rpt_mgr_wk_status             position (377:384),
  bill_mgr_emp_NUM              position (385:392),
  bill_mgr_first_NAME           position (393:412),
  bill_mgr_last_NAME            position (413:432),
  bill_mgr_ux_mail_id           position (433:560),
  bill_mgr_telnet_NUM           position (561:568),
  bill_mgr_wk_status            position (569:576),
  ENTITY_CODE                   position (577:578),
  SUB_ENTITY_CODE               position (579:580),
  DEPARTMENT                    position (581:584),
  PRODUCT_LINE                  position (585:586),
  PRODUCT_TYPE                  position (587:588),
  SALES_FORCE                   position (589:590),
  SUB_PRODUCT_LINE              position (591:592),
  DISTRICT                      position (593:594),
  ACCOUNT                       position (595:598),
  LOCAL_SUBACCT                 position (599:600),
  IC_SUBACCT                    position (601:602),
  mru_CODE                      position (603:606),
  orgn_chrt_grp_CODE            position (607:611),
  geog_CODE                     position (612:615),
  geog_op_CODE                  position (616:618),
  bus_orgn_CODE                 position (619:620),
  country_CODE                  position (621:622),
  date_created                  position (623:647),
  date_updated                  position (648:672),
  NAME_OVR                      position (673:673),
  UX_MAIL_ID_OVR                position (674:674),
  SCS_OVR                       position (675:675),
  bill_mgr_emp_NUM_OVR          position (676:676),
  bill_mgr_NAME_OVR             position (677:677),
  bill_mgr_ux_mail_id_OVR       position (678:678),
  rpt_mgr_emp_NUM_OVR           position (679:679),
  rpt_mgr_NAME_OVR              position (680:680),
  rpt_mgr_ux_mail_id_OVR        position (681:681),
  contractor_flag               position (682:682),
  production_flag               position (683:683),
  data_source_CODE              position (684:691),
  TEL_COUNTRY_CODE              position (692:695),
  TEL_CITY_CODE                 position (696:700),
  TEL_NUM                       position (701:712),
  TEL_EXT_NUM                   position (713:717),
  rpt_mgr_TEL_COUNTRY_CODE      position (718:721),
  rpt_mgr_TEL_CITY_CODE         position (722:726),
  rpt_mgr_TEL_NUM               position (727:738),
  rpt_mgr_TEL_ext_NUM           position (739:743),
  bill_mgr_TEL_COUNTRY_CODE     position (744:747),
  bill_mgr_TEL_CITY_CODE        position (748:752),
  bill_mgr_TEL_NUM              position (753:764),
  bill_mgr_TEL_ext_NUM          position (765:769),
  login_name                    position (770:777),
  service_CODE                  position (778:781),
  audit_comment                 position (782:801)
--  eff_from_date   SYSDATE
--  eff_end_date  CONSTANT DATE('12-31-9999', "MM-DD-YYYY")
--  eff_end_date
--     "TO_DATE('31-Dec-9999', "DD-Mon-YYYY")"

)

Hope this helps.
Marsha Campbell
marsha_campbell_at_c4corp.com

"david nathan katcher" <dkatcher_at_students.uiuc.edu> wrote in message news:Pine.GSO.4.31.0303092238010.18241-100000_at_ux8.cso.uiuc.edu...
> I am trying to load a large amount of data into my database. The problem
> is that it is not formatted well. I ran a script to pull the data from the
> website and it is in a text file like this:
> Nov 22-W vs. Mt St Marys 84-60
> .....
> .....

>

> I want to read Nov 22 into one column, vs into another, Mt St Marys in
> another, 84 into another column, and 60 into one more. I am not sure how
> to do this other than by fields terminated by 'whatever'. Since all fields
> are terminated by different ways, is there anyway to do this?

>

> Thanks for the help.

>

> David Katcher

> Received on Tue Mar 11 2003 - 12:59:28 CST