CSV Bulk insert and Delete
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.
- <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.
|
|
|
|
