sql load dbf files

From: <ssharma_at_clearnet.com>
Date: 1998/03/25
Message-ID: <6fbdi2$gpn$1_at_nnrp1.dejanews.com>#1/1

Hello
I searched for an answer to this a while ago, and am now posting so anyone searching for it can find it.

The question was about loading dbf files with sql loader. the record length for my files database was 146, here is the control file:

LOAD DATA
INFILE 'F:\toll.dbf' "db3 146"
append
INTO TABLE icsadm.ics_call_detail
(ACCOUNT_ID POSITION(2:11) CHAR,

  BTN           POSITION(12:21) CHAR,
  CONN_DATE     POSITION(22:33) DATE "YYYYMMDDHH24MI",
  ORIG_CITY     POSITION(34:43) CHAR,
  ORIG_PROV     POSITION(44:45) CHAR,
  ORIG_NO       POSITION(46:60) CHAR,
  TERM_CITY     POSITION(61:70) CHAR,
  TERM_PROV     POSITION(71:72) CHAR NULLIF TERM_PROV=BLANKS,
  TERM_NO       POSITION(73:87) CHAR,
  SP_NO         POSITION(85:99) CHAR,
  MSG_CODE      POSITION(100:102) CHAR,
  CUO_CODE      POSITION(105:105) CHAR,
  DURATION      POSITION(106:111) FLOAT EXTERNAL,
  CHARGE        POSITION(112:119) FLOAT EXTERNAL,
  DISCOUNT      POSITION(120:127) FLOAT EXTERNAL,
  AMOUNT        POSITION(128:135) FLOAT EXTERNAL,
  PLAN          POSITION(136:138) CHAR NULLIF PLAN=BLANKS,
  ABC_CODE      POSITION(139:142) CHAR NULLIF ABC_CODE=BLANKS,
  USER_1        POSITION(143:147) CHAR NULLIF USER_1=BLANKS,

  PROCESS_DATE SYSDAT) THIS WILL NOT WORK ON unix!!!!!!!!!!! unix sql loader apparently is not compiled to recognize the db3 option. this worked on NT, and when I went to put it into our production environment it crapped. I had to write a little C program to remove the dbf header stuff and call sqlldr on that (search for brad eacker's dbfutils for a good dbf - handling library) Under unix this uncrapped file loads with:

OPTIONS (rows=256, errors=1000000)
LOAD DATA
INFILE '/tmp/uncrapped.dbf' "fixed 146"
append
INTO TABLE icsadm.ics_call_detail
(ACCOUNT_ID POSITION(2:11) CHAR,

  BTN           POSITION(12:21) CHAR,
  CONN_DATE     POSITION(22:33) DATE "YYYYMMDDHH24MI",
  ORIG_CITY     POSITION(34:43) CHAR,
  ORIG_PROV     POSITION(44:45) CHAR,
  ORIG_NO       POSITION(46:60) CHAR,
  TERM_CITY     POSITION(61:70) CHAR,
  TERM_PROV     POSITION(71:72) CHAR NULLIF TERM_PROV=BLANKS,
  TERM_NO       POSITION(73:87) CHAR,
  SP_NO         POSITION(85:99) CHAR,
  MSG_CODE      POSITION(100:102) CHAR,
  CUO_CODE      POSITION(105:105) CHAR,
  DURATION      POSITION(106:111) FLOAT EXTERNAL,
  CHARGE        POSITION(112:119) FLOAT EXTERNAL,
  DISCOUNT      POSITION(120:127) FLOAT EXTERNAL,
  AMOUNT        POSITION(128:135) FLOAT EXTERNAL,
  PLAN          POSITION(136:138) CHAR NULLIF PLAN=BLANKS,
  ABC_CODE      POSITION(139:142) CHAR NULLIF ABC_CODE=BLANKS,
  USER_1        POSITION(143:147) CHAR NULLIF USER_1=BLANKS,

  PROCESS_DATE SYSDATE ) search strings: dbf sqlloader sqlldr sql loader foxpro dbase

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Wed Mar 25 1998 - 00:00:00 CET