Solved

SQLBulkCopy Upload ,csv file

Posted on 2014-11-26
3
242 Views
Last Modified: 2014-11-26
Hi

I found the following code at the link below to use the SQLBulkUpload class to move data between two databases
What similar code would I use to rather upload a .csv file to one SQL database table?

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy(v=vs.110).aspx

Imports System.Data.SqlClient

Module Module1
    Sub Main()
        Dim connectionString As String = GetConnectionString()

        ' Open a connection to the AdventureWorks database.
        Using sourceConnection As SqlConnection = _
           New SqlConnection(connectionString)
            sourceConnection.Open()

            ' Perform an initial count on the destination table.
            Dim commandRowCount As New SqlCommand( _
            "SELECT COUNT(*) FROM dbo.BulkCopyDemoMatchingColumns;", _
                sourceConnection)
            Dim countStart As Long = _
               System.Convert.ToInt32(commandRowCount.ExecuteScalar())
            Console.WriteLine("Starting row count = {0}", countStart)

            ' Get data from the source table as a SqlDataReader.
            Dim commandSourceData As SqlCommand = New SqlCommand( _
               "SELECT ProductID, Name, ProductNumber " & _
               "FROM Production.Product;", sourceConnection)
            Dim reader As SqlDataReader = commandSourceData.ExecuteReader

            ' Open the destination connection. In the real world you would  
            ' not use SqlBulkCopy to move data from one table to the other    
            ' in the same database. This is for demonstration purposes only.
            Using destinationConnection As SqlConnection = _
                New SqlConnection(connectionString)
                destinationConnection.Open()

                ' Set up the bulk copy object.  
                ' The column positions in the source data reader  
                ' match the column positions in the destination table,  
                ' so there is no need to map columns.
                Using bulkCopy As SqlBulkCopy = _
                  New SqlBulkCopy(destinationConnection)
                    bulkCopy.DestinationTableName = _
                    "dbo.BulkCopyDemoMatchingColumns"

                    Try
                        ' Write from the source to the destination.
                        bulkCopy.WriteToServer(reader)

                    Catch ex As Exception
                        Console.WriteLine(ex.Message)

                    Finally
                        ' Close the SqlDataReader. The SqlBulkCopy
                        ' object is automatically closed at the end
                        ' of the Using block.
                        reader.Close()
                    End Try
                End Using

                ' Perform a final count on the destination table
                ' to see how many rows were added.
                Dim countEnd As Long = _
                    System.Convert.ToInt32(commandRowCount.ExecuteScalar())
                Console.WriteLine("Ending row count = {0}", countEnd)
                Console.WriteLine("{0} rows were added.", countEnd - countStart)

                Console.WriteLine("Press Enter to finish.")
                Console.ReadLine()
            End Using
        End Using
    End Sub

    Private Function GetConnectionString() As String
        ' To avoid storing the sourceConnection string in your code,  
        ' you can retrieve it from a configuration file.  
        Return "Data Source=(local);" & _
            "Integrated Security=true;" & _
            "Initial Catalog=AdventureWorks;"
    End Function
End Module
0
Comment
Question by:murbro
3 Comments
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 250 total points
Comment Utility
just load up your csv in a datatable and you will be able to use bulkcopy.

Also check http://emoreau.com/Entries/Articles/2010/12/Using-ADONet-SQL-Bulk-Copy-feature.aspx
0
 
LVL 32

Assisted Solution

by:it_saige
it_saige earned 250 total points
Comment Utility
Just like what Eric States.  Take your CSV file and make it into a datatable, then bulkcopy your datatable into the database.  When I did this, I created some classes and methods, perhaps this code can help you:

UpdateTable method -
	''' <summary>Uses a file (zipcodes.csv) to bulk add entries into the Zip Code table.</summary>
	Public Sub UpdateTable()
		Dim connectionString As String = "your connection string here"
		Using connection As New SqlConnection(connectionString)
			connection.Open()

			' Perform an initial count on the destination table.
			Dim commandRowCount As New SqlCommand("SELECT COUNT(*) FROM dbo.ZipCodes;", connection)
			Dim countStart As Long = Convert.ToInt32(commandRowCount.ExecuteScalar())
			Console.WriteLine("Starting row count = {0}", countStart)

			Dim tr As TextReader = New StreamReader(File.Open(String.Format("{0}\version\zipcodes.csv", Application.StartupPath), FileMode.Open, FileAccess.Read, FileShare.Read))
			' Create a table with some rows.
			'DataTable newZipCodes = MakeTable();
			Dim newZipCodes As List(Of ZipCode) = CsvParser.Parse(tr, True)
			Dim t As New DataTable()
			t = newZipCodes.ToDataTable()
			t.Columns.Remove("ID")
			' Create the SqlBulkCopy object.
			' Note that the column positions in the source DataTable 
			' match the column positions in the destination table so 
			' there is no need to map columns. 
			Using bulkCopy As New SqlBulkCopy(connectionString, SqlBulkCopyOptions.TableLock) With {.DestinationTableName = "dbo.ZipCodes"}
				For Each dc As DataColumn In t.Columns
					bulkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName)
				Next
				Try
					' Write from the source to the destination.
					bulkCopy.WriteToServer(t)
				Catch ex As Exception
					' Log exceptions
				End Try
			End Using
			' Perform a final count on the destination 
			' table to see how many rows were added.
			Dim countEnd As Long = Convert.ToInt32(commandRowCount.ExecuteScalar())
			Console.WriteLine("Ending row count = {0}", countEnd)
			Console.WriteLine("{0} rows were added.", countEnd - countStart)
			Console.WriteLine("Press Enter to finish.")
			Console.ReadLine()
		End Using
	End Sub
End Module

Open in new window


CsvParser.vb -
Public Class CsvParser
	Public Shared Function Parse(ByVal data As String, ByVal headers As Boolean) As List(Of ZipCode)
		Return Parse(New StringReader(data), headers)
	End Function

	Public Shared Function Parse(ByVal data As String) As List(Of ZipCode)
		Return Parse(New StringReader(data))
	End Function

	Public Shared Function Parse(ByVal stream As TextReader) As List(Of ZipCode)
		Return Parse(stream, False)
	End Function

	Public Shared Function Parse(ByVal stream As TextReader, ByVal headers As Boolean) As List(Of ZipCode)
		Dim table As New List(Of ZipCode)()
		Dim csv As New CsvStream(stream)
		Dim row As String() = csv.GetNextRow()
		If row Is Nothing Then
			Return Nothing
		End If

		If headers Then
			For Each header As String In row
				If header IsNot Nothing AndAlso header.Length > 0 Then
					AddTableHeader(header, table)
				Else
					'table.Columns.Add(GetNextColumnHeader(table), typeof(string));
				End If
			Next

			' Create an array for DataColumn objects.
			'DataColumn[] keys = new DataColumn[1];
			'keys[0] = table.Columns[0];
			'table.PrimaryKey = keys;

			row = csv.GetNextRow()
		End If

		While row IsNot Nothing
			If row(1).Length < 5 Then
				Dim temp As String = row(1)
				While temp.Length < 5
					temp = "0" & temp
				End While
				row(1) = temp
			End If
			'while (row.Length > table.Columns.Count)
			'{
			'     table.Columns.Add(GetNextColumnHeader(table), typeof(string));
			'}
			Dim item As New ZipCode() With { _
			 .ID = Convert.ToInt32(row(0)), _
			 .Code = row(1), _
			 .CodeType = row(2), _
			 .City = row(3), _
			 .State = row(4), _
			 .LocationType = row(5), _
			 .Country = row(6), _
			 .LocationText = row(7), _
			 .Location = row(8), _
			 .Decommisioned = Convert.ToBoolean(row(9)) _
			}

			table.Add(item)
			row = csv.GetNextRow()
		End While
		Return table
	End Function

	Private Shared Function GetNextColumnHeader(ByVal table As DataTable) As String
		Dim c As Integer = 1
		While True
			Dim h As String = "Column" & System.Math.Max(System.Threading.Interlocked.Increment(c), c - 1)
			If Not table.Columns.Contains(h) Then
				Return h
			End If
		End While
		Return Nothing
	End Function

#Region "Parse Row"
	Private Shared Sub AddTableHeader(ByVal header As String, ByVal table As List(Of ZipCode))
		Dim val As String = String.Empty
		Select Case header.ToUpper()
			Case "RECORDNUMBER"
				'table.Columns.Add(new DataColumn() { DataType = Type.GetType("System.Int32"), ColumnName = "ID", AutoIncrement = true });
				Exit Select
			Case "ZIPCODE"
				'table.Columns.Add(new DataColumn() { DataType = Type.GetType("System.Int32"), ColumnName = "ZipCode" });
				Exit Select
			Case "ZIPCODETYPE"
				'table.Columns.Add(new DataColumn() { DataType = Type.GetType("System.String"), ColumnName = "ZipCodeType" });
				Exit Select
			Case "CITY"
				'table.Columns.Add(new DataColumn() { DataType = Type.GetType("System.String"), ColumnName = "City" });
				Exit Select
			Case "STATE"
				'table.Columns.Add(new DataColumn() { DataType = Type.GetType("System.String"), ColumnName = "State" });
				Exit Select
			Case "LOCATIONTYPE"
				'table.Columns.Add(new DataColumn() { DataType = Type.GetType("System.String"), ColumnName = "LocationType" });
				Exit Select
			Case "COUNTRY"
				'table.Columns.Add(new DataColumn() { DataType = Type.GetType("System.String"), ColumnName = "Country" });
				Exit Select
			Case "LOCATIONTEXT"
				'table.Columns.Add(new DataColumn() { DataType = Type.GetType("System.String"), ColumnName = "LocationText" });
				Exit Select
			Case "LOCATION"
				'table.Columns.Add(new DataColumn() { DataType = Type.GetType("System.String"), ColumnName = "Location" });
				Exit Select
			Case "DECOMMISIONED"
				'table.Columns.Add(new DataColumn() { DataType = Type.GetType("System.Boolean"), ColumnName = "Decommisioned" });
				Exit Select
			Case Else
				Exit Select
		End Select
	End Sub

	Private Shared Sub ParseRow(ByVal row As String, ByVal currentHeader As String, ByRef dump As ZipCode)
		Dim val As String = String.Empty
		Select Case currentHeader
			Case "RECORDNUMBER"
				dump.ID = Convert.ToInt32(row)
				Exit Select
			Case "ZIPCODE"
				dump.Code = row
				Exit Select
			Case "ZIPCODETYPE"
				dump.CodeType = row
				Exit Select
			Case "CITY"
				dump.City = row
				Exit Select
			Case "STATE"
				dump.State = row
				Exit Select
			Case "LOCATIONTYPE"
				dump.LocationType = row
				Exit Select
			Case "COUNTRY"
				dump.Country = row
				Exit Select
			Case "LOCATIONTEXT"
				dump.LocationText = row
				Exit Select
			Case "LOCATION"
				dump.Location = row
				Exit Select
			Case "DECOMMISIONED"
				dump.Decommisioned = Convert.ToBoolean(row)
				Exit Select
			Case Else
				Exit Select
		End Select
	End Sub
#End Region

	Public Class CsvStream
		Implements IDisposable
		Private stream As TextReader
		Private EOS As Boolean
		Private EOL As Boolean
		Private buffer As Char() = New Char(4095) {}
		Private pos As Integer
		Private length As Integer

		Public Sub New(ByVal s As TextReader)
			stream = s
		End Sub

		Public Function GetNextRow() As String()
			Dim row As New ArrayList()
			While True
				Dim item As String = GetNextItem()
				If item Is Nothing Then
					Return If(row.Count = 0, Nothing, DirectCast(row.ToArray(GetType(String)), String()))
				End If
				row.Add(item)
			End While
			Return Nothing
		End Function

		Private Function GetNextItem() As String
			If EOL Then
				' previous item was last in line, start new line
				EOL = False
				Return Nothing
			End If

			Dim quoted As Boolean = False
			Dim predata As Boolean = True
			Dim postdata As Boolean = False
			Dim item As New StringBuilder()

			While True
				Dim c As Char = GetNextChar(True)
				If EOS Then
					Return If(item.Length > 0, item.ToString(), Nothing)
				End If

				If (postdata OrElse Not quoted) AndAlso c = ","c Then
					' end of item, return
					Return item.ToString()
				End If

				If (predata OrElse postdata OrElse Not quoted) AndAlso (c = ControlChars.Lf OrElse c = ControlChars.Cr) Then
					' we are at the end of the line, eat newline characters and exit
					EOL = True
					If c = ControlChars.Cr AndAlso GetNextChar(False) = ControlChars.Lf Then
						' new line sequence is 0D0A
						GetNextChar(True)
					End If
					Return item.ToString()
				End If

				If predata AndAlso c = " "c Then
					' whitespace preceeding data, discard
					Continue While
				End If

				If predata AndAlso c = """"c Then
					' quoted data is starting
					quoted = True
					predata = False
					Continue While
				End If

				If predata Then
					' data is starting without quotes
					predata = False
					item.Append(c)
					Continue While
				End If

				If c = """"c AndAlso quoted Then
					If GetNextChar(False) = """"c Then
						' double quotes within quoted string means add a quote
						item.Append(GetNextChar(True))
					Else
						' end-quote reached
						postdata = True
					End If
					Continue While
				End If

				' all cases covered, character must be data
				item.Append(c)
			End While
			Return Nothing
		End Function

		Private Function GetNextChar(ByVal eat As Boolean) As Char
			If pos >= length Then
				length = stream.ReadBlock(buffer, 0, buffer.Length)
				If length = 0 Then
					EOS = True
					Return ControlChars.NullChar
				End If
				pos = 0
			End If
			If eat Then
				Return buffer(System.Math.Max(System.Threading.Interlocked.Increment(pos), pos - 1))
			Else
				Return buffer(pos)
			End If
		End Function

		Public Sub Dispose() Implements IDisposable.Dispose
			Dispose(True)
			GC.SuppressFinalize(Me)
		End Sub

		Protected Overridable Sub Dispose(ByVal disposing As Boolean)
			If disposing Then
				If stream IsNot Nothing Then
					stream.Dispose()
					stream = Nothing
				End If
			End If
		End Sub

		Protected Overrides Sub Finalize()
			Try
				Dispose(False)
			Finally
				MyBase.Finalize()
			End Try
		End Sub
	End Class
End Class

Open in new window


ZipCode.vb -
Public Class ZipCode
	Public Property ID() As Integer
	Public Property Code() As String
	Public Property CodeType() As String
	Public Property City() As String
	Public Property State() As String
	Public Property LocationType() As String
	Public Property Country() As String
	Public Property LocationText() As String
	Public Property Location() As String
	Public Property Decommisioned() As Nullable(Of Boolean)
End Class

Open in new window


Extensions.vb -
Module Extensions
	<System.Runtime.CompilerServices.Extension()> _
	Public Function ToDataTable(Of T)(ByVal data As IList(Of T)) As DataTable
		Dim props As PropertyDescriptorCollection = TypeDescriptor.GetProperties(GetType(T))
		Dim table As New DataTable()
		For i As Integer = 0 To props.Count - 1
			Dim prop As PropertyDescriptor = props(i)
			If prop.PropertyType.IsGenericType AndAlso prop.PropertyType.GetGenericTypeDefinition().Equals(GetType(Nullable(Of ))) Then
				Dim nprop As Type = prop.PropertyType
				nprop = Nullable.GetUnderlyingType(nprop)
				table.Columns.Add(prop.Name, nprop)
			Else
				table.Columns.Add(prop.Name, prop.PropertyType)
			End If
		Next

		Dim values As Object() = New Object(props.Count - 1) {}

		For Each item As T In data
			For i As Integer = 0 To values.Length - 1
				values(i) = props(i).GetValue(item)
			Next
			table.Rows.Add(values)
		Next
		Return table
	End Function
End Module

Open in new window


-saige-
0
 

Author Closing Comment

by:murbro
Comment Utility
Thanks very much
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now