Upload CSV file to a Datagrid then to a Database

[simbro]

I tried doing some searches to see if this has been asked yet, but I
did not see anything. What I am looking to do is create an application
in VB.NET that will allow users to select a .CSV file from their
computer and then display it in a datagrid on the web. Once they
verify that is the file that they want to upload, I then want them to
be able to upload it to a database. Are there any examples of this?

[shriop]

It's a pretty common task, but everyone has different requirements, so
their end implementation is slightly different each time. First, the
web control that you'd use is different depending on if you're using
.net 1.0 or 1.1 vs 2.0. In pre 2.0, you drag in a File Field control
from the HTML section of your toolbox in design view, then right click
on the control and select "Run As Server Control". Then, on the code
side, you have the file as File1.PostedFile by default, with the
InputStream property, or the SaveAs method. With 2.0, you drag in a
FileUpload control from the Standard section of your toolbox. It starts
out as a server control by default. On the code side, you can get to
the same properties as the previous way with the control named
FileUpload1 by default, and FileUpload1.PostedFile, or there's just a
FileUpload1.SaveAs method. At this point, you need to decide how you
want to save the csv data from the file during the display to the user
step. The most common ways would be to save the file off to the
harddrive of the server, or save it in a DataTable and then save that
DataTable in either the ViewState or the Session. The harddrive route
has security issues that you'll have to overcome because by default,
ASP .Net is not able to save files directly to the harddrive. The
DataTable route is best for small files. The Session route will use up
memory on the server, and the ViewState route could make the page take
a while to display the grid. To save the file to the harddrive, you
just use the SaveAs method and pass it a file name. For either route,
you'll want to create a DataTable to feed the DataSource property of
the grid. If you've saved the file off to the harddrive, you can just
pass in the filename to the CsvReader constructor, then call ReadToEnd,
which will return the DataTable. If you're just going to save the
DataTable without saving to the harddrive, you can just pass
PostedFile.InputStream into the CsvReader constuctor, specifying
Encoding.Default and comma in the additional parameters needed, then
call the same ReadToEnd. How to get the file into the database again
depends on how you saved the data from the uploaded file, the .net
version that you're using, and the setup of your database. Ideally,
you'll be using .net 2.0, which will let you make use of the
SqlBulkCopy class to pipe the data directly to SQL Server. If you've
saved the file off to the harddrive, you can pass the filename into the
CsvDataReader constructor, and pass it into the SqlBulkCopy object. If
you've gone the DataTable route, you'll have some issues converting the
DataTable over from all strings into the respective data types in the
database. Give me some answers to all the questions above and I can
help further. Here's some links to relevant discussions and code
samples.

Upload CSV file to SQL Server using FileUpload, CsvDataReader, and
SqlBulkCopy in .Net 2.0
http://www.csvreader.com/csv_samples.php

http://groups.google.com/group/CSVChat/browse_frm/thread/ef3453b906600cf1

http://groups.google.com/group/CSVChat/browse_frm/thread/731fb967d8837c2c

http://groups.google.com/group/CSVChat/browse_frm/thread/38ad567dceea62db

Bruce Dunwiddie

[simbro]

well, here are some of the answers. I was going to upload this into a
datagrid to have it shown on the web to make sure that what was chosen
was the file that they wanted to upload. From there, I have an upload
button that will take the file that they are uploading and that was
displayed and load into the database. We are using .NET 2.0.

[shriop]

See how far this code gets you. I've got a GridView named Grid, a
FileUpload control named FileUpload, and 2 buttons named UploadButton
and ImportButton. I have a file named users.csv that looks like this:

id,user
1,bruce
2,bob

I've got a table defined by this:

CREATE TABLE [user] (
[id] [int] NOT NULL ,
[user] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

And here's the code:

Imports System.Data.SqlClient
Imports System.IO
Imports System.Text

Imports DataStreams.Csv

Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub UploadButton_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles UploadButton.Click
If FileUpload.PostedFile.FileName.Length > 0 Then
Dim count As Integer
Dim dataBuffer(1024) As Byte
Dim dataHolder As New MemoryStream
Using FileUpload.PostedFile.InputStream
Do
count =
FileUpload.PostedFile.InputStream.Read(dataBuffer, 0,
dataBuffer.Length)
dataHolder.Write(dataBuffer, 0, count)
Loop While count > 0
End Using
Session("uploadedData") = dataHolder.ToArray
Using reader As CsvReader = New CsvReader(New
MemoryStream(CType(Session("uploadedData"), Byte())), Encoding.Default)
Grid.DataSource = reader.ReadToEnd(True)
End Using
ImportButton.Enabled = True
Grid.Visible = True
Grid.DataBind()
End If
End Sub

Protected Sub ImportButton_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles ImportButton.Click
Using bulkCopy As New SqlBulkCopy("Data Source=.;Initial
Catalog=pubs;Persist Security Info=True;User ID=sa;Password=")
Using dataReader As New CsvDataReader(New
MemoryStream(CType(Session("uploadedData"), Byte())), Encoding.Default)
dataReader.Settings.HasHeaders = True

dataReader.Columns.Add("int", "id")
dataReader.Columns.Add("varchar", "user")

bulkCopy.DestinationTableName = "[user]"

bulkCopy.WriteToServer(dataReader)
End Using
End Using
Session.Remove("uploadedData")
Grid.Visible = False
ImportButton.Enabled = False
End Sub
End Class

This will temporarily save the file to session state while the data is
displayed. Let me know what questions you have from here.

Bruce Dunwiddie