read excel file

[ppl1]

how can i read excel file from vb?
what technic supports also csv files?
thanks in advanced.

[ppl1]

i searched in the net and found that there are 2 ways to read from excel ,
one is with OleDb and the other is using a com dll.
what is the different between them? what should i use ?

[aniskhan]

reading csv file

Imports System.Data
 Imports System.Data.OleDb
 
 
 Public Class Form1
     Inherits System.Windows.Forms.Form
     Dim objDataset1 As DataSet()
 
     Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As 
 System.EventArgs) Handles MyBase.Load
         
         Dim sConnectionString As String = 
 "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\;Extended Properties=Text;"
         Dim objConn As New OleDbConnection(sConnectionString)
         objConn.Open()
 
         Dim objCmdSelect As New OleDbCommand("SELECT * FROM test.csv", 
 objConn)
         Dim objAdapter1 As New OleDbDataAdapter()
         objAdapter1.SelectCommand = objCmdSelect
 
         Dim objDataset1 As New DataSet()
         objAdapter1.Fill(objDataset1, "Test")
         DataGrid1.DataSource = objDataset1.Tables(0).DefaultView
         objConn.Close()
     End Sub
 End Class

reading csv file

[la2texas]

I am fairly new to VB, i used to do some programming a loooong time ago with qbasic lol, but thats way out of date. I have been trying to do the same thing, read an excel file and eventually write it to a plain txt dat file for another program. Thanks aniskhan for your reply, that really helped me. The original article i looked at really helped too ( http://support.microsoft.com/kb/316934/EN-US/). My question is this, i see how a datagrid can be filled now, but what i need to do is

1.) instead of using the top row's colums as headers, i need to use the third row columns as headers, how might i go about this? i figure its some sort of small loop that counts the rows down and puts them as the headers, then continues reading the rest of the file, but im not sure how to go about it.

2.) I need some way of parsing or reading (each individual cell?) the data and saying if this = that then output to file or something like that, any ideas? I figure it would be easy to write a procedure like ParseXL(). But what variable will i be parsing if that makes sense? is it the objDataset1.Tables(0).DefaultView ? or which one?

Thanks in advance :P

[aniskhan]

I need some way of parsing or reading (each individual cell?) the data

 Dim str As String
        For i As Integer = 0 To dataset1.datatable("tablename").Rows.Count - 1

            MessageBox.Show(dataset1.datatable("tablename").Rows(i).Item("name").ToString)
        Next

i hope u asked this, it give the column "name" from the dataset datatable

[aniskhan]

i need use the third row columns as headers

first add the columns into datatable.
then loop through ur file and get the rows

 If loopindex = 3 Then    'make row the columnName
                dt.Columns(0).ColumnName = value
                dt.Columns(1).ColumnName = value
Else
                Dim r As DataRow = dt.NewRow    'addRow to dataTable
                r(0) = value
                r(1) = value
                dt.Rows.Add(r)
            End If

[ppl1]

im using the connection string-
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Book2.xls;Extended
properties=Excel 8.0;IMEX=1;HDR=YES;"

and im getting the error-
Could not find installable ISAM

what is he problem?

[aniskhan]

try this connection string

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test.xls;Extended Properties=""Excel 8.0;HDR=YES;"""