从CSV文件填充SQL数据库 [英] Fill SQL database from a CSV File

查看：72
本文介绍了从CSV文件填充SQL数据库的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我需要使用带有SSIS的CSV文件创建数据库. CSV文件包括四列:

I need to create a database using a CSV file with SSIS. The CSV file includes four columns:

我需要使用该表的信息来填充我在下面的SQL中创建的三个表.

I need to use the information of that table to populate the three tables I created in SQL below.

我已经意识到，我需要使用Employee表EmployeeNumber和Group Table GroupID的一列来填充EmployeeGroup表.为此，我认为我需要一个Join Merge表，但是我在SSIS中创建了Data Flow Task，结果是一样的，没有数据显示.

I have realized that what I need is to use one column of the Employee Table, EmployeeNumber, and Group Table, GroupID, to populate the EmployeeGroup table. For that, I thought that a Join Merge table is what I needed, but I created the Data Flow Task in SSIS, and the results are the same, no data displayed.

中间表是用于关联其他表的表.

The middle table is the one used to relate the other tables.

我在SSIS中创建了程序包，并填充了Employee和Group表，但没有填充EmployeeGroup表. EmployeeGroup将仅显示没有数据的EmployeeNumber和Group ID列.

I created the package in SSIS and the Employee and Group Tables are populated, but the EmployeeGroup table is not. EmployeeGroup will only show the EmployeeNumber and Group ID columns with no data.

我是使用SSIS的新手，我真的不知道该怎么办.非常感谢您的帮助.

I am new using SSIS, and I really do not know what else to do. I will really appreciate your help.

推荐答案

概述

  1. 使用SSIS的解决方案
    • 使用3个数据流任务
    • 使用2个数据流任务
  1. Solutions using SSIS
    • Using 3 Data Flow Tasks
    • Using 2 Data Flow Tasks
  • 使用Microsoft.Ace.OLEDB
  • 使用Microsoft文本驱动程序
  • Using Microsoft.Ace.OLEDB
  • Using Microsoft Text Driver

第一个解决方案-SSIS

使用3个数据流任务

这只能使用2个Data Flow Task来完成，但是根据问题I am new using SSIS, and I really do not know what else to do中提到的OP，我将提供最简单的解决方案，即3 DataFlow Task，以避免使用更多的组件，例如MultiCast

1st Solution - SSIS

Using 3 Data Flow Tasks

This can be done using only 2 Data Flow Task, but according to what the OP mentioned in the question I am new using SSIS, and I really do not know what else to do, i will provide easiest solution which is 3 DataFlow Task to avoid using more components like MultiCast.

由于要构建关系数据库并从csv中提取关系，因此必须读取csv 3次-将其视为3个独立文件-.

Because you want to build a relational database and extract relations from the csv, you have to read the csv 3 times -consider it as 3 seperated files -.

首先，您必须导入雇员和组数据，然后，必须导入它们之间的关系表.

First you have to import Employees and Groups Data, Then you have to import the relation table between them.

每个导入步骤都可以在单独的数据流任务中完成

Each Import step can be done in a seperate Data Flow Task

  1. 添加平面文件连接管理器(Csv文件)
  2. 添加 OLEDB连接管理器(SQL目标)
  3. 添加3个 DataFlow任务，如下图所示
  1. Add a Flat File connection Manager (Csv File)
  2. Add An OLEDB connection Manager (SQL Destination)
  3. Add 3 DataFlow Task like the image below

  1. 添加平面文件源，脚本组件， OLEDB目标，如下图所示.
  1. Add a Flat File Source , a Script Component , OLEDB destination like shown in the image below

  1. 在脚本组件中，选择组名"列作为输入
  1. In the Script Component choose Group Name column as Input

  1. 选择输出缓冲区并将SynchronousInputID Property更改为None并添加类型为DT_STR
    2. 的输出列OutGroupname
  1. Select the Output Buffer and change SynchronousInputID Property to None And add an output column OutGroupname with type DT_STR

  1. 在脚本部分中，编写以下代码:

  1. In the Script section write the following Code:

 Imports System.Collections.Generic

 Private m_List As New List(Of String)
 Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

If Not Row.GroupName_IsNull AndAlso
        Not String.IsNullOrEmpty(Row.GroupName.Trim) Then

    If Not m_List.Contains(Row.GroupName.Trim) Then

        m_List.Add(Row.GroupName.Trim)

        CreateOutputRows(Row.GroupName.Trim)

    End If


End If
End Sub

Public Sub CreateOutputRows(ByVal strValue As String)


Output0Buffer.AddRow()
Output0Buffer.OutGroupName = strValue
End Sub

  • 在 OLEDB目标上，将OutGroupName映射到GroupName列

  • On the OLEDB Destination map OutGroupName to GroupName Column

    • 重复使用Groupname列执行的相同步骤:唯一的区别是您必须选择EmployeeID，Employee Name，LoginName列作为脚本组件中的输入并在比较中使用ID列而不是Groupname列
    • Repeat the same steps done with Groupname Column : with a single difference that is you have to choose the EmployeeID, Employee Name, LoginName columns as Input in the Script Component and Use the ID Column instead of Groupname column in the comparaison
    1. 您必须添加平面文件源，查找转换， OLEDB目标

    1. 在查找转换组件中，选择Groups表作为查找表

    1. In The LookUp Transformation Component select Groups Table as a Lookup table

    映射GroupName列并获取Group ID作为输出

    1. 在错误输出配置

    如下

    注意:GroupID必须是一个身份(在sql server中设置)

    Note: GroupID must be an Identity (set it in sql server)

    您必须执行与3个数据流任务"解决方案相同的步骤，但不必在Group和Employee中添加2个数据流任务，而只需在添加MultiCast组件以复制流程.然后，对于第一个流程，使用与Employee数据流任务中使用的相同的Script Component和OLEDB Destination，对于第二个流程，使用与Group相关的Script Component和OLEDB Destination.

    You have to do the same steps as the 3 Data Flow Tasks solution, but instead of adding 2 Data Flow Tasks to Group and Employee, just add one Data Flow Task, and after the Flat File Source add a MultiCast component to duplicate the Flow. Then for the first flow use the same Script Component and OLEDB Destination used in the Employee Data Flow Task, and for the second flow use the Script Component and OLEDB Destination related to Group.

    有很多方法可以通过T-SQL命令将平面文件导入SQL

    There are many method to import Flat file to SQL via T-SQL commands

    假定Microsoft ACE OLEDB的安装版本为Microsoft.ACE.OLEDB.12.0，并且csv文件位置为C:\abc.csv

    Assuming that the installed version of Microsoft ACE OLEDB is Microsoft.ACE.OLEDB.12.0 and that the csv file location is C:\abc.csv

    1. 首先将数据导入到员工和组表中 

    INSERT INTO [GROUP]
    ([Group Name])
SELECT 
    [Group Name] 
FROM 
    OPENROWSET
        (
            'Microsoft.ACE.OLEDB.12.0','Text;Database=C:\;IMEX=1;','SELECT * FROM abc.csv'
        ) t


INSERT INTO [Employee]
    ([Employee Number],[Employee Name],[LoginName])
SELECT 
    [Employee Number],[Employee Name],[LoginName] 
FROM 
    OPENROWSET
        (
            'Microsoft.ACE.OLEDB.12.0','Text;Database=C:\;IMEX=1;','SELECT * FROM abc.csv'
        ) t

  • 导入Employee_Group数据 

    INSERT INTO [EmployeeGroup]
    ([Employee Number],[GroupID])
SELECT 
    t1.[Employee Number],t2.[GroupID]
FROM 
    OPENROWSET
        (
            'Microsoft.ACE.OLEDB.12.0','Text;Database=C:\;IMEX=1;','SELECT * FROM abc.csv'
        ) t1 INNER JOIN GROUP t2 ON t1.[Group Name] = T2.[Group Name]

    • 带有Microsoft文本驱动程序的OPENROWSET

    1. 首先将数据导入到员工和组表中 

    INSERT INTO [GROUP]
    ([Group Name])
SELECT 
    [Group Name] 
FROM 
    OPENROWSET
        (
            'MSDASQL',
            'Driver={Microsoft Text Driver (*.txt; *.csv)};
            DefaultDir=C:\;',
            'SELECT * FROM abc.csv'
        ) t


INSERT INTO [Employee]
    ([Employee Number],[Employee Name],[LoginName])
SELECT 
    [Employee Number],[Employee Name],[LoginName] 
FROM 
    OPENROWSET
        (
            'MSDASQL',
            'Driver={Microsoft Text Driver (*.txt; *.csv)};
            DefaultDir=C:\;',
            'SELECT * FROM abc.csv'
        ) t

  • 导入Employee_Group数据 

    INSERT INTO [EmployeeGroup]
    ([Employee Number],[GroupID])
SELECT 
    t1.[Employee Number],t2.[GroupID]
FROM 
    OPENROWSET
        (
            'MSDASQL',
            'Driver={Microsoft Text Driver (*.txt; *.csv)};
            DefaultDir=C:\;',
            'SELECT * FROM abc.csv'
        ) t1 INNER JOIN GROUP t2 ON t1.[Group Name] = T2.[Group Name]

    • 注意:您可以将数据导入到临时表中，然后查询该表，以避免多次连接到csv文件

    有很多方法可以将csv文件导入到SQL Server，您可以检查以下链接以获取更多信息.

    There are many method to import csv files to SQL server, you can check the following links for additional informations.

    • Four Easy Ways to Import CSV Files to SQL Server with PowerShell
    • How to import data from .csv in SQL Server using PowerShell?
    • OPENROWSET (Transact-SQL)
    • T-SQL – Read CSV files using OpenRowSet
    • Import error using Openrowset

    这篇关于从CSV文件填充SQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

    查看全文
    相关文章
    数据库最新文章
    热门教程
    热门工具
    登录 关闭
    扫码关注1秒登录
    发送“验证码”获取 | 15天全站免登陆