dharmendra singh
asked on
Hi Team, I want to load data from external source system to oracle EBS.
I want to load invoice header and lines information from external system provided csv file to custom table, please help to create sql loader. this is bit urgent.
Below is file structure which contains multiple data. One header invoice can have more than 1 lines. hope below files help you to understand the requirement.
H~TEST_INVOICE~STANDARD~20
D~1~ITEM~145942.80~VAT20~P
D~2~ITEM~155509.70~VAT20~P
D~3~ITEM~164851.65~VAT20~P
D~4~ITEM~40835.47~VAT20~Pr
D~5~ITEM~43511.65~VAT20~Pr
D~6~ITEM~44992.09~VAT20~Pr
D~7~TAX~59564.40~VAT20~Pro
H~INVOICE_2~STANDARD~2017-
D~1~ITEM~30.70~VAT20~Proph
D~2~ITEM~69.30.~VAT20~Prop
D~7~TAX~20.00~VAT20~Prophe
I have created two table table A for header information and table B for lines information for each invoice headers.
Advance Thanks,
Dharmendra Singh
Sample-File.txt
Below is file structure which contains multiple data. One header invoice can have more than 1 lines. hope below files help you to understand the requirement.
H~TEST_INVOICE~STANDARD~20
D~1~ITEM~145942.80~VAT20~P
D~2~ITEM~155509.70~VAT20~P
D~3~ITEM~164851.65~VAT20~P
D~4~ITEM~40835.47~VAT20~Pr
D~5~ITEM~43511.65~VAT20~Pr
D~6~ITEM~44992.09~VAT20~Pr
D~7~TAX~59564.40~VAT20~Pro
H~INVOICE_2~STANDARD~2017-
D~1~ITEM~30.70~VAT20~Proph
D~2~ITEM~69.30.~VAT20~Prop
D~7~TAX~20.00~VAT20~Prophe
I have created two table table A for header information and table B for lines information for each invoice headers.
Advance Thanks,
Dharmendra Singh
Sample-File.txt
Prashant Sabnekar
Are you trying to load data from external file(csv) to a table? To do this you can use SQL Developer.
ASKER
Yes, I understood sql developer could help to load data. Will you in position to suggest/guide me to load above file(csv) data to custom table
Yes, I did this so many times.
Where are you facing the issue?
Where are you facing the issue?
ASKER
I'm confuse, below points like.
1. Do i need to create two external table
A. Table A for invoice header level information(H)
B. Table B for Invoice lines level information(D)
2. If two table need to create how to map the invoice with lines table(B), how system will understand the invoice line detail
3. If two table need to create how to insert data in two different table from one csv file.
Your help is really appreciated.
Thanks,
1. Do i need to create two external table
A. Table A for invoice header level information(H)
B. Table B for Invoice lines level information(D)
2. If two table need to create how to map the invoice with lines table(B), how system will understand the invoice line detail
3. If two table need to create how to insert data in two different table from one csv file.
Your help is really appreciated.
Thanks,
There is only one table needed to load data from one file.
If you have two tables with different data then you need to create two files (csv) and load them into the concerned tables.
If you have two tables with different data then you need to create two files (csv) and load them into the concerned tables.
ASKER
That's why am confuse. if i insert all the data in one table, how would i identify lines of any invoice.
Suppose in file one invoice can have multiples lines and same wise other invoice will also have, in this case how to handle.
Suppose in file one invoice can have multiples lines and same wise other invoice will also have, in this case how to handle.
That is your DB schema design. I can not comment on that. Might be you need to check your data model design.
To load the data from a csv file to a database table you need to do this.
Create a connection to the database in SQL Developer
Create a table as a destination for the xls data
save excel data as csv
right click on the table from step 2
select 'import data'
select csv file
use the wizzard and continue.
To load the data from a csv file to a database table you need to do this.
Create a connection to the database in SQL Developer
Create a table as a destination for the xls data
save excel data as csv
right click on the table from step 2
select 'import data'
select csv file
use the wizzard and continue.
ASKER
table created.
after right click on table i did not find 'import data'
You can follow this link
https://www.thatjeffsmith.com/archive/2012/04/how-to-import-from-excel-to-oracle-with-sql-developer/
https://www.thatjeffsmith.com/archive/2012/04/how-to-import-from-excel-to-oracle-with-sql-developer/
ASKER
Thanks, this work. data inserted from file to table but this is manual work.
I want create one data loader sql and create concurrent program and schedule it so no manual work. data loader will read data from file and insert into table
I want create one data loader sql and create concurrent program and schedule it so no manual work. data loader will read data from file and insert into table
ASKER
Your are really awesome. but my question was misleading it's SQL loader not Data loader. Sorry for typo
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much.
I see 0 points for this questions. May I know why it is assigned 0 points?
My Recommendation is option 2.
Please accept comment ID: 42295741 with all available points and the best grade.
Thanks,
Prashant
Please accept comment ID: 42295741 with all available points and the best grade.
Thanks,
Prashant