CSV Bulk insert and Delete

Written by teddymeu, March 4th, 2007

Hi Guys, since I've done nothing but ask questions these last few weeks to get my first application up and running I thought it was about time to share the wealth and help out a newbie like me since you all did the same, thanks for all that replied to me.

This code will save a csv file to a dir, you can then use this to bulk insert the information to a specific table in your DB, this is done with asp.net vb and sql, I'm using an mdf for this. I also gave admins the ability to delete the info in there before they actually import the csv.

Code: ( text )

<script runat="server">
Sub UploadButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
'Save the uploaded file to an "Uploads" directory
' that already exists in the file system of the
' currently executing ASP.NET application.
Dim saveDir As String = "\Data\"
' Get the physical file system path for the currently
' executing application.
Dim appPath As String = Request.PhysicalApplicationPath
' Before attempting to save the file, verify
' that the FileUpload control contains a file.
If (FileUpload1.HasFile) Then
Dim savePath As String = appPath + saveDir + FileUpload1.FileName
' Call the SaveAs method to save the
' uploaded file to the specified path.
' Will overwrite existing file of same name
FileUpload1.SaveAs(savePath)
' Notify the user that the file was uploaded successfully.
UploadStatusLabel.Text = "Your file was uploaded successfully."
Else
' Notify the user that a file was not uploaded.
UploadStatusLabel.Text = "You did not specify a file to upload."
End If
End Sub
Sub DeleteAllButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim SQLConn As New System.Data.SqlClient.SqlConnection
SQLConn.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirector y|\PostOffice.mdf;Integrated Security=True;User Instance=True"
Dim strInsert As String
Dim cmdInsert As SqlCommand
strInsert = "DELETE FROM LocalPostOffice WHERE(ID > 0)"
cmdInsert = New SqlCommand(strInsert, SQLConn)
SQLConn.Open()
cmdInsert.ExecuteNonQuery()
SQLConn.Close()
End Sub
Sub ImportButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim SQLConn As New System.Data.SqlClient.SqlConnection
SQLConn.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirector y|\PostOffice.mdf;Integrated Security=True;User Instance=True"
Dim strInsert As String
Dim cmdInsert As SqlCommand
strInsert = "BULK INSERT [LocalPostOffice] FROM [c:\Inetpub\wwwroot\MerlinLocalPostOfficeApp\Data\c sv.txt] WITH (FIELDTERMINATOR = ',')"
cmdInsert = New SqlCommand(strInsert, SQLConn)
SQLConn.Open()
cmdInsert.ExecuteNonQuery()
SQLConn.close()
End Sub
</script>

Hope this helps someone in the future.

0 Comments Posted ( Post your comment )

Post your comment about this Howto