Getting .CSV headers into a combo box

[nottaclu]

I am attempting to do my own field mapping within VB. I am selecting a .csv file and want to populate a combo box with the column headers from the .csv. Can anyone help me out/lead me in the right direction. Thank you very much in advance!

[kareninstructor]

Greetings,

An example using the following text file located in the application startup folder.

Document.txt

FirstName, LastName, Address
Kevin, Gallagher, 456 Wyndmoor ave
Mary, Gallagher, 234 Apple Lane

This code (VS2010) is hardwired to a single document, does not check to see if the file exists, expects a comma as a delimiter and assumes the first row has headers and there are lines of data beneath the first row. The only assertion is to not pick up on empty rows in the second statement. If the first row is blank then this will be a problem. I mention this because these are things you need to consider.

Private Sub Demo()
    Dim ColumnNames = (From line In IO.File.ReadAllLines("Document.txt")
                       Let Items = line.Split(","c)
                       Select Items).FirstOrDefault

    For Each Column In ColumnNames
        ComboBox1.Items.Add(Column.Trim)
    Next

    Dim Rows = (From line In IO.File.ReadAllLines("Document.txt")
                Where line.Length > 0
                Let Items = line.Split(","c)
                Select Items Skip 1).ToList

    For Each row In Rows
        Console.WriteLine(String.Join("-", row))
    Next
End Sub

[nottaclu]

That worked great! Do you know how you would do this with an excel (.xls)file as well? These will be the two formats I would like to be able to read the headers into the combobox. Thanks.

[kareninstructor]

That worked great! Do you know how you would do this with an excel (.xls)file as well? These will be the two formats I would like to be able to read the headers into the combobox. Thanks.

I am going to only address saving data from text files to Excel but not provide a ready to run solution that meets all your needs. By creating a new project, add a button to call my code and add the following reference Microsoft.Office.Interop.Excel to the project you are ready to go.

The code presented will create an Excel file, populate the first row of sheet1 with column headers obtained from the text file, and then populate two rows of data from the text file, which works on any number of rows and columns. What it does not account for is coming back and adding more rows to the file. To come back and add more rows in short all the pieces needed are buried in the code below.

The function ExcelSheetLastRow is not needed to create or populate cells but would be needed if you wanted to open the file later and append more rows.

A word of caution, when working with Excel automation as done here you must pay close attention to how you dispose of these objects. If they are not disposed correctly they will remain in memory until the application closes. If the application crashes you will need to open task manager to force closure. Why would your program crash? Not because of properly written code but instead you ran into a normal error in the course of populating cells i.e. improperly indexing an array of say strings. Any time you are coding with automation I highly suggest keeping task manager open to make sure Excel is closing before the application closes and if not you did not clean up the Office objects correctly. Microsoft calls this tunneling.

In regards to formatting cells, play with the code and you will find where to format cells. Best to learn about using styles in Excel if you end up doing a great deal of formatting.

So with that said I have given you tools to accomplish saving data from a delimited text file to an Excel file.

Private Sub SomeButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SomeButton.Click
    InitPopulateSheet1()
End Sub
Private Sub InitPopulateSheet1()

    Dim ColumnNames = _
    ( _
        From line In IO.File.ReadAllLines("Document.txt") _
        Let Items = line.Split(","c) _
        Select Items _
    ).FirstOrDefault

    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    Dim misValue As Object = System.Reflection.Missing.Value

    xlApp = New Excel.ApplicationClass

    xlWorkBook = xlApp.Workbooks.Add(misValue)
    xlWorkSheet = CType(xlWorkBook.Sheets("sheet1"), Excel.Worksheet)

    For col As Integer = 0 To ColumnNames.Count - 1
        xlWorkSheet.Cells(1, col + 1) = ColumnNames(col).Trim
    Next

    Dim Rows = _
    ( _
        From line In IO.File.ReadAllLines("Document.txt") _
        Where (line.Length > 0) _
        Let Items = line.Split(","c) _
        Select Items Skip 1 _
    ).ToList

    Dim RowIndex As Integer = 2

    For Each row In Rows
        For col As Integer = 0 To row.Count - 1
            xlWorkSheet.Cells(RowIndex, col + 1) = row(col).Trim
        Next
        RowIndex += 1
    Next

    Dim FileName As String = IO.Path.Combine(Application.StartupPath, "MyNewFile.xls")
    
    If IO.File.Exists(FileName) Then
        IO.File.Delete(FileName)
        Application.DoEvents()
    End If

    xlWorkSheet.SaveAs(FileName)

    xlWorkBook.Close(False)
    xlApp.Quit()
    ReleaseObject(xlWorkSheet)
    ReleaseObject(xlWorkBook)
    ReleaseObject(xlApp)


    MessageBox.Show( _
        String.Format("Created {0}{1}Row count = [{2}]", _
                      FileName, _
                      Environment.NewLine, _
                      ExcelSheetLastRow(FileName)))

End Sub
Public Function ExcelSheetLastRow(ByVal FileName As String) As Integer
    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    xlApp = New Excel.Application
    xlApp.DisplayAlerts = False

    xlApp.Workbooks.Open(FileName)
    xlWorkBook = xlApp.Workbooks.Open(IO.Path.Combine(Application.StartupPath, "MyNewFile.xls"))
    xlWorkSheet = CType(xlWorkBook.Sheets("Sheet1"), Microsoft.Office.Interop.Excel.Worksheet)
    Dim rng = xlWorkSheet.Range("A1").End(Excel.XlDirection.xlDown)

    Dim Result As Integer = rng.Row

    ReleaseObject(rng)
    xlWorkBook.Close(False)
    xlApp.Quit()
    ReleaseObject(xlWorkSheet)
    ReleaseObject(xlWorkBook)
    ReleaseObject(xlApp)

    Return Result
End Function
Private Sub ReleaseObject(ByVal obj As Object)
    Try
        Do Until System.Runtime.InteropServices.Marshal.ReleaseComObject(obj) <= 0
            Application.DoEvents()
        Loop
        obj = Nothing
    Catch ex As Exception
        obj = Nothing
    Finally
        GC.Collect()
    End Try
End Sub