Loading data into table from .csv file

Question

1.00/5 (1 vote)

See more:

Below are the details of my query.
Processing of data goes as below
I have Data in .csv file.--> through procedure---> Data from .csv gets stored into revenue table.

Table structure

ID----Varchar2 
Name--> Varchar2 
Revenue_Amount--> number
Upd_dt--->date (sysdate)

Data file(.csv) structure

Acct-OCT-15,  NAME,  RevenueAmount,
11111190778,  ABC,   100000
11111119877,  ABC1,  200020

Issue: We need to load the file record monthly. But if the duplicate file will come we need to identify and stop the processing of the file.
In data file header first row can be taken as identifier but I am not sure how do I implement this scenario.

Will receive data file on monthly basis. Please don't hesistate to ask if question is not clear

Posted 18-Dec-15 16:28pm

Member 10431234

Updated 18-Dec-15 18:53pm

George Jonsson

v2

Add a Solution

Comments

Mehdi Gholam 19-Dec-15 1:46am

What have you done so far?

Member 10431234 19-Dec-15 7:59am

I have created a procedure and inserted the records into the table.
It looks like as below
Procedure
--------------
create or replace procedure revenue_sp
(
ID VArchar2
Name Varchar2,
Revenue_Amount number,
Upd_dt date,
v_out Out Varchar2);
as
l_ID t_revenue.ID%TYPE;
l_Name t_revenue.ID%TYPE;
l_Revenue_Amount t_revenue.ID%TYPE;
l_Upd_dt t_revenue.ID%TYPE;
IS
BEGIN
l_Upd_dt :=sysdate ;
insert into t_revenue
(
ID ,
Name ,
Revenue_Amount ,
Upd_dt
)
Values
(
l_ID ,
l_Name ,
l_Revenue_Amount ,
l_Upd_dt
)
commit;
END;

Table
----------
ID Name Revenue_Amount Upd_dt
11111190778 ABC 100000 19-Dec-2015
11111119877 ABC1 200020 19-Dec-2015

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Basmeh Awad · Answer 1 · 2015-12-19T23:22:00

1) better way to avoid duplicates is by creating a temp table with same structure name RevenueTemp

SQL

CREATE TABLE RevenueTemp 
AS SELECT * FROM Revenue where 1=0

and first time load your .csv file in this Temp table after completing use below query to insert from temp table to main table

SQL

Insert into Revenue
Select * from RevenueTemp a left outer join Revenue b on a.id=b.id where b.id is null

And i hope your id in Revenue Table is set as Primary Key.

2)You can create one header table with column ID,FileName and before processing complete file you can first check in this table

SQL

SELECT * FROM HeaderTable WHERE FileName='Acct-OCT-15'

if no data found then insert first line in HeaderTable and rest in your detail table with id as foreign key