如何将多个CSV文件加载到多个表中 [英] how to load multiple CSV files into Multiple Tables
问题描述
我在文件夹中有多个CSV文件
I have Multiple CSV files in Folder
示例:
Member.CSv
Leader.CSv
我需要将它们加载到数据库表中
I need to load them in to Data base tables .
我已经使用ForEachLoop容器,Data FlowTask,Excel Source和OLEDB Destination进行了研究
I have worked on it using ForEachLoop Container ,Data FlowTask, Excel Source and OLEDB Destination
如果使用表达式和优先约束来做,但是如果我有10个以上的文件,该如何使用脚本任务来做呢..我被这个卡住了
we can do if by using Expressions and Precedence Constraints but how can I do using Script task if I have more than 10 files ..I got Stuck with this one
推荐答案
我们有一个类似的问题,我们的解决方案是上述建议的混合。
We have a similar issue, our solution is a mixture of the suggestions above.
- 我们每天从客户端发送许多文件类型。
- 这些具有特定的文件名模式(例如SalesTransaction20160218.csv,Product20160218.csv)
- 这些文件类型中的每一个都具有您期望的结构的暂存着陆表
- 然后我们有一个.net脚本任务,该任务采用文件名模式并将数据加载到登录表中。
- csv解析器中还进行了各种检查-匹配的列数,进行一些基本数据验证,然后再加载到着陆表中
- We have a number of files types sent from our client on a daily basis.
- These have a specific filename pattern (e.g. SalesTransaction20160218.csv, Product20160218.csv)
- Each of these file types have a staging "landing" table of the structure you expect
- We then have a .net script task that takes the filename pattern and loads that data into a landing table.
- There are also various checks that are done within the csv parser - matching number of columns, some basic data validation, before loading into the landing table
.net程序员不足以使我们能够动态解析未知的文件结构,创建SQL表,然后将数据加载到其中。我希望这是可行的,毕竟,这是SSIS导入/导出向导所做的(需要一些手动干预)
We are not good enough .net programmers to be able to dynamically parse an unknown file structure, create SQL table and then load the data in. I expect it is feasible, after all, that is what the SSIS Import/Export Wizard does (with some manual intervention)
作为此方法的替代方法(过程非常微妙),我们正在尝试HDFS数据登陆区,那么它使我们可以使用R之类的分析工具来解析HDFS中的数据。之后,利用PIG将数据加载到SQL中。
As an alternative to this (the process is quite delicate), we are experimenting with a HDFS data landing area, then it allows us to use analytic tools like R to parse the data within HDFS. After that utilising PIG to load the data into SQL.
这篇关于如何将多个CSV文件加载到多个表中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!