[RESOLVED] Importing CSV to SQL
I am trying to import a CSV file to SQL Server 2005. The CSV file has several fields, but I am not sure of the best way to import them. This is what I have so far.
Code:
Private Sub BackgroundWorker1_DoWork(ByVal sender As System.Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork
Try
Dim filename As String = TextBox1.Text
Dim fields As String()
Dim delimiter As String = ","
Using parser As New TextFieldParser(filename)
parser.SetDelimiters(delimiter)
While Not parser.EndOfData
' Read in the fields for the current line
fields = parser.ReadFields()
' Add code here to use data in fields variable.
End While
End Using
Catch ex As ApplicationException
End Try
End Sub
This is the first time I am doing this sort of work. Thus far, I have connected the table that I need to connect to on the SQL server and I have setup an insert command for it.
Code:
INSERT INTO dbo.WendysDailyPayroll
(UniqueID, Company, FKStoreID, DateOfBusiness, SSN, LawsonID, TotalHours)
VALUES (@UniqueID,@Company,@FKStoreID,@DateOfBusiness,@SSN,@LawsonID,@TotalHours)
I know that I must setup the fields for the insert command, but I am not sure how to do it. I want to do it all in one shot.
I have to insert the following fields from the CSV file: CompanyNo, StoreNo, Effdate, SSN, LawsonID, TotalHours.
Does anybody have any suggestions on how to do this? Your input is much appreciated.