[2005] How to read excel file and display in text box

[cheynewong]

Hi all,

How can I read the the .csv file and display the particular string (channel 1) in textbox?

say I have write into a .csv file using code below

SW = File.CreateText("C:\" & "test" & ".csv")
SW.Write("Channel 1, Channel 2, Channel 3, Channel 4, Channel 5, Channel 6, Channel 7, Channel 8")
SW.Close()

Cheyne

[jmcilhinney]

First up, a CSV file is not an Excel file. An Excel file is an XLS file. CSV is a standard, delimited text format that Excel can open.

As to your question, you can read CSV files in various ways. You could use a StreamReader, but you wouldn't. I would suggest either using ADO.NET and reading the file as though it was a database, or else using a TextFieldParser object. www.connectionstrings.com can give you the details for ADO.NET and I've posted an example of using a TextFieldParser previously, so a search of the forum will bring it up.

[cheynewong]

Hi,

If I change the file format to .xls while writing into it. all the channel 1, channel 2 cannot be written in a excel box. it will split up. But when using .csv it could fit into a box in the .csv file for the whole string (say channel 1).

Reading a .csv file easier or reading a .xls file easier?

[jmcilhinney]

If you're going to write comma-separated values to the file then it's a CSV file. If you want to create an XLS file then you'd need to write it in the correct binary format, which you'd normally do by automating the Excel application itself. Stick with CSV unless you specifically need an XLS file. It's easier that way.

[cheynewong]

I am using this code to actually read the value to textfile. but it couldnt work

Please advice.

Dim line As String
Dim SR As StreamReader
SR = New StreamReader("C:\" & "test" & ".csv")

Do
     line = SR.ReadLine()
     TextBox1.Text = line90)
     Console.WriteLine(line)
Loop Until line Is Nothing
SR.Close()

[jmcilhinney]

I've already told you to use ADO.NET or a TextFieldParser. That code does neither. If you're not going to follow the advice I provide then I can help no further.

You could use a StreamReader, but you wouldn't. I would suggest either using ADO.NET and reading the file as though it was a database, or else using a TextFieldParser object. www.connectionstrings.com can give you the details for ADO.NET and I've posted an example of using a TextFieldParser previously, so a search of the forum will bring it up.

[cheynewong]

Can you provide me a simple sample code on how to read the .csv file ? Just a simple one will do.

Thanks in advance

[jmcilhinney]

As I have already stated, I have already posted example code to read a delimited text file on the forum. I'm not going to do it again. For me to tell you where that example is I would have to search the forum for textfieldparser with my user name. You can do that just as easily as I can. I suggest some initiative on your part.

[cheynewong]

Hi,

I have managed to do it.

So, let say I am going to read a .xls file. Any recommendation on how I could do it?

[RobDog888]

In order to create an xls file format file you wil need to use the Excel COM object library. Since you need to use the EOM you might as well open your csv file with it too to simplify and shorten code.

See my FAQ item on opening a csv and saving to xls
http://www.vbforums.com/showthread.php?t=408487

[jmcilhinney]

You can also read XLS files using ADO.NET, pretty much as you would an Access database. What you can do is far more limited than using Excel Automation but it's easier so if it serves your needs then I'd go that way. www.connectionstrings.com has the connection details.

[cheynewong]

Hi Rob Dog,

The thread you put here does not have any VB2005 code.

jmcilhinney,

I cant find any information related to ADO.net from the site you recommended.

Please kindly provide me some sample code if possible.

I would learn faster by looking at the sample code.

[jmcilhinney]

The site I recommended provides the connection string formats for numerous ADO.NET data sources. If you don't know anything about ADO.NET in general then you should read some ADO.NET tutorials, of which there are many on the Web and even one on this site. The Data Access link in my signature also provides some code examples for common data access scenarios.

[cheynewong]

Hi.

Say I want to read a particular box from the excel file. How could I proceed from the code below?

Please advice.

Dim conn As New OleDbConnection
        Dim connString As String
        connString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                     "Data Source=C:\test.xls;"
        conn.ConnectionString = connString
        conn.Open()

Thanks alot

[jmcilhinney]

Have you read the Data Access link of mine? It shows you how to read data from an ADO.NET data source using a data reader or, alternatively, into a DataTable. If you use the DataTable option then you can get the desired row by index from the table's Rows collection, then the desired field by column name or index. For example, this code:

Dim value As Object = myDataTable.Rows(0)("SomeColumn")

gets the value from the SomeColumn column of the first row in the table.

[cheynewong]

hi,

I have read about it but not really can understand the concept behind.

Could you just provide a simple code on how to read a value from a particular column and row.

I just need to know the code that does the work.

Thanks

[RobDog888]

Hi Rob Dog,

The thread you put here does not have any VB2005 code.

jmcilhinney,

I cant find any information related to ADO.net from the site you recommended.

Please kindly provide me some sample code if possible.

I would learn faster by looking at the sample code.

If you notice the heading above the code example ...
"Visual Basic .NET 2003-2008 And Excel 2000-2003"

The question of even using ADO.NET could be answered if we know what are your plans for using the csv/xls file.