-
Nov 9th, 2013, 06:05 AM
#1
Thread Starter
Member
Import records from CSV file into SQL Server 2008 R2 using VB .Net 2010
I need to import records from a comma delimited CSV file into an existing table in SQL Server R2 database using Visual Basic .Net 2010. Existing records in the table are to be deleted prior to import. I have been able to create an in-memory temporary DataTable and populate the records from CSV file using TextFieldParser. I have checked it by binding the in-memory DataTable to a DataGridView. But I am clueless in the second part i.e. how to insert records into the SQL table from the in-memory DataTable.
I have done the following:
Code:
Dim TextFileReader As New TextFieldParser("C:\csvtosql\StockVB\VFPFiles\ExpSysusers.csv")
TextFileReader.TextFieldType = FileIO.FieldType.Delimited
TextFileReader.SetDelimiters(",")
Dim TextFileTable As DataTable = Nothing
Dim Column As DataColumn
Dim Row As DataRow
Dim UpperBound As Int32
Dim ColumnCount As Int32
Dim CurrentRow As String()
Dim intCount As Integer
intCount = 0
CurrentRow = TextFileReader.ReadFields() ' Ignore the header
While Not TextFileReader.EndOfData
Try
CurrentRow = TextFileReader.ReadFields()
If Not CurrentRow Is Nothing Then
''# Check if DataTable has been created
If TextFileTable Is Nothing Then
TextFileTable = New DataTable("TextFileTable")
''# Get number of columns
UpperBound = CurrentRow.GetUpperBound(0)
''# Create new DataTable
For ColumnCount = 0 To UpperBound
Column = New DataColumn()
Column.DataType = System.Type.GetType("System.String")
Column.ColumnName = "Column" & ColumnCount
Column.Caption = "Column" & ColumnCount
Column.ReadOnly = True
Column.Unique = False
TextFileTable.Columns.Add(Column)
Next
End If
intCount = intCount + 1
Row = TextFileTable.NewRow
For ColumnCount = 0 To UpperBound
Row("Column" & ColumnCount) = CurrentRow(ColumnCount).ToString
Next
TextFileTable.Rows.Add(Row)
End If
Catch ex As Exception
MsgBox("Line " & ex.Message & "is not valid and will be skipped.")
End Try
End While
TextFileReader.Dispose()
DataGridView1.DataSource = TextFileTable
MsgBox(intCount & " Records Read.")
Can anybody please help/guide me??
-
Nov 9th, 2013, 06:43 AM
#2
Re: Import records from CSV file into SQL Server 2008 R2 using VB .Net 2010
Use a data adapter to save all the data in the DataTable to the database. For an example, follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data.
-
Nov 9th, 2013, 09:48 PM
#3
Thread Starter
Member
Re: Import records from CSV file into SQL Server 2008 R2 using VB .Net 2010
I tried the following code to read the records from the DataTable and insert into the SQL Table. But it seems that only the first record is being added.
Code:
For Each TextFileTableDataRow As DataRow In TextFileTable.Rows
Dim Column0 As String = TextFileTableDataRow("Column0")
Dim Column1 As String = TextFileTableDataRow("Column1")
Dim Column2 As Int16 = TextFileTableDataRow("Column2")
Dim Column3 As Boolean = TextFileTableDataRow("Column3")
Dim strSqlQry As String = "INSERT INTO Personnel (Operator,OpPassword,SecurityLevel,Active) VALUES (@Operator,@OpPassword,,@SecurityLevel,@Active)"
Dim SqlconnectionString As String = gcconnect
Using connection As New SqlClient.SqlConnection(SqlconnectionString)
Dim cmd As New SqlClient.SqlCommand(strSqlQry, connection)
' create command objects and add parameters
With cmd.Parameters
.AddWithValue("@Operator", Column0)
.AddWithValue("@OpPassword", Column1)
.AddWithValue("@SecurityLevel", Column3)
.AddWithValue("@LoggedOn", Column7)
End With
Dim adapter As New SqlClient.SqlDataAdapter()
adapter.InsertCommand = cmd
'--Update the original SQL table from the datatable
Dim iRowsInserted As Int32 = adapter.Update(TextFileTable)
End Using
Next
I am getting the following error: "Violation of PRIMARY KEY Constraint 'PK_Personnel'. Cannot insert duplicate key in object 'dbo.Personnel'."
-
Nov 9th, 2013, 10:26 PM
#4
Re: Import records from CSV file into SQL Server 2008 R2 using VB .Net 2010
Fastest way is to use the SQLBulkCopy class and the WriteToServer method...
-tg
-
Nov 10th, 2013, 02:37 AM
#5
Re: Import records from CSV file into SQL Server 2008 R2 using VB .Net 2010
 Originally Posted by Eager_Beever
I tried the following code to read the records from the DataTable and insert into the SQL Table. But it seems that only the first record is being added.
Code:
For Each TextFileTableDataRow As DataRow In TextFileTable.Rows
Dim Column0 As String = TextFileTableDataRow("Column0")
Dim Column1 As String = TextFileTableDataRow("Column1")
Dim Column2 As Int16 = TextFileTableDataRow("Column2")
Dim Column3 As Boolean = TextFileTableDataRow("Column3")
Dim strSqlQry As String = "INSERT INTO Personnel (Operator,OpPassword,SecurityLevel,Active) VALUES (@Operator,@OpPassword,,@SecurityLevel,@Active)"
Dim SqlconnectionString As String = gcconnect
Using connection As New SqlClient.SqlConnection(SqlconnectionString)
Dim cmd As New SqlClient.SqlCommand(strSqlQry, connection)
' create command objects and add parameters
With cmd.Parameters
.AddWithValue("@Operator", Column0)
.AddWithValue("@OpPassword", Column1)
.AddWithValue("@SecurityLevel", Column3)
.AddWithValue("@LoggedOn", Column7)
End With
Dim adapter As New SqlClient.SqlDataAdapter()
adapter.InsertCommand = cmd
'--Update the original SQL table from the datatable
Dim iRowsInserted As Int32 = adapter.Update(TextFileTable)
End Using
Next
I am getting the following error: "Violation of PRIMARY KEY Constraint 'PK_Personnel'. Cannot insert duplicate key in object 'dbo.Personnel'."
You don't call Update in a loop. The whole point of a data adapter is to save an entire DataTable worth of changes in one go. Did you read my CodeBank thread? If not, why not? If so, why are you not doing what it does?
-
Nov 10th, 2013, 08:17 AM
#6
Thread Starter
Member
Re: Import records from CSV file into SQL Server 2008 R2 using VB .Net 2010
@jmcilhinney. I am sorry. I did went through your CodeBank link but got confused between handling a DataTable (temporary in-memory table) and a regular (physical) Table in a Database. I re-read the thread again and will now try to follow your lead. Now my aim is to read the DataTable (all records) and insert the records into the table. Hopefully I will be able to solve my problem. I think I am also facing problem due to the presence of a primary key in the target table. But my input DataTable contains unique records only.
Sorry again for being a pain. I am a newbie in VB.Net and also facing health issues (Thyroid induced difficulty in concentration and a little memory lapse).
Thanks again for your patience.
-
Nov 10th, 2013, 08:52 AM
#7
Thread Starter
Member
Re: Import records from CSV file into SQL Server 2008 R2 using VB .Net 2010
@jmcilhinney. I followed your example code #7 in your CodeBank (Retrieving and Saving Data in Databases) and did the following:
Code:
For Each TextFileTableDataRow As DataRow In TextFileTable.Rows
Dim Column0 As String = TextFileTableDataRow("Column0")
Dim Column1 As String = TextFileTableDataRow("Column1")
Dim Column2 As String = TextFileTableDataRow("Column2")
Dim Column3 As Int16 = TextFileTableDataRow("Column3")
Dim Column4 As String = TextFileTableDataRow("Column4")
Dim Column5 As Int16 = TextFileTableDataRow("Column5")
Dim Column6 As Boolean = TextFileTableDataRow("Column6")
Dim Column7 As Boolean = TextFileTableDataRow("Column7")
Dim SqlconnectionString As String = gcconnect
Using connection As New SqlClient.SqlConnection(SqlconnectionString)
Using command As New SqlCommand("INSERT INTO Personnel ([Operator], OpPassword, OpName, SecurityLevel, BriefCode, Branch, Active, LoggedOn) VALUES (@Operator, @OpPassword, @OpName, @SecurityLevel, @BriefCode, @Branch, @Active, @LoggedOn)", connection)
command.Parameters.AddWithValue("@Operator", Column0)
command.Parameters.AddWithValue("@OpPassword", Column1)
command.Parameters.AddWithValue("@OpName", Column2)
command.Parameters.AddWithValue("@SecurityLevel", Column3)
command.Parameters.AddWithValue("@BriefCode", Column4)
command.Parameters.AddWithValue("@Branch", Column5)
command.Parameters.AddWithValue("@Active", Column6)
command.Parameters.AddWithValue("@LoggedOn", Column7)
connection.Open()
command.ExecuteNonQuery()
End Using
End Using
Next
Now my code is working OK, though I know it needs refining. I also noticed that the SQL Table field name "Operator" is a reserved word in Visual Basic and therefore I enclosed it within square braces.
Is there a better approach (more efficient) to do the same??
Thanks again for your patience and guidance.
-
Nov 10th, 2013, 04:43 PM
#8
Re: Import records from CSV file into SQL Server 2008 R2 using VB .Net 2010
That is NOT what you should be doing. You should be following one of the examples in post #3 of that thread. You populate a DataTable with ALL the data and you save it in a single batch. That post in that thread includes an example using a data adapter and another using SqlBulkCopy, which tg recommended earlier. There's no real advantage unless you have a large amount of data though.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|