将数据从文本文件导入 MS SQL Server 的最佳方法 [英] Best method for importing data from text file into MS SQL Server

问题描述

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

In need of your opinions. Currently developing an application in 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

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

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).

第一列由

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

第二列

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

第三列是

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

最后一列是

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

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

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.

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

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;

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

  推荐答案

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

  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
  

  这篇关于将数据从文本文件导入 MS SQL Server 的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！