Home » RDBMS Server » Server Utilities » Issue while Inserting Multiline Records from a CSV file (2 threads merged by bb) (Oracle 10g SQL LOADER)
| Issue while Inserting Multiline Records from a CSV file (2 threads merged by bb) [message #539508] |
Mon, 16 January 2012 10:59  |
|
|
I have a CSV(Excel) file which i have exported From TAlly.This is like below
---Col1--- | ---Col2-- |---Col3-- |---Col4---
01-04-2010, Tour_ABC , 2343.00 , 0.00
------- -- , Tour_DEF , 1212.00 , 0.00
--------- Tour_GHI , 0.00 , 2367.00
02-04-2010, Tour_MNO, 3567.00, 0.00
---------- , Tour_jkh , 0.00, 2342.00
Now the problem is how to Insert these in to an Oracle table as 2 record under value '01-04-2010' of col1 and one record under 02-04-2011 under col1 is having no dates(This is exactly the excel format exported from tally).That means while one record is having 4 values others are having 3 values.I want those blank fields under the column to be filled by the same date of the parent record while data loding in to oracle.
Urgent help needed
[mod-edit: code tags added by bb]
[Updated on: Mon, 16 January 2012 12:19] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: Issue while Inserting Multiline Records from a CSV file [message #539522 is a reply to message #539511] |
Mon, 16 January 2012 13:43  |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You can do this by loading the data into a staging table with a SQL*Loader sequence, then inserting from the staging table to the target table.
I have made some assumptions about table structure and date format, in order to provide the example below. If yours are different, then you should be able to modify it easily. One line of your data file appeared to be missing a comma before Tour_GHI, so I added one.
In the control file, I used skip=1 to skip the header line. In the control file, I also used a SQL*Loader sequence to number the rows in the table in the order that they appeared in the data file. In the control file, I also used replace to remove hyphens, then trim to remove any remaining leading and/or trailing spaces, so that values of col1 with only hyphens and spaces became null. I then loaded the data into a staging table.
I then inserted the data from the staging table to the target table. I used nvl and lag with ignore nulls and ordered by the sequence, in order to select the last non-null value of col1 when ordered by the sequence. I also eliminated any rows where col2 was null, in order to eliminate the blank line between the two sets of data in your data file.
-- excel.csv data file:
---Col1--- | ---Col2-- |---Col3-- |---Col4-- prompt
01-04-2010, Tour_ABC , 2343.00 , 0.00
------- -- , Tour_DEF , 1212.00 , 0.00
--------- , Tour_GHI , 0.00 , 2367.00
02-04-2010, Tour_MNO, 3567.00, 0.00
---------- , Tour_jkh , 0.00, 2342.00
-- test.ctl control file:
options (skip=1)
load data
infile excel.csv
into table staging
fields terminated by ','
trailing nullcols
(seqnum sequence,
col1 "to_date (trim (replace (:col1, '-', '')), 'ddmmyyyy')",
col2 "trim (:col2)",
col3,
col4)
-- staging table:
SCOTT@orcl_11gR2> create table staging
2 (seqnum number,
3 col1 date,
4 col2 varchar2(10),
5 col3 number,
6 col4 number)
7 /
Table created.
-- load into staging table and results:
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log
SCOTT@orcl_11gR2> select * from staging order by seqnum
2 /
SEQNUM COL1 COL2 COL3 COL4
---------- --------- ---------- ---------- ----------
1 01-APR-10 Tour_ABC 2343 0
2 Tour_DEF 1212 0
3 Tour_GHI 0 2367
4
5 02-APR-10 Tour_MNO 3567 0
6 Tour_jkh 0 2342
6 rows selected.
-- target table:
SCOTT@orcl_11gR2> create table target
2 (col1 date,
3 col2 varchar2(10),
4 col3 number,
5 col4 number)
6 /
Table created.
-- insert from staging to target and results:
SCOTT@orcl_11gR2> insert into target (col1, col2, col3, col4)
2 select nvl
3 (col1,
4 lag (col1 ignore nulls) over (order by seqnum)),
5 col2, col3, col4
6 from staging
7 where col2 is not null
8 /
5 rows created.
SCOTT@orcl_11gR2> select * from target
2 /
COL1 COL2 COL3 COL4
--------- ---------- ---------- ----------
01-APR-10 Tour_ABC 2343 0
01-APR-10 Tour_DEF 1212 0
01-APR-10 Tour_GHI 0 2367
02-APR-10 Tour_MNO 3567 0
02-APR-10 Tour_jkh 0 2342
5 rows selected.
[Updated on: Mon, 16 January 2012 14:29] Report message to a moderator |
|
|
|
| Re: Issue while Inserting Multiline Records from a CSV file [message #539524 is a reply to message #539522] |
Mon, 16 January 2012 14:09 |
|
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following uses an external table, as Michel suggested, instead of SQL*Loader. It requires that the data file be on the server. A control file is not needed. You will need an Oracle directory object.
-- excel.csv data file:
---Col1--- | ---Col2-- |---Col3-- |---Col4-- prompt
01-04-2010, Tour_ABC , 2343.00 , 0.00
------- -- , Tour_DEF , 1212.00 , 0.00
--------- , Tour_GHI , 0.00 , 2367.00
02-04-2010, Tour_MNO, 3567.00, 0.00
---------- , Tour_jkh , 0.00, 2342.00
-- Oracle directory object and external staging table:
SCOTT@orcl_11gR2> create or replace directory my_dir as 'c:\my_oracle_files'
2 /
Directory created.
SCOTT@orcl_11gR2> create table staging
2 (col1 varchar2(10),
3 col2 varchar2(10),
4 col3 number,
5 col4 number)
6 ORGANIZATION external
7 (
8 TYPE oracle_loader
9 DEFAULT DIRECTORY my_dir
10 ACCESS PARAMETERS
11 (
12 RECORDS DELIMITED BY NEWLINE
13 LOGFILE 'test.log'
14 SKIP 1
15 FIELDS TERMINATED BY "," LRTRIM
16 MISSING FIELD VALUES ARE NULL
17 REJECT ROWS WITH ALL NULL FIELDS
18 (col1, col2, col3, col4)
19 )
20 location ('excel.csv')
21 )REJECT LIMIT UNLIMITED
22 /
Table created.
SCOTT@orcl_11gR2> select * from staging
2 /
COL1 COL2 COL3 COL4
---------- ---------- ---------- ----------
01-04-2010 Tour_ABC 2343 0
------- -- Tour_DEF 1212 0
--------- Tour_GHI 0 2367
02-04-2010 Tour_MNO 3567 0
---------- Tour_jkh 0 2342
5 rows selected.
-- target table:
SCOTT@orcl_11gR2> create table target
2 (col1 date,
3 col2 varchar2(10),
4 col3 number,
5 col4 number)
6 /
Table created.
-- insert from staging to target and results:
SCOTT@orcl_11gR2> insert into target (col1, col2, col3, col4)
2 with
3 data as
4 (select rownum seqnum,
5 to_date (trim (replace (col1, '-', '')), 'ddmmyyyy') col1,
6 trim (col2) col2, col3, col4
7 from staging)
8 select nvl
9 (col1,
10 lag (col1 ignore nulls) over (order by seqnum)),
11 col2, col3, col4
12 from data
13 where col2 is not null
14 /
5 rows created.
SCOTT@orcl_11gR2> select * from target
2 /
COL1 COL2 COL3 COL4
--------- ---------- ---------- ----------
01-APR-10 Tour_ABC 2343 0
01-APR-10 Tour_DEF 1212 0
01-APR-10 Tour_GHI 0 2367
02-APR-10 Tour_MNO 3567 0
02-APR-10 Tour_jkh 0 2342
5 rows selected.
[Updated on: Mon, 16 January 2012 14:32] Report message to a moderator |
|
|
|
| Re: Issue while Inserting Multiline Records from a CSV file [message #539526 is a reply to message #539524] |
Mon, 16 January 2012 14:24 |
|
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following uses the method in the link provided by LKBrwn_DBA. By using a package and trigger, it eliminates the need for either a staging table with sequence or an Oracle directory object and external table. It uses SQL*Loader to load directly to the target table.
-- excel.csv data file:
---Col1--- | ---Col2-- |---Col3-- |---Col4-- prompt
01-04-2010, Tour_ABC , 2343.00 , 0.00
------- -- , Tour_DEF , 1212.00 , 0.00
--------- , Tour_GHI , 0.00 , 2367.00
02-04-2010, Tour_MNO, 3567.00, 0.00
---------- , Tour_jkh , 0.00, 2342.00
-- test.ctl control file:
options (skip=1)
load data
infile excel.csv
into table target
fields terminated by ','
trailing nullcols
(col1 "to_date (trim (replace (:col1, '-', '')), 'ddmmyyyy')",
col2 "trim (:col2)",
col3,
col4)
-- target table:
SCOTT@orcl_11gR2> create table target
2 (col1 date,
3 col2 varchar2(10),
4 col3 number,
5 col4 number)
6 /
Table created.
-- package and trigger as suggested in link:
SCOTT@orcl_11gR2> CREATE OR REPLACE PACKAGE demo_pkg AS
2 last_col1 DATE;
3 END demo_pkg;
4 /
Package created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> CREATE OR REPLACE TRIGGER target_bir
2 BEFORE INSERT ON target
3 FOR EACH ROW
4 BEGIN
5 IF :NEW.col1 IS NOT NULL THEN
6 demo_pkg.last_col1 := :NEW.col1;
7 ELSE
8 :NEW.col1 := demo_pkg.last_col1;
9 END IF;
10 END traget_bir;
11 /
Trigger created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
-- load into target table and results:
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log
SCOTT@orcl_11gR2> select * from target
2 /
COL1 COL2 COL3 COL4
--------- ---------- ---------- ----------
01-APR-10 Tour_ABC 2343 0
01-APR-10 Tour_DEF 1212 0
01-APR-10 Tour_GHI 0 2367
02-APR-10 Tour_MNO 3567 0
02-APR-10 Tour_jkh 0 2342
5 rows selected.
|
|
|
|
| Re: Issue while Inserting Multiline Records from a CSV file [message #539588 is a reply to message #539522] |
Tue, 17 January 2012 05:00 |
|
|
Thanks a lot Barbara. Really it helped me a lot as I dont use sql loader frequently and I do all the stuffs through OWB. But today I again stumbled upon while.
Kindly Go through the attachemnt.
Understanding: Line no 1 to Line No 11 is a single transaction from (From Finacial Point of view) .Line 12 to Line line 15 is another transaction .The value in the line 11,15 etc are the voucher no for all the multiple records for those corresponding transactions. I have to Load data into Oracle table in th following way with the following columns.
My oracle table columns should be like this.
DATE|LedgerName|UnUsedData1|PaymentType|Debit Amount|CreditAmount|UnusedData2|Vocucher No
I have to fill the columns under date by the parent date that is for line 2 to 10 it should be 01/04/2011 and the line no 11 (No.:001) should be voucher No for all the records from 2 to 10.You can make a .Xls file from the csv file attached with this for better look. Kindly help
-
Attachment: tally.csv
(Size: 8.25KB, Downloaded 2125 times)
|
|
|
|
| Re: Issue while Inserting Multiline Records from a CSV file [message #539646 is a reply to message #539588] |
Tue, 17 January 2012 10:18 |
|
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
A lot of people cannot or will not download attachments, due to fear of viruses or security restrictions, so it is better if you include the csv data in your post, instead of as an attachment. I don't know if your data is real or simulated and if it is copied and pasted or re-typed, but it appears to have some inconsistencies, so it will cause some data to be loaded into wrong columns. Also, you still have not specified your date format, so I cannot tell if 01/04/2011 is January 4th of 2011 or the 1st of April of 2011. I have used the 1st of April in the code below.
You should have provided a create table statement as I have done in the code below or at least a copy and paste of a describe of the table structure run from SQL*Plus that would provide accurate column names and data types and lengths. You did not provide a table name, so I continued to use target. Your column names appear to be made up and not realistic. For example, date is a reserved word and should not be used for a column name. You should also not have spaces in your column names. You can use these by enclosing them in double quotes, but doing so is a bad practice, as it creates many other problems. I have therefore continued to use col1 for the date column and modified some of the others slightly.
You did not say which of the three methods demonstrated that you wish to use or whether your data file is on your server or your client. Since your date file might be on your client and you seem to want to use SQL*Loader, I did not use the external table method in the code below. It seems like the package and trigger method is the simplest and recommended by Oracle, so I used that.
You should have been able to use what I already provided to create something like the code below. If you need to make further modifications, due to not having provided accurate data file or date format or table structure or other things, then you should still be able to make the minimal required modifications. If you need further help, then you need to provide accurate things and show what you have tried and the errors or results that you got and what you want instead.
-- If you have a data file like this tally.csv that you provided as an attachment:
1/4/2011,"DDC, Chennai Current A/c",,Jrnl,-34338954.5
,Fund Trans_Speech,,2659372.5,
,Fund Trans _Indo Japan Speech DST,,2703656,
,Fund Trans_Corpus Creation,,2553105,
,Fund Trans_SSM Paschim,,777544,
,Fund Trans_SSM HWH,,2414736,
,Fund Trans_Env DST,,4112089,
,Fund Trans_Auto BL Tea,,428170,
,Fund Trans_VB DLI,,15222282,
,Fund Trans_Image Proc,,3468000,
(No. :001),,,,
2/4/2011,Fixed Deposit,,Jrnl,-1029529
,Fixed Deposit,,1013562,
,Interest on TDR,,15967,
(No. :001A),,,,
4/4/2011,Sundry Liability,,Pymt,-7450
,State Bank of India,,7450,,
(No. :0001),,,,,
4/4/2011,Sundry Liability,,Pymt,-7450,
,State Bank of India,,7450,,
(No. :0002),,,,,
4/4/2011,MA/Amitesh De,,Pymt,-15000,
,State Bank of India,,15000,,
(No. :0003),,,,,
4/4/2011,TA/M.K.Saha,,Rcpt,,13380
,State Bank of India,-13380,,,
(No. :001),,,,,
5/4/2011,"DDC, Kolkata Current A/c",,Pymt,-568417,
,State Bank of India,,568417,,
(No. :0004),,,,,
5/4/2011,Salary & Wages,,Pymt,-150614,
,State Bank of India,,150614,,
(No. :0005),,,,
5/4/2011,Consm & Other_Dig Library,,Pymt,-1333
,State Bank of India,,1333,
(No. :0006),,,,
5/4/2011,Sundry Liability,,Pymt,-4658
,Sundry Liability,-2225,,
,Sundry Liability,-1524,,
,Sundry Liability,-1996,,
,State Bank of India,,10403,
(No. :0007),,,,
5/4/2011,Sundry Liability,,Pymt,-644
,State Bank of India,,644,
(No. :0008),,,,
5/4/2011,Sundry Liability,,Pymt,-818
,State Bank of India,,818,
(No. :0009),,,,
5/4/2011,Sundry Liability,,Pymt,-660
,State Bank of India,,660,
(No. :0010),,,,
5/4/2011,Sundry Liability,,Pymt,-98
,State Bank of India,,98,
(No. :0011),,,,
5/4/2011,Sundry Liability,,Pymt,-963
,State Bank of India,,963,
(No. :0012),,,,
5/4/2011,Sundry Liability,,Pymt,-1965
,State Bank of India,,1965,
(No. :0013),,,,
5/4/2011,Sundry Liability,,Pymt,-2083
,State Bank of India,,2083,
(No. :0014),,,,
5/4/2011,Sundry Liability,,Pymt,-2308
,State Bank of India,,2308,,
(No. :0015),,,,,
5/4/2011,TA/MANAS CHAKRABORTY,,Rcpt,,727
,State Bank of India,-727,,,
(No. :002),,,,,
5/4/2011,Interest on TDR,,Rcpt,,8673
,State Bank of India,-8673,,,
(No. :003),,,,,
-- and you want to load the data into a target table like this:
SCOTT@orcl_11gR2> CREATE TABLE target
2 (col1 DATE,
3 LedgerName VARCHAR2(33),
4 UnUsedData1 VARCHAR2(11),
5 PaymentType VARCHAR2(11),
6 DebitAmount NUMBER,
7 CreditAmount NUMBER,
8 UnUsedData2 VARCHAR2(11),
9 VoucherNo NUMBER)
10 /
Table created.
-- then you need to create a package like this:
SCOTT@orcl_11gR2> CREATE OR REPLACE PACKAGE demo_pkg AS
2 last_col1 DATE;
3 END demo_pkg;
4 /
Package created.
-- then create a trigger like this:
SCOTT@orcl_11gR2> CREATE OR REPLACE TRIGGER target_bir
2 BEFORE INSERT ON target
3 FOR EACH ROW
4 BEGIN
5 IF :NEW.col1 IS NOT NULL THEN
6 demo_pkg.last_col1 := :NEW.col1;
7 ELSE
8 :NEW.col1 := demo_pkg.last_col1;
9 END IF;
10 END target_bir;
11 /
Trigger created.
-- then create a SQL*Loader control file like this test.ctl:
LOAD DATA
INFILE tally.csv
INTO TABLE target
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(col1 NULLIF col1=BLANKS "TO_DATE (:col1, 'DD/MM/YYYY')",
LedgerName,
UnusedData1,
PaymentType,
DebitAmount,
CreditAmount,
UnusedData2,
VoucherNo)
-- then load your data using SQL*Loader like this from SQL*Plus, substituting your username and password for scott and tiger (or you could load it from the operating system without the HOST command):
SCOTT@orcl_11gR2> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log
-- then you should get the following results (some data appears to be in the wrong columns, due to inconsistencies in your data file that you need to fix, not due to any problems with the loading process):
SCOTT@orcl_11gR2> SET LINESIZE 120
SCOTT@orcl_11gR2> SELECT * FROM target
2 /
COL1 LEDGERNAME UNUSEDDATA1 PAYMENTTYPE DEBITAMOUNT CREDITAMOUNT UNUSEDDATA2 VOUCHERNO
--------- --------------------------------- ----------- ----------- ----------- ------------ ----------- ----------
01-APR-11 DDC, Chennai Current A/c Jrnl -34338955
01-APR-11 Fund Trans_Speech 2659372.5
01-APR-11 Fund Trans _Indo Japan Speech DST 2703656
01-APR-11 Fund Trans_Corpus Creation 2553105
01-APR-11 Fund Trans_SSM Paschim 777544
01-APR-11 Fund Trans_SSM HWH 2414736
01-APR-11 Fund Trans_Env DST 4112089
01-APR-11 Fund Trans_Auto BL Tea 428170
01-APR-11 Fund Trans_VB DLI 15222282
01-APR-11 Fund Trans_Image Proc 3468000
02-APR-11 Fixed Deposit Jrnl -1029529
02-APR-11 Fixed Deposit 1013562
02-APR-11 Interest on TDR 15967
04-APR-11 Sundry Liability Pymt -7450
04-APR-11 State Bank of India 7450
04-APR-11 Sundry Liability Pymt -7450
04-APR-11 State Bank of India 7450
04-APR-11 MA/Amitesh De Pymt -15000
04-APR-11 State Bank of India 15000
04-APR-11 TA/M.K.Saha Rcpt 13380
04-APR-11 State Bank of India -13380
05-APR-11 DDC, Kolkata Current A/c Pymt -568417
05-APR-11 State Bank of India 568417
05-APR-11 Salary & Wages Pymt -150614
05-APR-11 State Bank of India 150614
05-APR-11 Consm & Other_Dig Library Pymt -1333
05-APR-11 State Bank of India 1333
05-APR-11 Sundry Liability Pymt -4658
05-APR-11 Sundry Liability -2225
05-APR-11 Sundry Liability -1524
05-APR-11 Sundry Liability -1996
05-APR-11 State Bank of India 10403
05-APR-11 Sundry Liability Pymt -644
05-APR-11 State Bank of India 644
05-APR-11 Sundry Liability Pymt -818
05-APR-11 State Bank of India 818
05-APR-11 Sundry Liability Pymt -660
05-APR-11 State Bank of India 660
05-APR-11 Sundry Liability Pymt -98
05-APR-11 State Bank of India 98
05-APR-11 Sundry Liability Pymt -963
05-APR-11 State Bank of India 963
05-APR-11 Sundry Liability Pymt -1965
05-APR-11 State Bank of India 1965
05-APR-11 Sundry Liability Pymt -2083
05-APR-11 State Bank of India 2083
05-APR-11 Sundry Liability Pymt -2308
05-APR-11 State Bank of India 2308
05-APR-11 TA/MANAS CHAKRABORTY Rcpt 727
05-APR-11 State Bank of India -727
05-APR-11 Interest on TDR Rcpt 8673
05-APR-11 State Bank of India -8673
52 rows selected.
|
|
|
|
| Re: Issue while Inserting Multiline Records from a CSV file [message #539652 is a reply to message #539646] |
Tue, 17 January 2012 10:48 |
|
|
Barbara..Thanks a lot and sorry for not providing everything in a user friendly manner so that u cld have understood it better. I will send it after few hours.But 3 things I want to clarify.
1- We are extracting data from a financial package called "Tally" very popular in India. The software keeps its data in its own format.But have provision for export in Excel format.
2- Yeah I can understand your difficulty in analysing the data (Special the no of (,)s in each row.Thats why I asked u to import the data in to a Excel file by using that import external data option in Ms Excel
3- What us have done is absolutely ok except few things that is missing Voucher Nos.
So I shall send u all the things in a better format for your clarity.
Again thanks a Lot
|
|
|
|
|
|
| Re: Issue while Inserting Multiline Records from a CSV file [message #539657 is a reply to message #539652] |
Tue, 17 January 2012 11:33 |
|
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
dsamaresh wrote on Tue, 17 January 2012 08:48
2- Yeah I can understand your difficulty in analysing the data (Special the no of (,)s in each row.Thats why I asked u to import the data in to a Excel file by using that import external data option in Ms Excel
The problem is not with me being able to analyze the data. The problem is with SQL*Loader being able to understand it. SQL*Loader will only load a text file, like the one that you provided, not an Excel file. SQL*Loader can only use the commas to tell where one column ends and the next begins. So, if commas are missing, then it will cause SQL*Loader to try to put some data in the wrong columns. You need to make sure that your data is an a recognizable format. You should be able to specify that there is a comma after every column, even if it is null. Any columns that have a comma in the data should be enclosed within double quotes. You should also try to get your data file so that all of the data is on one line. If necessary, you can load your data into Excel, then export it from Excel in a proper format, using the options in Excel. Otherwise, you will need some means of identifying when a new record starts, so that it knows whether what is on the next row is part of the previous row or a new record. SQl*Loader can load either a delimited file or fixed format file. I have given you everything that you should need to do the load yourself. Please try to do it yourself, then if you encounter any problems, post what you tried. This forum is intended to help you learn to do it yourself, not to do just write all of the code for you.
[EDITED by LF: removed superfluous empty lines]
[Updated on: Tue, 17 January 2012 14:25] by Moderator Report message to a moderator |
|
|
|
| Re: Issue while Inserting Multiline Records from a CSV file [message #539721 is a reply to message #539646] |
Wed, 18 January 2012 04:44 |
|
|
Wont the trigger cause if I run sql loader to load data from two csv files simultaneously with same format ? And How to put the voucher No(No. :001) in the line 11 for all the records from 1 to 10 and like wise voucher No in line 15 (No. :001A) for line no 12 to 14 and Likewise
1/4/2011,"CDAC, Kolkata Current A/c",,Jrnl,-34338954.5
,Fund Trans_Speech,,2659372.5,
,Fund Trans _Indo Japan Speech DST,,2703656,
,Fund Trans_Corpus Creation,,2553105,
,Fund Trans_SSM Paschim,,777544,
,Fund Trans_SSM HWH,,2414736,
,Fund Trans_Env DST,,4112089,
,Fund Trans_Auto BL Tea,,428170,
,Fund Trans_VB DLI,,15222282,
,Fund Trans_Image Proc,,3468000,
(No. :001),,,,
2/4/2011,Fixed Deposit,,Jrnl,-1029529
,Fixed Deposit,,1013562,
,Interest on TDR,,15967,
(No. :001A),,,,
4/4/2011,Sundry Liability,,Pymt,-7450
,State Bank of India,,7450,,
(No. :0001),,,,,
5/4/2011,TA/MANAS CHAKRABORTY,,Rcpt,,727
,State Bank of India,-727,,,
No. :002),,,,,
|
|
|
|
| Re: Issue while Inserting Multiline Records from a CSV file [message #539826 is a reply to message #539721] |
Wed, 18 January 2012 12:15 |
|
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
dsamaresh wrote on Wed, 18 January 2012 02:44
Wont the trigger cause if I run sql loader to load data from two csv files simultaneously with same format ?
Your question is unclear. If you anticipate problems due to running two loads at the same time, then either test to see if it causes a problem or don't run two loads at the same time or use one of the other two methods instead.
dsamaresh wrote on Wed, 18 January 2012 02:44
And How to put the voucher No(No. :001) in the line 11 for all the records from 1 to 10 and like wise voucher No in line 15 (No. :001A) for line no 12 to 14 and Likewise
As I said in my previous response,
"You should also try to get your data file so that all of the data is on one line. If necessary, you can load your data into Excel, then export it from Excel in a proper format, using the options in Excel. Otherwise, you will need some means of identifying when a new record starts, so that it knows whether what is on the next row is part of the previous row or a new record. SQl*Loader can load either a delimited file or fixed format file."
|
|
|
|
Goto Forum:
Current Time: Mon Dec 23 07:10:37 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
