By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,040 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

CSV Bulk insert and Delete

P: 19
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.


Expand|Select|Wrap|Line Numbers
  1. <script runat="server">    
  2.     Sub UploadButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
  3.         'Save the uploaded file to an "Uploads" directory
  4.         ' that already exists in the file system of the 
  5.         ' currently executing ASP.NET application.
  6.         Dim saveDir As String = "\Data\"
  7.  
  8.         ' Get the physical file system path for the currently
  9.         ' executing application.
  10.         Dim appPath As String = Request.PhysicalApplicationPath
  11.  
  12.         ' Before attempting to save the file, verify
  13.         ' that the FileUpload control contains a file.
  14.         If (FileUpload1.HasFile) Then
  15.             Dim savePath As String = appPath + saveDir + FileUpload1.FileName
  16.  
  17.             ' Call the SaveAs method to save the 
  18.             ' uploaded file to the specified path.
  19.             ' Will overwrite existing file of same name
  20.             FileUpload1.SaveAs(savePath)
  21.  
  22.             ' Notify the user that the file was uploaded successfully.
  23.             UploadStatusLabel.Text = "Your file was uploaded successfully."
  24.  
  25.         Else
  26.             ' Notify the user that a file was not uploaded.
  27.             UploadStatusLabel.Text = "You did not specify a file to upload."
  28.         End If
  29.  
  30.     End Sub
  31.  
  32.     Sub DeleteAllButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
  33.         Dim SQLConn As New System.Data.SqlClient.SqlConnection
  34.         SQLConn.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\PostOffice.mdf;Integrated Security=True;User Instance=True"
  35.         Dim strInsert As String
  36.         Dim cmdInsert As SqlCommand
  37.         strInsert = "DELETE FROM LocalPostOffice WHERE(ID > 0)"
  38.         cmdInsert = New SqlCommand(strInsert, SQLConn)
  39.         SQLConn.Open()
  40.         cmdInsert.ExecuteNonQuery()
  41.         SQLConn.Close()
  42.     End Sub
  43.  
  44.     Sub ImportButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
  45.         Dim SQLConn As New System.Data.SqlClient.SqlConnection
  46.         SQLConn.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\PostOffice.mdf;Integrated Security=True;User Instance=True"
  47.         Dim strInsert As String
  48.         Dim cmdInsert As SqlCommand
  49.         strInsert = "BULK INSERT [LocalPostOffice] FROM [c:\Inetpub\wwwroot\MerlinLocalPostOfficeApp\Data\csv.txt] WITH (FIELDTERMINATOR = ',')"
  50.         cmdInsert = New SqlCommand(strInsert, SQLConn)
  51.         SQLConn.Open()
  52.         cmdInsert.ExecuteNonQuery()
  53.         SQLConn.close()
  54.     End Sub
  55.  
  56.   </script>
Hope this helps someone in the future.
Mar 4 '07 #1
Share this Article
Share on Google+