how can i read excel file from vb?
what technic supports also csv files?
thanks in advanced.
Printable View
how can i read excel file from vb?
what technic supports also csv files?
thanks in advanced.
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 ?
reading csv file
reading csv fileCode: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
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
Quote:
I need some way of parsing or reading (each individual cell?) the data
i hope u asked this, it give the column "name" from the dataset datatableCode: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
first add the columns into datatable.Quote:
i need use the third row columns as headers
then loop through ur file and get the rows
Code: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
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?
try this connection stringCode:"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test.xls;Extended Properties=""Excel 8.0;HDR=YES;"""