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)
Popular Answer
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