Convert CSV column to Decimal for filtering in VB.NEt DataTable

TheCalicoTree
TheCalicoTree used Ask the Experts™
on
I am reading a CSV file into a DataSet and then creating a datatable so that I can apply a filter to the rows.
My problem is that I need to apply a price range based filter but all columns are held as strings.
Can I convert a column to decimal for the purpose of this filter?  I thought I could CAST the value in my select statement but this does not work.


Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2008

Commented:
You could add a calculated expression column, that converts the data to decimal, and filter by that column value.

Author

Commented:
Thanks TheLearnedOne.  I have added the column but am geting an error when trying to add the rows.  Is the column added to the start or end of the table?  I tried to see if you could position it but to no avail.
This is my code:
Dim dsCSV As DataSet = Nothing
        If Not _Items Is Nothing AndAlso _Items.Count > 0 Then
            Dim flds() As FieldInfo = _Items.Item(0).GetType.GetFields(BindingFlags.Instance Or BindingFlags.Public)
            If Not flds Is Nothing Then
                dsCSV = New DataSet
                dsCSV.Tables.Add(TableName)
                For Each fld As FieldInfo In flds
                    'Add Column Names
                    With dsCSV.Tables.Item(TableName)
                        .Columns.Add(fld.Name, fld.FieldType)
                    End With
                Next
                Dim expr As String = "Convert(Price, 'System.Int32')"
                dsCSV.Tables(TableName).Columns.Add("PriceDecimal", GetType(Decimal), expr)

                'Populate Table with Data
                For Each itm As CSVFileItem In _Items

                    dsCSV.Tables.Item(TableName).Rows.Add(itm.ToStringArray)

                Next
            End If
        End If
Most Valuable Expert 2012
Top Expert 2008

Commented:
What error are you getting when you add rows?  Do you have rows where the value is blank?  If you do, then it will cause a problem.  I would try with an expression like "Val(Price)"

Author

Commented:
Sorry, the error is:
Input string not in the correct format.

This occurs at l dsCSV.Tables.Item(TableName).Rows.Add(itm.ToStringArray)

There are no blank rows but the top row contains header information and would therefore fail a conversion to INT32..
Most Valuable Expert 2012
Top Expert 2008

Commented:
If there is one row with non-numeric data, then Convert(Price, "System.Int32") won't work, so try Val(Price).

Author

Commented:
I got the error:
The expression contains undefined function call Val().
Looks as though Val() is not compatible with the DataRow filter method.  All suggestions point to CONVERT.  Is there anyway to ignore the first line of the table or no write the first line of data to the table in the first place?

Most Valuable Expert 2012
Top Expert 2008

Commented:
"I am reading a CSV file into a DataSet"
How are you doing this?

Author

Commented:
I have a function:

  Dim ds As DataSet = csv.ToDataSet("MyTable")

Public Function ToDataSet(ByVal TableName As String) As DataSet
        Dim dsCSV As DataSet = Nothing
        If Not _Items Is Nothing AndAlso _Items.Count > 0 Then
            Dim flds() As FieldInfo = _Items.Item(0).GetType.GetFields(BindingFlags.Instance Or BindingFlags.Public)
            If Not flds Is Nothing Then
                dsCSV = New DataSet
                dsCSV.Tables.Add(TableName)
                For Each fld As FieldInfo In flds
                    'Add Column Names
                    With dsCSV.Tables.Item(TableName)
                        .Columns.Add(fld.Name, fld.FieldType)
                    End With
                Next
                Dim expr As String = "Val(Price)"
                dsCSV.Tables(TableName).Columns.Add("PriceDecimal", GetType(Decimal), expr)

                'Populate Table with Data
                For Each itm As CSVFileItem In _Items

                    dsCSV.Tables.Item(TableName).Rows.Add(itm.ToStringArray)

                Next
            End If
        End If
        Return dsCSV
    End Function

The associated methods are attached in the code snippet.

Thanks

Imports System.Reflection
Imports System.IO
Imports System.Data

Public Class CSVFile
#Region " Private Variables "
    Private _CSVFile As CSVFileItem, _Delimiter As String, _Items As New CSVFileItemCollection
#End Region

#Region " Private Methods "
    Private Sub FromString(ByVal Line As String, ByVal Delimiter As String)
        ' Dim CSVFileElements() As String = Line.Split(Delimiter)
        Dim CSVFileElements() As String = Split(Line, ",", Chr(34), True)
        If Not CSVFileElements Is Nothing Then
            Dim fldInfo() As FieldInfo = _CSVFile.GetType.GetFields(BindingFlags.Instance Or BindingFlags.Public)
            If Not fldInfo Is Nothing Then
                Dim itm As System.ValueType = CType(_CSVFile, System.ValueType)
                For fldIdx As Integer = 0 To CSVFileElements.Length - 1
                    fldInfo(fldIdx).SetValue(itm, CSVFileElements(fldIdx).Replace(Chr(34), ""))
                Next
                _CSVFile = itm
            Else
                Dim itms As Integer = 0
                If Not fldInfo Is Nothing Then
                    itms = fldInfo.Length
                End If
                Throw New Exception("Invalid line definition.")
            End If
        Else
            Dim itms As Integer = 0
            If Not CSVFileElements Is Nothing Then
                itms = CSVFileElements.Length
            End If
            Throw New Exception("Invalid line definition.")
        End If
    End Sub

    Private Function Split(ByVal expression As String, ByVal delimiter As String, ByVal qualifier As String, ByVal ignoreCase As Boolean) As String()
        Dim _QualifierState As Boolean = False
        Dim _StartIndex As Integer = 0
        Dim _Values As New System.Collections.ArrayList

        For _CharIndex As Integer = 0 To expression.Length - 1
            If Not qualifier Is Nothing AndAlso String.Compare(expression.Substring(_CharIndex, qualifier.Length), qualifier, ignoreCase) = 0 Then
                _QualifierState = Not _QualifierState
            ElseIf Not _QualifierState AndAlso Not delimiter Is Nothing AndAlso String.Compare(expression.Substring(_CharIndex, delimiter.Length), delimiter, ignoreCase) = 0 Then
                _Values.Add(expression.Substring(_StartIndex, _CharIndex - _StartIndex))
                _StartIndex = _CharIndex + 1
            End If
        Next

        If _StartIndex < expression.Length Then
            _Values.Add(expression.Substring(_StartIndex, expression.Length - _StartIndex))
        End If

        Dim _returnValues(_Values.Count - 1) As String
        _Values.CopyTo(_returnValues)
        Return _returnValues
    End Function
#End Region
#Region " Public Methods "
    Public Sub New()
        _CSVFile = New CSVFileItem
    End Sub

    Public Sub New(ByVal Line As String, ByVal Delimiter As String)
        _CSVFile = New CSVFileItem
        _Delimiter = Delimiter
        FromString(Line, Delimiter)
    End Sub

    Public Sub New(ByVal Filename As String)
        LoadFile(Filename)
    End Sub

    Public Sub LoadFile(ByVal Filename As String)
        Dim inFile As StreamReader = File.OpenText(Filename)
        Do While inFile.Peek > 0
            FromString(inFile.ReadLine, ",")
            _Items.Add(_CSVFile)
            _CSVFile = Nothing

        Loop
        inFile.Close()
    End Sub


#End Region

#Region " Public Functions "
    Public Function ToDataSet(ByVal TableName As String) As DataSet
        Dim dsCSV As DataSet = Nothing
        If Not _Items Is Nothing AndAlso _Items.Count > 0 Then
            Dim flds() As FieldInfo = _Items.Item(0).GetType.GetFields(BindingFlags.Instance Or BindingFlags.Public)
            If Not flds Is Nothing Then
                dsCSV = New DataSet
                dsCSV.Tables.Add(TableName)
                For Each fld As FieldInfo In flds
                    'Add Column Names 
                    With dsCSV.Tables.Item(TableName)
                        .Columns.Add(fld.Name, fld.FieldType)
                    End With
                Next
                Dim expr As String = "Val(Price)"
                dsCSV.Tables(TableName).Columns.Add("PriceDecimal", GetType(Decimal), expr)

                'Populate Table with Data 
                For Each itm As CSVFileItem In _Items

                    dsCSV.Tables.Item(TableName).Rows.Add(itm.ToStringArray)

                Next
            End If
        End If
        Return dsCSV
    End Function


#End Region

#Region " Public Properties "
    Public ReadOnly Property Item() As CSVFileItem
        Get
            Return _CSVFile
        End Get
    End Property

    Public ReadOnly Property Items() As CSVFileItemCollection
        Get
            Return _Items
        End Get
    End Property


#End Region

End Class

Open in new window

Most Valuable Expert 2012
Top Expert 2008

Commented:
You can use OLEDB to read the CSV or Excel file:

 http://weblogs.asp.net/donxml/pages/24908.aspx
Public Shared Function GetCSVFile(pathName As String, fileName As String) As DataSet
	Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & pathName & ";Extended Properties=Text;"
	Dim commandText As String = "SELECT * FROM " & fileName
	Using adapter As New OleDbDataAdapter(commandText, connectionStringExcelCommand)
  	     Dim ds As New DataSet()
	     adapter.Fill(ds)
      	     Return ds
         End Using
End Function

Open in new window

Author

Commented:
OK but I'm not sure that this will help me.  My methods use the column headings so that I can bind them to the repeater.  <%#DataBinder.Eval(Container, "DataItem.PropertyName")%>
I have tried using this oledb method but it doesn't seem to return any data.  
Most Valuable Expert 2012
Top Expert 2008

Commented:
The only way that I can see is to make a copy of the header row, and then remove it, so that you can use Convert.
Thanks for the help.  In the end I have used this:

 Dim expr As String = "Convert(Price, 'System.Int32')"

                dsCSV.Tables(TableName).Columns.Add("PriceDecimal", GetType(Decimal), expr)

And then coded to skip the header row, checking for a string before adding the CSV row to my dataset.

   Public Sub LoadFile(ByVal Filename As String)
        Dim inFile As StreamReader = File.OpenText(Filename)
        Do While inFile.Peek > 0
            FromString(inFile.ReadLine, ",")
            If Not _CSVFile.BranchID = "BranchID" Then
                _Items.Add(_CSVFile)
            End If
            _CSVFile = Nothing

        Loop
        inFile.Close()
    End Sub

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial