Software Technology Tips

To insert records from a CSV file to SQL table , we need to  first read the file line by line and add to a datatable , and from the datatable we can update the SQL table.Here is the code sample to update a CSV file containg 3 colums to a SQL table.
[VB.NET CODE STARTS]
 
'--First create a datatable with the same cols as CSV file, the cols order in both should be same
Dim table As New DataTable()
 
table.Columns.Add("ZipCode")
table.Columns.Add("State")
table.Columns.Add("City")
 
'--TextField Parser is used to read the files
Dim parser As New FileIO.TextFieldParser(CSV FILE PATH)
 
parser.Delimiters = New String() {","} ' fields are separated by comma
parser.HasFieldsEnclosedInQuotes = True ' each of the values is enclosed with double quotes
parser.TrimWhiteSpace = True
 
'--First line is skipped , its the header
parser.ReadLine()
 
'-- Add all the rows to datatable
Do Until parser.EndOfData = True
     table.Rows.Add(parser.ReadFields())
Loop
 
'--Create SQL query
Dim strSql As String = "INSERT INTO tblZipCode (ZipCode,State,City) VALUES (@ZipCode,@State,@City)"
 

Dim SqlconnectionString As String = "Data Source=" & SQLServerName & "; Initial Catalog=" & SQLDatabaseName & "; User Id=" & SQLUserName & "; Password=" & SQLPassword & "; Connect Timeout=120;"
 
        Using connection As New SqlClient.SqlConnection(SqlconnectionString)
 
            Dim cmd As New SqlClient.SqlCommand(SQL, connection) ' create command objects and add parameters
            With cmd.Parameters
                .Add("@ZipCode", SqlDbType.VarChar, 15, "ZipCode")
                .Add("@State", SqlDbType.VarChar, 20, "State")
                .Add("@City", SqlDbType.VarChar, 30, "City")
            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(table)
         
        End Using
[VB.NET CODE ENDS]


Related Tags:

VB.NET, CSV

Author: Amrita Dash

VB.NET

Let us Connect!

iso 9001 QA25 Red Herring STPI D&B Fastest Growing SME 2013 Award zinnov Nasscom

This site uses cookies. We respect your privacy.copyright (c) Mindfire Solutions 2007-2015. Login