Read CSV Files, then Display in Table format using variables

[choudhmh]

I need to read and display data from a csv file in a table format. I then will need to use these data to calculate different values.
This is how the CSV file looks:

<table> <tr> <td>9 carat gold</td><td>11.87</td> </tr><tr> <td>18 carat gold</td><td>23.73</td> </tr><tr> <td>Silver</td><td>0.49</td> </tr><tr> <td>Platinum</td><td>27.52</td> </tr> </table>

I need to display these on a table format on the webpage (able to do that as gridview) then use individual values from the table to work out calculation i.e 10 grams of silver should be worked out by multiplying 0.49 (retrieved from the table) by 10.

That's the code I have so far for GridView:

If File.Exists("metals.csv") Then
 Dim data As String() = File.ReadAllLines("metals.csv")
 
 Dim dt As New DataTable()
 Dim col As String() = data(0).Split(",")
 For Each s As String In col
 
 dt.Columns.Add(s, GetType(String))
 
 Next
 For i As Integer = 0 To data.Length - 1
 
 Dim row As String() = data(i).Split(",")
 
 dt.Rows.Add(row)
 
 Next
 
 GridView1.DataSource = dt
 
 GridView1.DataBind()
 End If

Is there another way of doing this say by using arrays to assign variables for each metal. I need to make sure they are displayed in a table format.

Thank you

Regards,
Mahmud

[Frinavale]

The CSV file example you posted is not in Comma Separated Values (CSV) format.

If your file actually looks like what you posted you can simply use Response.Write to write the entirety of the file contents contents into the output stream and it will look like a table (because the file contains an HTML table).

Regardless of the fact that your file doesn't match the type that you are describing, what you are doing is fine: converting the file into an in-memory .NET data table and assigning it as the source of a GridView which displays as a table in the browser.

You an also bind the source of GridViews to sources that are not a DataTable type though.

For example, you can bind to a list or array of custom Metal objects if you would like.

Like, if you had the following custom Metal object:

Public Class Metal
  Public Property Name As String
  Public Property BasePriceInGrams As Double
  Public Property AmountInGrams As Double
 
  Public Readonly Property Cost As Double
    Get
      Return BasePriceInGrams * AmountInGrams
    End Get
  End Property
 
  Public Sub New(ByVal metalName As String, ByVal basePrice As Double)
    Me.Name = metalName
    Me.BasePriceInGrams = basePrice
    Me.AmountInGrams = 10 ' defaulting to 10 grams
  End Sub
End Class

You could read from the file and create Metal objects with the data that you retrieve to then set them as the source of your GridView.

' ...code that you have for reading the file...
' But instead of using a DataTable as the source
' you can use a list of Metal objects
 
Dim metalsSource As New List(Of Metal)
 
For i As Integer = 0 To data.Length - 1
 Dim data As String() = data(i).Split(",")
 metalsSource.Add(New Metal(data(0), data(1))
Next
 
GridView1.DataSource = metalsSource 
GridView1.DataBind()

Now because the Metal object has additional properties (the Cost property in particular), you can bind to them in the UI/ASP markup for the GridView to display the "cost" of 10 grams for each metal.

-Frinny

[choudhmh]

Bit confused with your model.
The data is in a CSV file I only made it into a table so you understand.
I have 2 CSV, first one should display the metal type and the price in pounds. These item should be shown in DataGrid.
The next CSV has rates in Euro and Dollar, I should use (read) the rates and use (multiply) by the pound rate value in the DataGrid column 2 and get a total value on column 3 & 4:

Imports System.IO
Imports System.Data
 
Partial Class Precious
    Inherits System.Web.UI.Page
 
 
 
    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If File.Exists("C:\Users\Mahmud\Documents\Visual Studio 2013\WebSites\WebSite2\metals.csv") Then
            Dim data As String() = File.ReadAllLines("C:\Users\Mahmud\Documents\Visual Studio 2013\WebSites\WebSite2\metals.csv")
 
            If File.Exists("C:\Users\Mahmud\Documents\Visual Studio 2013\WebSites\WebSite2\rates.csv") Then
                Dim data2 As String() = File.ReadAllLines("C:\Users\Mahmud\Documents\Visual Studio 2013\WebSites\WebSite2\rates.csv")
 
                Dim dt As New DataTable()
                Dim col As String() = data(0).Split(",")
                dt.Columns.Add("Metal Name")
                dt.Columns.Add("Rate in Pound")
                dt.Columns.Add("Rate in Euro")
                dt.Columns.Add("Rate in Dollar")
 
                For Each s As String In col
 
                    dt.Columns.Add(s, GetType(String))
 
                Next
                For i As Integer = 0 To data.Length - 1
 
                    Dim row As String() = data(i).Split(",")
 
                    dt.Rows.Add(row)
 
                Next
 
                GridView1.DataSource = dt
 
                GridView1.DataBind()
            End If
 
        End If
    End Sub
End Class

This is what I get so far:

[Frinavale]

Like I said, there is nothing wrong with doing what you were originally... you asked for a different approach though.

What is confusing about the model?
The model class takes care of the calculations for you.
If you need to read from multiple files you can and then take the information that you gather from the files to populate the class with the data required to do the processing for you.

Then in the ASP markup code for the UI for the GridView you can bind to the properties or execute methods to do the calculations that you need to do to accomplish your task. If you don't use the model approach, it will still be a good idea to use the ASP markup for the GridView to do calculations during data binding.

-Frinny

[choudhmh]

Sorry its a project for college, so a bit confused on what your trying to communicate.
The model is not doing any calculation at the moment, its just reading data from the CSV (metal) file.
I need to assign calculation on the Euro & Dollar column using the figures from the pound column and rates from the CSV file.
If you can direct me with some codes that will be great; all files are attached with the website I'm trying to create.

Thank you in advance
Regards

[Frinavale]

In your course, have you learned about how the GridView can bind to Objects?

It takes all of the properties of the object and uses them as column headers by default.

This is why, in my demonstration Metal class, I added the ReadOnly Cost property.

It does calculations to display how much 10 grams of each metal would cost.

What you would do is add additional properties for currency conversions that you wish to display.

Check out the MSDN documentation for the GridView class

There you will find links to documentation for all of the GridView's properties, methods, and events along with additional articles that will show you how to use the control.

The MSDN Library contains this kind of documentation for all of the .NET controls available (some documentation is better than others and you may still need clarification after reading it)

Anyway, try my example in your code to see what it's like binding to the list of Objects instead of a DataTable.

Objects really do offer a lot more functionality :)




-Frinny

[choudhmh]

Tried your code with the metal as object. Keep on getting errors:

Imports System.IO
Imports System.Data
 
Public Class Metal
    Inherits System.Web.UI.Page
 
    Public Property Name As String
    Public Property BasePriceInGrams As Double
    Public Property AmountInGrams As Double
 
    Public ReadOnly Property Cost As Double
        Get
            Return BasePriceInGrams * AmountInGrams
        End Get
    End Property
 
    Public Sub New(ByVal metalName As String, ByVal basePrice As Double)
        Me.Name = metalName
        Me.BasePriceInGrams = basePrice
        Me.AmountInGrams = 10 ' defaulting to 10 grams
 
 
    End Sub
    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
 
        If File.Exists("C:\Users\Mahmud\Documents\Visual Studio 2013\WebSites\WebSite2\metals.csv") Then
            Dim data As String() = File.ReadAllLines("C:\Users\Mahmud\Documents\Visual Studio 2013\WebSites\WebSite2\metals.csv")
 
            If File.Exists("C:\Users\Mahmud\Documents\Visual Studio 2013\WebSites\WebSite2\rates.csv") Then
                Dim data2 As String() = File.ReadAllLines("C:\Users\Mahmud\Documents\Visual Studio 2013\WebSites\WebSite2\rates.csv")
 
 
 
                Dim metalsSource As New List(Of Metal)
                For i As Integer = 0 To data.Length - 1
                    data = data(i).Split(",")
                    metalsSource.Add(New Metal(data(0), data(1)))
 
                Next
 
                GridView1.DataSource = metalsSource
                GridView1.DataBind()
 
            End If
        End If
    End Sub
 
 
End Class

Is there another way of setting the column as object. The stuff you linked does not really give much info for this project.

[Frinavale]

Well, that's probably because you renamed your page to "Metal"....
The Metal class should not be your page class.

Change your class name back to the name of the page that the ASP code is expecting it to be and your errors will probably go away (in the future please post the error messages because otherwise people here have to guess what is going on) and remove the code that is supposed to be in the metal class out of your page code.

After you do that, add a new class to your project.
Call that class Metal.
Add the code for the metal class to the file you created.

-Frinny

[choudhmh]

Done what you asked me to do.
an IndexoutOfRangeException is thrown on this code:
metalsSource.Add(New Metal(data(0), data(1)))

It seems metalsSource cannot retrieved the data. What else can be done

[Frinavale]

You need to make sure that the data array has at least 2 items in it. I assumed that it would always have 2 values...

Put a check in for the length of data before accessing indexes 0 and 1.

-Frinny

[choudhmh]

Tried adding check for length but still the same issues as mentioned above arises:

If File.Exists("C:\Users\Mahmud\Documents\Visual Studio 2013\WebSites\WebSite2\metals.csv") Then
            Dim data As String() = File.ReadAllLines("C:\Users\Mahmud\Documents\Visual Studio 2013\WebSites\WebSite2\metals.csv")
 
            If File.Exists("C:\Users\Mahmud\Documents\Visual Studio 2013\WebSites\WebSite2\rates.csv") Then
                Dim data2 As String() = File.ReadAllLines("C:\Users\Mahmud\Documents\Visual Studio 2013\WebSites\WebSite2\rates.csv")
 
                Dim dt As New DataTable()
 
 
 
                Dim metalsSource As New List(Of Metal)
                For i As Integer = 0 To data.Length - 1
                    data = data(i).Split(",")
                    Console.WriteLine(data.Length)
                    metalsSource.Add(New Metal(data(0), data(1)))
 
                Next
 
                GridView1.DataSource = metalsSource
                GridView1.DataBind()
            End If
 
        End If

Any other suggestions. Thank you so far for all your help

[Frinavale]

I'm not seeing the length checking.

Actually I see quite a few problems in the above code. Please see the comments in the following code:

'The following line could cause a problem 
'if the file doesn't exist
'You are properly checking if it exists in the following line
'And after the following line you are populating
'a variable called "data2" with the lines
'....you are using "data" to hold the strings
' that result from using the Split function on
' the lines from the file.
'I moved this into the appropriate place
 
'Dim data As String() = File.ReadAllLines("C:\Users\Mahmud\Documents\Visual Studio 2013\WebSites\WebSite2\metals.csv")
 
 
 
'In the line below: 
'  consider using the Server.MapPath method to retrieve 
'  the path to the file instead of hard coding the path because
'  this won't work on a live server
 
If File.Exists("C:\Users\Mahmud\Documents\Visual Studio 2013\WebSites\WebSite2\rates.csv") Then
 
 ' data2 holds all of the lines from the file
 ' you should be looping through this array 
  Dim data2 As String() = File.ReadAllLines("C:\Users\Mahmud\Documents\Visual Studio 2013\WebSites\WebSite2\rates.csv")
 
 
  'Dim dt As New DataTable() <- you don't need this if you are using the list
 
  Dim metalsSource As New List(Of Metal)
  'You are using the same variable for too many things
  'it is causing problems. 
  'You should be looping through "data2" instead of data
  'For i As Integer = 0 To data.Length - 1
  '  data = data(i).Split(",")
  '  'Console.WriteLine(data.Length) <- this makes no sense in an asp.net application
  '  metalsSource.Add(New Metal(data(0), data(1)))
  '
  'Next
 
  'You should be looping through "data2" instead
  For i As Integer = 0 To data2.Length - 1
    Dim data = data2(i).Split(",")
    If data.Length = 2 Then 'Right here is where you check the length of the data array before accessing the indexes!
      metalsSource.Add(New Metal(data(0), data(1)))
    End If
  Next
  ' You really should consider naming data2 something meaningful
  ' consider renaming it to "linesFromFile" or something....
 
  'Likewise you should reconsider naming data to something meaningful
  ' consider renaming it to "lineContents" or something
 
  GridView1.DataSource = metalsSource
  GridView1.DataBind()
End If

The following is the above posted code with no comments and properly named variables:

 
If File.Exists(Server.MapPath("~/metals.csv")) Then 'Checking if the file exists before accessing it
  'I'm not sure of your directory structure
  'so you may need to replace the above and below Server.MapPaths with
  'your hard coded value for now
  'it was: File.Exists("C:\Users\Mahmud\Documents\Visual Studio 2013\WebSites\WebSite2\metals.csv")
 
  Dim fileLines As String() = File.ReadAllLines(Server.MapPath("~/metals.csv")) 'Retrieving all lines from file
 
  Dim metalsSource As New List(Of Metal) 'Will be used as the source for the GridView
 
  For i As Integer = 0 To fileLines.Length - 1 'Looping through the file lines to retrieve the content from them and populate the Metal objects with data
    Dim lineContent As String() = fileLines(i).Split(",") 'Retrieving the content from the line 
    If lineContent.Length = 2 Then 'Checking the length of the content to ensure that it is what we expect it to be before accessing the content to avoid errors/crashing
      metalsSource.Add(New Metal(lineContent(0), Ctype(lineContent(1),Double))) 'Creating a new Metal object, populated with the data from the file, and adding it to the list which is the source for the grid
    End If
  Next
 
  GridView1.DataSource = metalsSource 'Setting the source of the GridView
  GridView1.DataBind() 'Binding to the source
End If

It just makes a lot more sense when variable names are meaningful.

-Frinny

[choudhmh]

Now having issues with this line
Gridview1.Databind()

It throws an exception stating object reference not set to an instance.
Had to physically add in the path to the csv file instead of the servermap

[Frinavale]

Well I have no idea what you are doing to get a null reference exception if you have what I do. I don't know what your asp markup is for your page or what else you have in there that might be causing your null reference exception.

I don't usually do this because most people (including myself) either can't download sample projects or do not like to download attached example projects, but I have attached a sample project for you to grab.

Anyways, for those who cannot download the sample project I'm going to post the content of the project here.


This is the metals.csv file (located in the root of the project structure):

9 carat gold,11.87
18 carat gold,23.73
Silver,0.49
Platinum,27.52

And this is the rates.csv file, also located in the root of my project structure:

USD,1.15325238
EUR,1.4218
GBP,1.80402381

(Please note that I am Canadian, so I am 'assuming' the base cost is in Canadian dollars and the other exchange rates are based off the exchange rate with the Canadian dollar as of end of day yesterday)


My Metal class is as follows:

Public Class Metal
    Private _name As String
    Private _basePriceInGrams As Double
    Private _amountInGrams As Double
 
    Private _usdRate As Double
    Private _euroRate As Double
    Private _gbpRate As Double
 
    Public Property Name() As String
        Get
            Return _name
        End Get
        Set(ByVal value As String)
            _name = value
        End Set
    End Property
    Public Property BasePriceInGrams() As Double
        Get
            Return _basePriceInGrams
        End Get
        Set(ByVal value As Double)
            _basePriceInGrams = value
        End Set
    End Property
    Public Property AmountInGrams() As Double
        Get
            Return _amountInGrams
        End Get
        Set(ByVal value As Double)
            _amountInGrams = value
        End Set
    End Property
 
    Public Property UsdRate() As Double
        Get
            Return _usdRate
        End Get
        Set(ByVal value As Double)
            _usdRate = value
        End Set
    End Property
    Public Property EuroRate() As Double
        Get
            Return _euroRate
        End Get
        Set(ByVal value As Double)
            _euroRate = value
        End Set
    End Property
    Public Property GBPRate() As Double
        Get
            Return _gbpRate
        End Get
        Set(ByVal value As Double)
            _gbpRate = value
        End Set
    End Property
 
 
    Public ReadOnly Property CanadianCost() As Double
        Get
            Return BasePriceInGrams * AmountInGrams
        End Get
    End Property
    Public ReadOnly Property USACost() As Double
        Get
            Return BasePriceInGrams * AmountInGrams * UsdRate
        End Get
    End Property
    Public ReadOnly Property EUROCost() As Double
        Get
            Return BasePriceInGrams * AmountInGrams * EuroRate
        End Get
    End Property
    Public ReadOnly Property GBPCost() As Double
        Get
            Return BasePriceInGrams * AmountInGrams * GBPRate
        End Get
    End Property
 
 
 
    Public Sub New(ByVal metalName As String, ByVal basePrice As Double)
        Me.Name = metalName
        Me.BasePriceInGrams = basePrice
        Me.AmountInGrams = 10 ' defaulting to 10 grams
    End Sub
End Class

Notice how my Metal class now has properties that let me get and set the exchange rates?

Notice how the Metal class also has properties that return the cost of each metal by using the exchange rates? These properties do all of the calculations so that the GridView can display them in the table.

Recall that the GridView, by default, will display all Public Properties as column headers in the table displayed on the screen. If you don't want something to display in the Grid (say what the exchange rates are) you have to do something to fix that.

Some options to only display the Properties that you want are to:

• Change the scope of the property that you want to hide to "Friend" or "Private" but if you use private you will have to find a different way to set the values
• Change the Properties in to methods (Subs/Functions) because methods are not displayed in grids
• Not depend on the the default behaviour of the GridView and add the specific columns that you want to display

All of this information and MUCH more is in the links that I previously posted about the GridView control.


In my project, there is only one .aspx page in the project called "Default.aspx"

The following is the ASP markup for the Default.aspx file:

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" />
    </div>
    </form>
</body>
</html>

And the following is the .vb server-side code for the Default.aspx.vb file:

Imports System
Imports System.IO
Imports System.Collections.Generic
 
Partial Class _Default
    Inherits System.Web.UI.Page
 
    Protected Sub form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles form1.Load
        Dim metalsSource As New List(Of Metal) 'Will be used as the source for the GridView
 
 
        If File.Exists(Server.MapPath("~/metals.csv")) Then 'Checking if the file exists before accessing it
            Dim metalsFileLines As String() = File.ReadAllLines(Server.MapPath("~/metals.csv")) 'Retrieving all lines from file
            For i As Integer = 0 To metalsFileLines.Length - 1 'Looping through the file lines to retrieve the content from them and populate the Metal objects with data
                Dim metalLineContent As String() = metalaFileLines(i).Split(",") 'Retrieving the content from the line 
                If metalLineContent.Length = 2 Then 'Checking the length of the content to ensure that it is what we expect it to be before accessing the content to avoid errors/crashing
                    metalsSource.Add(New Metal(metalLineContent(0), CType(metalLineContent(1), Double))) 'Creating a new Metal object, populated with the data from the file, and adding it to the list which is the source for the grid
                End If
            Next
        End If
 
        If File.Exists(Server.MapPath("~/rates.csv")) Then 'Checking if the file exists before accessing it
            Dim exchangeRateFileLines As String() = File.ReadAllLines(Server.MapPath("~/rates.csv")) 'Retrieving all lines from file
            For i As Integer = 0 To exchangeRateFileLines.Length - 1 'Looping through the file lines to retrieve the content from them and populate the Metal objects with data
                Dim exchangeRateLineContent As String() = exchangeRateFileLines(i).Split(",") 'Retrieving the content from the line 
                If exchangeRateLineContent.Length = 2 Then 'Checking the length of the content to ensure that it is what we expect it to be before accessing the content to avoid errors/crashing
                    For Each m In metalsSource
                        Select Case exchangeRateLineContent(0)
                            Case "USD"
                                m.UsdRate = CType(exchangeRateLineContent(1), Double)
                            Case "EUR"
                                m.EuroRate = CType(exchangeRateLineContent(1), Double)
                            Case "GBP"
                                m.GBPRate = CType(exchangeRateLineContent(1), Double)
                        End Select
                    Next
                End If
            Next
        End If
 
        GridView1.DataSource = metalsSource 'Setting the source of the GridView
        GridView1.DataBind() 'Binding to the source
    End Sub
End Class

Notice how I am creating Metal object instances based on what I retrieve from the metal.csv file.

Also notice how I am populating the Metal object instances with the exchange rates from the rates.csv file.


I have no errors when I run my code.

Usually the experts here will work with your current solution to find the best answers based on what you provide. Everyone's coding styles, concepts, approaches, and ways-of-thinking are different.


With that in mind, if you want to achieve the same thing using DataTables as an exercise we can look into what you would need to do to achieve that.

In particular, because DataTables do not have functionality that will do things like "apply an exchange rate", you would have to do the calculations when you retrieve the data from the files to store into the DataTable at the time of population... or you would have to change the GridView so that it does not use the default column-generation behaviour to create your own Template Columns that use ASP calls to do the calculations upon binding to the data to display it... or you would have to over write the row data binding events to do calculations at that point.


Once you try this out post back here and I will help you to understand anything that is confusing about it.

-Frinny

[choudhmh]

Thank you very much for the code. Sorry been away from coding for a week concentrating on the theory aspect of the course.
The code works perfectly.
When it data binds it also displays the rates for each currency.
tried amending the code so the databind is not shown instead it just reads it and calculates EURO AND Dollar value. Is there a way of doing this?

Again thank you very much for showing me the rope.

Kind Regards

[Frinavale]

As I mentioned before, there are several options to only display the Properties that you want to:

• Change the scope of the property that you want to hide to "Friend" or "Private" but if you use private you will have to find a different way to set the values
• Change the Properties in to methods (Subs/Functions) because methods are not displayed in grids
• Not depend on the the default behaviour of the GridView and add the specific columns that you want to display

The first (and easiest) thing I suggest you try is setting the properties in the Metal class for the Exchange Rates to Friend instead of Public.


-Frinny

[choudhmh]

Thank you its functions.
I also databinded the name of each item to a dropdown list.

 DropDownList1.DataSource = metalsSource
        DropDownList1.DataTextField = "Name"
        DropDownList1.DataBind()

Now say if I choose Silver from the dropdown list, how will I display on a say textbox the price of Silver in one of the currency rate say in Euro or Dollar
As all data will be on a grid how do I get the row value for the silver from the different rates that's been calculated

[Frinavale]

Have you learned about the ASP.NET Page Life Cycle?

It is really important to understand this concept.

Right now the code we have is doing a data bind on controls the Page Load event but we should move that to the Page PreRender event to avoid losing information about what the user wants to do.

You can still create the metals data source in the Page Load event and you can still set the data source of your controls to the metals data source in that event...but you should hold off on calling DataBind until the PreRender event or else you could lose data that the user has indicated for editing purposes.


You need to implement a method that handles the DropDownList1's SelectedIndexChanged event. In that event you can retrieve the selected item based on the index provided and set the Text property of the text box to the value that you want to display.

Here is an example of a method that would handle the DropDownList1's SelectedIndexChanged event:

Sub DropDownList1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles DropDownList1.SelectedIndexChanged
 
   Dim selectedMetal = DropDownList1.SelectedItem
 
   ' you have the selected metal so you can now display what you want in a text box
   ' if you have a text box called txt_metalPriceDetails on your page 
   ' you would do the following
 
   txt_metalPriceDetails.Text = selectedMetal.EUROCost.ToString
 
 End Sub

-Frinny