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

Question

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

performance sqlbulkcopy sql-server vb.net

Question

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

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

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.

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;

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

Khairul

View more on Stack Overflow

S. Adam Nissley · Accepted Answer · 3/20/2015 5:05:56 PM

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

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

Question

Fastest Entity Framework Extensions

Popular Answer

Performance

Expression Evaluator