vb.net 将数据从文本文件导入 MS SQL Server 的最佳方法

Best method for importing data from text file into MS SQL Server

提问by Khairul

In need of your opinions. Currently developing an application in VB.NET.

需要你的意见。目前正在 VB.NET 中开发应用程序。

I have a text file which contains more than one thousand rows. Each rows contains the data needed to be inserted into the database. A sample of a row is as follows:

我有一个包含一千多行的文本文件。每行包含需要插入数据库的数据。一行的样本如下：

0001--------SCOMNET--------0.100---0.105

At first glance, one might figured that each column was separated with a tab but actually each column was separated by blank spaces (I used '-' to denote as blank spaces because somehow could not get SO text editor to show the blank spaces).

乍一看，人们可能认为每列都用制表符分隔，但实际上每列都用空格分隔（我使用“-”表示空格，因为不知何故无法让 SO 文本编辑器显示空格）。

The first column is defined by

第一列定义为

Substring(0, 12) which is the data [0001--------]

second column

第二列

Substring(12, 12) in which the data is [SCOMNET-----] 

third column is

第三列是

Substring(24, 8) in which the data is [---0.100] 

and last column is

最后一列是

Substring(32, 8) in which the data is [---0.105]

My initial though is to extract the lines for the text file and stored in as a list of strings, then while looping, do the separation of the each string item in the list with the SubString() function and insert it one by one until the end of the list. But this will no doubt takes time.

我最初的方法是提取文本文件的行并作为字符串列表存储在其中，然后在循环时，使用 SubString() 函数将列表中的每个字符串项分离并逐一插入，直到列表的结尾。但这无疑需要时间。

In my scenario, how can I take advantage of the SqlBulkCopy? Or if there is any other ways to approach this for a faster insert? Say;

在我的场景中，如何利用 SqlBulkCopy？或者，如果有任何其他方法可以实现更快的插入？说;

• open file
• start loop
  • read line
  • separate each column in the line with substring
  • save in a DataTable
• end loop
• BCP(DataTable)

• 打开文件
• 开始循环
  • 读行
  • 用子字符串分隔行中的每一列
  • 保存在数据表中
• 结束循环
• BCP(数据表)

回答by S. Adam Nissley

This includes a method that may be a more efficient way of reading your text file.

这包括一种可能是更有效地阅读文本文件的方法。

    Sub readFixWidthTextFileIntoSqlTable()

    Dim sqlConn As New SqlConnection("Connection String Goes Here")
    sqlConn.Open()
    Dim sqlComm As New SqlCommand
    sqlComm.Connection = sqlConn
    sqlComm.CommandType = CommandType.Text
    sqlComm.CommandText = "INSERT INTO YourTableNameHere VALUES(@Field1, @Field2, @Field3, @Field4)"

    sqlComm.Parameters.Add("@Field1", SqlDbType.Text)
    sqlComm.Parameters.Add("@Field2", SqlDbType.Text)
    sqlComm.Parameters.Add("@Field3", SqlDbType.Text)
    sqlComm.Parameters.Add("@Field4", SqlDbType.Text)



    Using IFReader As New FileIO.TextFieldParser(FileNameWithPath)
        IFReader.TextFieldType = FileIO.FieldType.FixedWidth
        IFReader.SetFieldWidths(12, 12, 8, 8)

        While Not IFReader.EndOfData
            Dim fields As String() = IFReader.ReadFields

            sqlComm.Parameters("@Field1").Value = fields(0)
            sqlComm.Parameters("@Field2").Value = fields(1)
            sqlComm.Parameters("@Field3").Value = fields(2)
            sqlComm.Parameters("@Field4").Value = fields(3)
            sqlComm.ExecuteNonQuery()
        End While

    End Using

    sqlConn.Close()
End Sub

回答by Ciarán

You've got it pretty much right. This approach is one that I take a lot. Here's a bit of sample code to get you started. It is ONLY an example, there's absolutely no validation or and no consideration for Primary Keys on the table. If you want to review your question with more details of the structure of the destination table then I can make this example much more specific.

你说得很对。这种方法是我经常采用的方法。这里有一些示例代码可以帮助您入门。这只是一个例子，绝对没有验证或没有考虑表上的主键。如果你想用目标表结构的更多细节来回顾你的问题，那么我可以让这个例子更加具体。

 Read_File:

    Dim sFileContents As String = ""

    Using sRead As New StreamReader("e:\ExampleFile.txt")
        sFileContents = sRead.ReadToEnd
    End Using

    Dim sFileLines() As String = sFileContents.Split(vbCrLf)

Connect_To_DB:

    Dim sqlConn As New SqlConnection

    sqlConn.ConnectionString = "Data Source=YourServerName;Initial Catalog=YourDbName;Integrated Security=True"
    sqlConn.Open()

Setup_DataTable:

    Dim ExampleTable As New DataTable
    ExampleTable.Load(New SqlCommand("Select Top 0 * From Example_Table", sqlConn).ExecuteReader)
    'This is not absolutely necessary but avoids trouble with NOT NULL columns (like keys)'
    For Each dcColumn As DataColumn In ExampleTable.Columns : dcColumn.AllowDBNull = True : Next dcColumn

Save_To_DataTable:

    For Each sLine In sFileLines

        Dim ExampleRow As DataRow = ExampleTable.NewRow

        ExampleRow("First_Column_Name") = sLine.Substring(0, 12).TrimEnd
        ExampleRow("Second_Column_Name") = sLine.Substring(12, 12).TrimEnd
        ExampleRow("Third_Column_Name") = sLine.Substring(24, 8).TrimEnd
        ExampleRow("Fourth_Column_Name") = sLine.Substring(32, 8).TrimEnd

        ExampleTable.Rows.Add(ExampleRow)

    Next

Update_Database:

    If ExampleTable.Rows.Count <> 0 Then

        Dim sqlBulk As SqlBulkCopy = New SqlBulkCopy(sqlMvConnection)

        sqlBulk.DestinationTableName = "Example_Table"
        sqlBulk.WriteToServer(ExampleTable)

    End If

Disconnect_From_DB:

    sqlConn.Close()

Also, as commented on above and if you have access to it SSIS will do this in a jiffy.

此外，正如上面所评论的，如果您可以访问它，SSIS 将很快完成此操作。