将数据从.csv文件加载到表中 [英] Loading data into table from .csv file

问题描述

以下是我的查询的详细信息。

数据处理如下：

我有.csv文件中的数据.-->通过程序--->来自.csv的数据存储在收入表中。



表结构

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)

数据文件（.csv）结构

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

推荐答案

1）避免重复的更好方法是创建一个具有相同结构名称的临时表RevenueTemp

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

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

并在完成下面的查询后，首次在此Temp表中加载.csv文件，从临时表插入主表

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

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

我希望您在收入表中的ID设置为主键。



2）您可以使用列ID，FileName创建一个标题表，在处理完整文件之前，您可以先在此表中查看

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

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

如果找不到数据则先插入在HeaderTable中行，并在id为外键的详细信息表中休息

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

这篇关于将数据从.csv文件加载到表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！