Reading data from csv file

Question

Afternoon,

I'm hoping someone can assist me.

I have a combo box which the user can drop down and select a value i.e.

Monday

Tuesday

Wednesday

I also have a spreadsheet which has around 40 rows similar to the below

Column 1 Column 2

Monday 30

Tuesday 31

Wednesday 32

etc

How can i grab the data in column 2 based on what the user has selected in the combo box and this then matches the entry in Column 1?

Many thanks

Simon

Answer 1

Hi

OK, is the user selecting from a ComboBox not less efficient than just scrolling through a DataGridView (watching the UTC time is a good marker for searching).

Anyway, your design so we will go with that. Here is code that may help. It saves the user selected item (timezone to text file and region to xml file) as per the paths/filenames set at top of code (you would need to edit accordingly)

Give this a try. (NOTE: try as a stand alone test using this code with your paths/filenames)

' Form1 with DataGridView1 (empty),
' ComboBox1 (empty),
' Label1 and Label2 (Label2 for result)

Public Class Form1
	Dim dt As New DataTable("Freddy")
	Dim WithEvents BS As New BindingSource

	' need to edit all paths/filenames to suit
	Dim BasePath As String = My.Computer.FileSystem.SpecialDirectories.Desktop

	Dim SourceDataFileXML As String = IO.Path.Combine(BasePath, "TimeZonesAndRegions.xml")

	Dim OutputZoneFile As String = IO.Path.Combine(BasePath, "TimeZone.txt")
	Dim OutputRegionFile As String = IO.Path.Combine(BasePath, "Regions.xml")


	Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing
		dt.WriteXml(SourceDataFileXML)
	End Sub
	Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

		With dt
			.Columns.Add("Zone", GetType(String))
			.Columns.Add("Region", GetType(String))

			If IO.File.Exists(SourceDataFileXML) Then .ReadXml(SourceDataFileXML)

		End With

		BS.DataSource = dt

		DataGridView1.DataSource = BS
		With ComboBox1
			.DataSource = BS
			.DisplayMember = "Zone"
			.ValueMember = "Region"
			.AutoCompleteMode = AutoCompleteMode.SuggestAppend
			.DropDownStyle = ComboBoxStyle.DropDown
			.AutoCompleteSource = AutoCompleteSource.ListItems
		End With

		ComboBox1.DataBindings.Add("text", BS, "Zone", True, DataSourceUpdateMode.OnValidation)
		Label2.DataBindings.Add("text", BS, "Region", True, DataSourceUpdateMode.OnValidation)

		AddHandler BS.PositionChanged, AddressOf GetCurrent
	End Sub
	Sub GetCurrent()
		Dim pos As Integer = BS.Position
		Dim tz As String = dt(pos)(0)
		Dim reg As String = dt(pos)(1)

		SaveTZtxt(tz)
		SaveREGtxt(reg)

	End Sub
	Sub SaveTZtxt(s As String)
		Using sr As New IO.StreamWriter(OutputZoneFile, False)
			sr.WriteLine(s)
		End Using
	End Sub
	Sub SaveREGtxt(s As String)
		Dim xmlDoc As New Xml.XmlDocument
		Dim RegElement As Xml.XmlElement = xmlDoc.CreateElement("Region")
		xmlDoc.AppendChild(RegElement).InnerText = s
		xmlDoc.Save(OutputRegionFile)
	End Sub
End Class

Answer 2

Hi

The forum is showing that you posted at 2:56 but the actual post contents are being withheld - this forum is total garbage!

Anyway, just to keep trying to help. Here is a Sub that when called will read in from a CSV file, the TimeZone and the Region, and populate the datatable accordingly. As the Timezone may contain commas, the code tries to cater for that (does your CSV file have the same?)

Also included is a Sub to Write from the datatable to a CSV file.

You would need to change file names/paths according to your needs.

  ' you need to change accordingly
  Dim DataFileCSV As String = IO.Path.Combine(My.Computer.FileSystem.SpecialDirectories.Desktop, "TimeZonesCSV.csv")

  Sub ReadCSV()
    dt.Clear()
    If IO.File.Exists(DataFileCSV) Then
      Using sr As New IO.StreamReader(DataFileCSV)

        While Not sr.EndOfStream
          Dim s() As String = sr.ReadLine.Split(","c)
          Select Case s.Length
            Case 2
              dt.Rows.Add(s)
            Case Else
              Dim s2 As String = String.Empty
              For i As Integer = 0 To s.Length - 2
                s2 &= s(i) & ","c
              Next
              dt.Rows.Add(s2.Substring(0, s2.Length - 1), s(s.Length - 1))

          End Select
        End While
      End Using
    End If
  End Sub
  Sub WriteCSV()
    ' create or overwite ezisting CSV file
    Using sw As New IO.StreamWriter(DataFileCSV, IO.FileMode.Create)
      For Each r As DataRow In dt.Rows
        sw.WriteLine(r(0) & "," & r(1))
      Next
    End Using
  End Sub

Answer 3

Hi

I think Dwayne has possibly offered some info/help - I am unsure because his comment is hidden from me.

Here is a working version, using the same UI as before, and using this XML file. No CSV anymore BTW: the file you posted was not a valid file of any type as it had a mixture of "|" characters and Tab characters - I harvested the last portion to fill the second column for the attached file.

This version just to allow viewing and editing functions.

File for example: TimeZonesAndRegions.xml

' Form1 with DataGridView1 (empty),
' ComboBox1 (empty),
' Label1 and Label2 (Label2 for result)

Public Class Form1
	Dim dt As New DataTable("Freddy")
	Dim BS As New BindingSource

	' you need to change to suit

	Dim DataFileXML As String = IO.Path.Combine(My.Computer.FileSystem.SpecialDirectories.Desktop, "TimeZonesAndRegions.xml")
	Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing
		dt.WriteXml(DataFileXML)
	End Sub
	Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

		With dt
			.Columns.Add("Zone", GetType(String))
			.Columns.Add("Region", GetType(String))

			If IO.File.Exists(DataFileXML) Then .ReadXml(DataFileXML)

		End With

		BS.DataSource = dt

		DataGridView1.DataSource = BS
		With ComboBox1
			.DataSource = BS
			.DisplayMember = "Zone"
			.ValueMember = "Region"
			.AutoCompleteMode = AutoCompleteMode.SuggestAppend
			.DropDownStyle = ComboBoxStyle.DropDown
			.AutoCompleteSource = AutoCompleteSource.ListItems
		End With

		ComboBox1.DataBindings.Add("text", BS, "Zone", True, DataSourceUpdateMode.OnValidation)
		Label2.DataBindings.Add("text", BS, "Region", True, DataSourceUpdateMode.OnValidation)
	End Sub
End Class

Answer 4

Hi

Sorry for the delay.

Here is a working example, Needs the controls as per previous post. No exception handling. Has code to use either XML or CSV.

You would need to edit file name/path to suit (for both CSV and XML) as the code is set to read CSV and to write both CSV an

' Form1 with DataGridView1 (empty),
' ComboBox1 (empty),
' Label1 and Label2 (Label2 for result)

Public Class Form1
  Dim dt As New DataTable("Freddy")
  Dim BS As New BindingSource

  ' you need to change to suit

  Dim DataFileXML As String = IO.Path.Combine(My.Computer.FileSystem.SpecialDirectories.Desktop, "TimeZonesXML.xml")

  Dim DataFileCSV As String = IO.Path.Combine(My.Computer.FileSystem.SpecialDirectories.Desktop, "TimeZonesCSV.csv")

  Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing

    ' just write both types for demo
    WriteXML()
    WriteCSV()

  End Sub
  Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    With dt
      .Columns.Add("Zone", GetType(String))
      .Columns.Add("Standard Name", GetType(String))


      ' comment in/out to use only ONE of these

      '   ReadXML()
      ReadCSV()

    End With

    BS.DataSource = dt

    DataGridView1.DataSource = BS
    With ComboBox1
      .DataSource = BS
      .DisplayMember = "Zone"
      .ValueMember = "Standard Name"
      .AutoCompleteMode = AutoCompleteMode.SuggestAppend
      .DropDownStyle = ComboBoxStyle.DropDown
      .AutoCompleteSource = AutoCompleteSource.ListItems
    End With

    ComboBox1.DataBindings.Add("text", BS, "Zone", True, DataSourceUpdateMode.OnValidation)
    Label2.DataBindings.Add("text", BS, "Standard Name", True, DataSourceUpdateMode.OnValidation)
  End Sub



  Sub WriteXML()
    dt.WriteXml(DataFileXML)
  End Sub
  Sub ReadXML()
    dt.Rows.Clear()
    With dt
      If IO.File.Exists(DataFileXML) Then
        .ReadXml(DataFileXML)
      Else
        Dim tz As ObjectModel.ReadOnlyCollection(Of
      TimeZoneInfo) = TimeZoneInfo.GetSystemTimeZones()
        ComboBox1.DataSource = tz
        For Each z As TimeZoneInfo In tz
          .Rows.Add(z.DisplayName)
        Next
      End If
    End With
  End Sub


  Sub ReadCSV()
    dt.Rows.Clear()
    Dim line As String = String.Empty
    If IO.File.Exists(DataFileCSV) Then
      Using sr As New IO.StreamReader(DataFileCSV)
        Do
          line = sr.ReadLine
          If Not line Is Nothing Then
            Dim s() As String = line.Split(","c)
            Select Case s.Length
              Case 2
                dt.Rows.Add(s)
              Case Else
                Dim s2 As String = String.Empty
                For i As Integer = 0 To s.Length - 2
                  s2 &= s(i) & ","c
                Next
                dt.Rows.Add(s2.Substring(0, s2.Length - 1), s(s.Length - 1))

            End Select
          End If
        Loop Until line = Nothing
      End Using
    End If
  End Sub
  Sub WriteCSV()
    ' create or overwite ezisting CSV file
    Using sw As New IO.StreamWriter(DataFileCSV, False)
      For Each r As DataRow In dt.Rows
        sw.WriteLine(r(0) & "," & r(1))
      Next
    End Using
  End Sub
End Class

d XML.