You are here:

Oracle/IMPORT TXT FILE DATA INTO ORACLE

Advertisement


Question
I have the following text file structure which I needed to post back to a oracle table. How should I do this on daily basis?

Please help ON HOW to import the following text file into a oracle table.

Y/f
Ranadeep


SITE: 01 : aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa          RUN DATE: 01/02/2013   05:08  PAGE:   59
REQUESTOR: TUSHAR          REQ DATE: 31/01/2013
REPORT   : GLA211          GENERAL LEDGER TRIAL BALANCE - CURRENT BALANCE
         ENTITY  - 00105 AAAAAAAAAAAAA
         CURRENCY - INR  INDIAN RUPEE
         UNIT    - 00001 ADMINISTRATION UNIT
         FOR YEAR : 2012, PERIOD : 10

G.L. CODE     DESCRIPTION      BALANCE FORWARD          DEBIT          CREDIT          NET CHANGE      ENDING BALANCE

1001070601  DR BAL IN CA-INDIVIDUAL
         477400.00          477400.00-          .00
1001070602  CA-FESTIVAL ADVANCE-IND
         16000.00          2000.00-          2000.00-          14000.00
1001505002  CA-FESTIVAL ADVANCE-OTH
         88000.00          11000.00-          11000.00-          77000.00
1002030601  CASH CREDIT-STAFF
         81971.00          2479.00          10000.00-          7521.00-          74450.00
1002030699  INT RBLE-CC-STAFF
         1320.00          1402.00          1320.00-          82.00          1402.00
1002070601  CASH CREDIT-INDIVIDUAL
         64321784.38         1036941.00          69700.00-          967241.00         65289025.38
1002070699  INT RBLE-CC-INDIVIDUAL
         2749720.00         2474091.00          3073844.00-          599753.00-         2149967.00
1002500701  CASH CREDIT-SOCIETY
         1408561.00          105011.00          675261.00-          570250.00-          838311.00
1002500799  INT RBLE-CC-SOCIETY
         97748.00          104756.00          97748.00-          7008.00          104756.00
1003030601  OD-STAFF
         441581.00          3375.00          4073.00-          698.00-          440883.00
1003030699  INT RBLE-OD-STAFF
         276.00          1890.00          1896.00-          6.00-          270.00
1003070601  OD-INDIVIDUAL
         1395505.18          1395505.18
1003070602  OD-ADV AGST TDR-IND
         13704.00          118.00          118.00          13822.00
1003070603  OD-ADV AGST GOVT SEC
         123091.00          21119.00          5000.00-          16119.00          139210.00
1003070697  INT RB-OD-ADV AGST GOVT SEC
         1088.00          1119.00          1088.00-          31.00          1119.00
1003070698  INT RB-OD-ADV AGST TDR-IND
         135.00          118.00          135.00-          17.00-          118.00
1003070699  INT RBLE-OD-INDIVIDUAL
         165321.00          180729.00          165321.00-          15408.00          180729.00
1026505002  BANK GUARANTEE ISSUED ACCOUNT
         82945.00-          82945.00-
1026505005  CHEQS&BILLS FR COLLECTIN-CONTR
         14834559.00          14834559.00
1106505001  CORE INTERBRANCH AC
         22846432.24-          850.00          850.00-          22846432.24-
1106505002  BRANCH CLEARING GENERAL AC
         32123754.29          32123754.29
1111111111  SUSPENSE ACCOUNT
         90693612.51         90693612.51-          .00
1111505011  BRANCH MIGRATION SUSPENSE AC
         8701686.18          8701686.18
1200030601  LOAN AGST TD-STAFF
         1473498.00          519867.00          104515.00-          415352.00          1888850.00
1200030699  INT RBLE-LOAN AGST TD-STAFF
         67584.03          80604.63          67584.03-          13020.60          80604.63






SITE: 01 : aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa          RUN DATE: 01/02/2013   05:08  PAGE:   59
REQUESTOR: TUSHAR          REQ DATE: 31/01/2013
REPORT   : GLA211          GENERAL LEDGER TRIAL BALANCE - CURRENT BALANCE
         ENTITY  - 00105 AAAAAAAAAAAAA
         CURRENCY - INR  INDIAN RUPEE
         UNIT    - 00001 ADMINISTRATION UNIT
         FOR YEAR : 2012, PERIOD : 10

G.L. CODE     DESCRIPTION      BALANCE FORWARD          DEBIT          CREDIT          NET CHANGE      ENDING BALANCE

1200070601  LOAN AGST TD-INDIVIDUAL
         570582.00          1604.00          13705.00-          12101.00-          558481.00
1200070699  INT RBLE-LOAN AGST TD-IND
         51866.44          55437.91          51866.44-          3571.47          55437.91
1200500001  LOAN AGST TD-BANK
         1201411.00          716719.00          789062.00-          72343.00-         1129068.00
1200500099  INT RBLE-LOAN AGST TD-BANK
         31746.82          28094.49          31746.82-          3652.33-          28094.49
1200505001  LOAN AGST TD-OTHERS
         954828.00          954828.00
1200505099  INT RBLE-LOAN AGST TD-OTHERS
         186479.48          194307.29          186479.48-          7827.81          194307.29
1201505001  CONSUMER  LOAN-OTHERS
         15222.00          15222.00
1201505099  INT RBLE-CONSUMER LN-OTHERS
         4689.46          4857.48          4689.46-          168.02          4857.48
1203030601  HOUSING LOAN-STAFF
         .00
1203030699  INT RBLE-HOUSING LOAN-STAFF
         .00
1203070601  HOUSING LOAN-INDIVIDUAL
         25104322.00         2022602.00          319286.00-         1703316.00         26807638.00
1203070699  INT RBLE-HOUSING LOAN-IND
         252679.27          263236.14          252679.27-          10556.87          263236.14
1203500001  HOUSING LOAN-BANK
         1401188.00          17399.00          60487.00-          43088.00-         1358100.00
1203500099  INT RBLE-HOUSING LOAN-BANK
         12203.12          8405.01          12203.12-          3798.11-          8405.01
1203505001  HOUSING LOAN-OTHERS
         33353944.00          324443.00          544494.00-          220051.00-        33133893.00
1203505002  MT HOUSE REPAIR LN-OTH
         347555.00          3661.00          9785.00-          6124.00-          341431.00
1203505098  INT RB-MT HOUSE REPAIR LN-OTH
         2488.89          2757.20          2488.89-          268.31          2757.20
1203505099  INT RBLE-HOUSING LOAN-OTHERS
         592841.57          552220.18          592841.57-          40621.39-          552220.18
1204505001  CASH-IN-HAND
         1615807.27       230582582.96        225728991.96-         4853591.00          6469398.27
1206070601  PERONAL LOAN-INDIVIDUAL
         8631237.00          748014.00          447378.00-          300636.00          8931873.00
1206070699  INT RBLE-PER LOAN-INDIVIDUAL
         107704.10          114282.42          107704.10-          6578.32          114282.42
1206505001  PERONAL LOAN-OTHERS
         9045497.00          118140.00          484691.00-          366551.00-         8678946.00
1206505099  INT RBLE-PER LOAN-OTHERS
         924382.93          912495.87          924382.93-          11887.06-          912495.87
1207070602  MT NON-FARM ISB LOAN(D)
         .00
1207070698  INT PB-MT NON-FARM ISB LOAN(D)
         .00




         7022.67          10156.70          7022.67-          3134.03          10156.70
1303505001  LON AGAINST NSC/KVP/LIP-OTHERS
         832576.00          16088.00          106088.00-          90000.00-          742576.00
1303505099  INT RBL-LN AGT NSC/KVP/LIP-OTH
         192638.52          184072.02          192638.52-          8566.50-          184072.02
1305030601  GENERAL TERM LOANS-STAFF
         1041464.00          11534.00          22415.00-          10881.00-         1030583.00
1305030699  INT RBLE-GENERAL TL-STAFF
         9087.78          8301.12          9087.78-          786.66-          8301.12
1305070601  GENERAL TERM LOANS-INDIVIDUAL
         2341260.00          19124.00          133940.00-          114816.00-         2226444.00
1305070602  MT N-COM TRANS LN-GN(CUR)-IND
         .00
1305070698  INT RB-MT N-COM TRANS LN-IND
         .00
1305070699  INT RBLE-GENERAL TERM LOAN-IND
         46452.06          50666.09          46452.06-          4214.03          50666.09
1305500001  GENERAL TERM LOANS-BANK
         19613910.00         4675237.00          1414505.00-         3260732.00         22874642.00
1305500003  MT (ECCS) LOAN(CUR)-BANK
         .00
1305500097  INT RB-MT (ECCS) LOAN(CUR)-BNK
         125053.33-          125053.33-
1305500099  INT RBLE-GENERAL TL-BNK
         186902.40          213599.67          186902.40-          26697.27          213599.67
1305505001  GENERAL TERM LOANS-OTHERS
         96645.00          979.00          2213.00-          1234.00-          95411.00
1305505003  MT (ECCS) LOAN(CUR)-OTH
         .00
1305505004  MT COM TRANS LN-IND(CUR)-OTH
         .00
1305505096  INR RB-MT COM TRN LN-CUR-OTH
         .00
1305505097  INT RB-MT (ECCS) LOAN(CUR)-OTH
         .00
1305505099  INT RBLE-GENERAL TL-OTHERS
         33.76          99.99          33.76-          66.23          99.99
1347505001  MT NnFrm ISB Ln-CuOTH
         13386278.00          134854.00          590768.00-          455914.00-        12930364.00
1347505099  Int Rb MT NnFrm ISB Ln-Cu-OTH
         289947.36          295503.56          289947.36-          5556.20          295503.56
1362505001  MT Cm Trn Ln-IndCur-OTH
         1316022.00          3324.00          18272.00-          14948.00-         1301074.00






SITE: 01 : aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa          RUN DATE: 01/02/2013   05:08  PAGE:   59
REQUESTOR: TUSHAR          REQ DATE: 31/01/2013
REPORT   : GLA211          GENERAL LEDGER TRIAL BALANCE - CURRENT BALANCE
         ENTITY  - 00105 AAAAAAAAAAAAA
         CURRENCY - INR  INDIAN RUPEE
         UNIT    - 00001 ADMINISTRATION UNIT
         FOR YEAR : 2012, PERIOD : 10

G.L. CODE     DESCRIPTION      BALANCE FORWARD          DEBIT          CREDIT          NET CHANGE      ENDING BALANCE

1362505099  Int Rb MT Cm Trn Ln-IndCur-OTH
         48718.59          59610.22          48718.59-          10891.63          59610.22
1366070601  MT Ncm Trn Ln-GnCur-IND
         441947.00          3690.00          253859.00-          250169.00-          191778.00
1366070699  Int Rb MT Ncm Trn Ln-GnCur-IND
         8683.88          8201.99          8683.88-          481.89-          8201.99
1380070601  MT ECCS Loan-Cur-IND
         699636.00          7190.00          20936.00-          13746.00-          685890.00
1380070699  Int Rb MT ECCS Loan-Cur-IND
         6047.29          6014.26          6047.29-          33.03-          6014.26
1380500001  MT ECCS Loan-Cur-BNK
         9445810.00          95842.00          1119729.00-         1023887.00-         8421923.00
1380500099  Int Rb MT ECCS Loan-Cur-BNK
         65237.95          52355.88          65237.95-          12882.07-          52355.88
1380505001  MT ECCS Loan-Cur-OTH
         22083319.00          249336.00          2245521.00-         1996185.00-        20087134.00
1380505099  Int Rb MT ECCS Loan-Cur-OTH
         2360664.39         2359561.52          2360664.39-          1102.87-         2359561.52
1407505001  MT Land Purch LnD-OTH
         123101.00          967.00          2084.00-          1117.00-          121984.00
1407505099  Int Rb MT Land Purch LnD-OTH
         967.20          958.21          967.20-          8.99-          958.21
1425505004  INT RBLE ON NON AGRI LOAN
         10345.00-          10345.00-
1441070601  MT Nn-Frm ISB Ln-D-IND
         166040.00          166040.00
1441070699  Int Rb MT Nn-Frm ISB Ln-D-IND
         377.98          450.52          377.98-          72.54          450.52
2001070601  CURRENT AC-INDIVIDUAL
         64807.90-          64807.90-
2001070602  CA-GEN-PUB-OTH-IND
         19047216.56-       58472269.00         55245615.00-         3226654.00         15820562.56-
2001070603  CA-GEN-PUB-SOC-IND
         7902765.72-       24373119.00         25282251.00-          909132.00-         8811897.72-
2001500902  CA-GEN-PUB-OTH-INS
         4144.00-          4144.00-
2001505002  CA-GEN-PUB-OTH-OTH
         900412.21-          900412.21-
2001505003  CA-GEN-PUB-SOC-OTH
         8997.00-          8997.00-
2002070601  CASH CREDIT-INDIVIDUAL
         .00
2006030601  SAVING DEP AC-STAFF
         1047191.49-        2094833.46          2060065.00-          34768.46          1012423.03-
2006030699  INT PBL-SAVING BNK ACSTAFF
         13440.43-          13440.43          17966.68-          4526.25-          17966.68-
2006070601  SAVING DEP AC-INDIVIDUAL
         75544755.00-       18949212.82         19972637.63-         1023424.81-        76568179.81-
2006070602  SBCHQ-IND
         15702554.13-       12257097.00         11936549.07-          320547.93         15382006.20-

Answer
Hello Ranadeep,

First you will need to create a table with the required fields (preferably master and transaction tables). You will then read the file using UTL_FILE, depending on the column positions, insert the data in respective fields. Do this in loop for all the transactions.

Hope this helps.

Regards

Suchitra

Oracle

All Answers


Answers by Expert:


Ask Experts

Volunteer


Suchitra Joshi

Expertise

I can answer questions regarding SQL, PL/SQL, Procedures, Functions, Triggers, SQL Loader, Oracle Forms, Oracle Reports, and some basic dba and performance tuning activities.

Experience

15+ years of Oracle PL/SQL Development

Education/Credentials
B.Sc (Electronics), Diploma in Computer Applications (DCA)

Awards and Honors
Oracle PL/SQL Developer Certified Associate (OCA)
Oracle Database: SQL Certified Expert
Brainbench certifications in Oracle Administration, PL/SQL, Developer 2000

©2016 About.com. All rights reserved.