File read from UNIX to Oracle

Shell Programming and Scripting
1

hi all,

I have flat file in unix. By using sql loader i need to import the data from the flat file. I have created a control file, table with the below data structure and pasted the sample input file. I am not sure whether it is a right way to do it. can anyone provide the thought on this.

$ cat sqlldr-fixed.ctl

load data infile '/unix/home/D080715.MMM'
into table testtable terminated by ""
 (LOCATION_id position(1:4), LOCATION_name position(5:54),address position(55:84),
  city position(85:114), state position(115:116), zip_code postion(117:121),
  zip_extension position(122:125), SEPARTOR2 position(126:126), country_code(127:129),
  SEPARTOR1 position(130:156), internal_contact position(157:186), LOCATION_contact position(187:216),
  contact_phone position(217:236), OCCURED_day position(237:237), mvmtSALES position(238:245)

Input Structure :
03 LOCATION_ID          CHAR(4),
03 LOCATION_NAME        CHAR(50),
03 ADDRESS           CHAR(30),
03 CITY              CHAR(30),
03 STATE             CHAR(2),
03 ZIP_CODE          CHAR(5),
03 ZIP_EXTENSION     CHAR(4),
03 TEMP2           CHAR(1),
03 COUNTRY_CODE      CHAR(3),
03 TEMP1           CHAR(27),
03 INTERNAL_CONTACT  CHAR(30),
03 LOCATION_CONTACT     CHAR(30),
03 CONTACT_PHONE     CHAR(20),
03 OCCURED_DAY       CHAR(1),
03 MVMTSALES          CHAR(9);

Input file View :

cat /unix/home/D080715.MMM | tail -5

YFG Yellow Front Grocery                              UPPER MAIN ST                 DAMARISCOTTA                  ME04543     Germany                       Satish Mohan                  Jeff                                              17Sun-6Sat
YLQ Young's Liquors                                   1432 E Mulberry St Unit A     FORT COLLINS                  CO80524                                   Andrea Biedron                                                                  11Mon-7Sun
YOK YOKES                                             3426 S UNIVERSITY             SPOKANE                       WA99206     USA                           Tiffani Caffrey               ED GOEBEL                     6099212292          33Wed-2Tue
YUN YOUNGS MARKET                                     P.O. Box 3167                 Visalia                       CA          USA                           Satish Mohan                  Ken Young                     6597339644          33Wed-2Tue
2

Did you solve this or was the thread tagged "solved" accidentally? If not, it would be interesting to know what the solution was, don't you think?

bakunin

3

It was not accidentally tagged.

Solution is :

By using the positional character numbers. i have loaded the records into staging table.

1 Like