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!
Printable View
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!
Greetings,
An example using the following text file located in the application startup folder.
Document.txt
Code: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.
Code: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
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.
Quote:
Originally Posted by nottaclu
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.
Code: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