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

Thanks both for your replies.

Let me provide you with some more information.

The combo box is pre-populated by me simply adding the items on the form design. I clicked on "Edit items" and added my selections.

The selections are all the Windows timezones, i was using days of the week to try and simplify my question.

Therefore the spreadsheet will have 2 columns, one with timezones and one with regions. The spreadsheet will be located in the root of the project. For example:

For example If my user selects "(UTC-02:00) Coordinated Universal Time-02 | UTC-02" from the combo box, i need to be able to reference the spreadsheet and see if that matches with a row and if it does, it will grab column B (in this case "Americas") and write this to a file.

I hope that helps

Many thanks

Simon

Answer 3

Amended code (removed need for ComboBox1.SelectedIndexChanged event)

' 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
  Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    Dim tz As ObjectModel.ReadOnlyCollection(Of
    TimeZoneInfo) = TimeZoneInfo.GetSystemTimeZones()
    ComboBox1.DataSource = tz

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

      For Each z As TimeZoneInfo In tz
        .Rows.Add(z.DisplayName, z.DaylightName)
      Next
    End With
    BS.DataSource = dt

    DataGridView1.DataSource = BS
    With ComboBox1
      .DataSource = BS
      .DisplayMember = "Zone"
      .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
End Class

Answer 4

Hi Les,

Thank you for your reply.

This works quite nicely, however the "Standard Name" which is pulled from the data table needs to be set based on the screenshot below. I'm not using the standard Timezone info in this column.

This is the first few rows of my table...

How can i achieve this?

Sorry, i only have basic skills in coding!

Many thanks

Simon

Answer 5

Hi Les.

Thank you for your replies. Really appreciate your help. Got someone chasing me on this and trying to accommodate this on top of my normal IT work! 😂

Yes i do have a CSV file with the "TimeZones" and corresponding "Regions". Can't attach it as it won't let me, doesn't like csv's!

Screenshot of the first few rows below...

I may amend the odd region onc this has been confirmed by my colleague but if i can get the code to read it then this won't be a problem 😁

Many thanks

Simon