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:
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--------]
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;
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