Friday, July 24, 2009

Upload a CSV file into a Gridview

You have a CSV file and you would like to display it in a Gridview.

1.gif

This bit of code was born out of a desire to allow user's to see their CSV contents inside of a Gridview. Once the user had a chance to see it they could click a button to move to the next step.

This following code will take the CSV and turn it into a dataTable that will be able then bound to a Gridview. This makes it easy to further manipulate your data or even saving the dataTable to session for future use.

THE CODE:

  1. Drop a FileUpload control on your canvas as well as a Button for submitting the file and a Gridview control.
  2. Then add the System.IO, your Variables, and the Click Event to the Button.

    Imports System.IO

    Partial Public Class _Default
    Inherits System.Web.UI.Page

    Dim streamRdr As StreamReader
    Dim fileLines As String()
    Dim line As String

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click

    If FileUpload1.HasFile Then

    Dim fileExt As String

    fileExt = System.IO.Path.GetExtension(FileUpload1.FileName)

    If fileExt = ".csv" Or fileExt = ".CSV" Then

    'Make variables needed
    Dim objStream As Stream
    Dim fileLen As Integer

    'Get the length of the file.
    fileLen = FileUpload1.PostedFile.ContentLength

    'Create a byte array to hold the contents of the file.
    Dim Input(fileLen) As Byte

    'Move byte array into a stream, then start it at beginning and pass it to method.
    objStream = FileUpload1.PostedFile.InputStream
    objStream.Read(Input, 0, fileLen)
    objStream.Position = 0

    'Manage File
    importToDG(objStream)

    End If

    End If

    End Sub

    NOTICE above, we verify that we are getting a CSV file in and then we create a stream to hold the incoming file.

    We then get the length, create a byte array to temporarily hold the contents, move it into the stream, then reset the stream's position.

    Finally we call our next method 'importToDG' and send in the stream.

  3. Create the import Sub - you will need to create a Datatable, a row, and the columns that match your CSV. You need to put your CSV info into a datatable for the Gridview can bind to it.

    Private Sub importToDG(ByVal fileStream As Stream)

    streamRdr = New StreamReader(FileStream)

    Dim myDTable As DataTable = New DataTable
    Dim myDRow As DataRow = myDTable.NewRow
    Dim schoolID, distID, schoolName, Address, Address2, Phone, City, State, Zip _
    As New DataColumn
    schoolID.ColumnName =
    "SchoolID"
    distID.ColumnName = "DistrictID"
    Phone.ColumnName = "Phone"
    schoolName.ColumnName = "schoolName"
    Address.ColumnName = "Address"
    Address2.ColumnName = "Address2"
    City.ColumnName = "City"
    State.ColumnName = "State"
    Zip.ColumnName = "Zip"
    myDTable.Columns.Add(schoolID)
    myDTable.Columns.Add(distID)
    myDTable.Columns.Add(schoolName)
    myDTable.Columns.Add(Address)
    myDTable.Columns.Add(Address2)
    myDTable.Columns.Add(Phone)
    myDTable.Columns.Add(City)
    myDTable.Columns.Add(State)
    myDTable.Columns.Add(Zip)

    'Now set the streamreader back to beginning.
    streamRdr.DiscardBufferedData()
    streamRdr.BaseStream.Seek(0, SeekOrigin.Begin)
    streamRdr.BaseStream.Position = 0

    Dim i As Integer = 0 'Skip the first row.

    While Not streamRdr.EndOfStream

    line = streamRdr.ReadLine 'Read first line
    fileLines = line.Split(",") 'Split the line up by the delimeter

    If i > 0 Then
    myDRow.ItemArray = fileLines
    myDTable.Rows.Add(myDRow)
    myDRow = myDTable.NewRow
    End If

    i += 1

    End While

    GridView1.DataSource = myDTable
    GridView1.DataBind()

    End Sub

    End Class

Take notice that above I skipped the first row of my file. That is because our CSV's had column names as the first row... DO NOT DO THIS
unless you need to skip the row for a reason.


See full detail: http://www.vbdotnetheaven.com/UploadFile/tvance929/106182009115344AM/1.aspx