[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.dotnet.framework.odbcnet

How to Convert Excel to Table?

Murali

9/16/2002 8:46:00 PM

Hi

How to convert Excel file into table using VB.NET code (not in ADO)?


Anyone help?

Thx
Murali
7 Answers

Paul Clement

9/16/2002 9:10:00 PM

0

On Mon, 16 Sep 2002 15:46:35 -0400, "Murali" <cutekutty@hotmail.com> wrote:

¤ Hi
¤
¤ How to convert Excel file into table using VB.NET code (not in ADO)?
¤

I don't know what kind of table you are referring to, but the following code will read an Excel
Worksheet into a DataTable:

'Establish a connection to the data source.
Dim sConnectionString As String
sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=d:\My Documents\Book2.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes"""
Dim objConn As New System.Data.OleDb.OleDbConnection(sConnectionString)
objConn.Open()

Dim da As New System.Data.OleDb.OleDbDataAdapter("Select * from [Sheet4$]", objConn)

Dim ds As New DataSet("ExcelDS")

da.Fill(ds, "ExcelSheet")

Dim dt As DataTable
dt = ds.Tables("ExcelSheet")

Dim drCurrent As DataRow
For Each drCurrent In dt.Rows
Console.WriteLine("{0} {1}", _
drCurrent("Col1").ToString, _
drCurrent("Col2").ToString)
Next

objConn.Close()

If the first row of the Excel Worksheet does not contain column names then remove the HDR=Yes
Extended Property from the connection string.


Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)

Murali

9/16/2002 9:40:00 PM

0

HI Thanks
But i got it already.I want to get the name of the Sheet ?
eg "Select * from SheetName"

Thx
Murali


"Paul Clement" <UseAdddressAtEndofMessage@swspectrum.com> wrote in message
news:ucecou8a4d73vnhbfjt99r208s1kidern4@4ax.com...
> On Mon, 16 Sep 2002 15:46:35 -0400, "Murali" <cutekutty@hotmail.com>
wrote:
>
> ¤ Hi
> ¤
> ¤ How to convert Excel file into table using VB.NET code (not in ADO)?
> ¤
>
> I don't know what kind of table you are referring to, but the following
code will read an Excel
> Worksheet into a DataTable:
>
> 'Establish a connection to the data source.
> Dim sConnectionString As String
> sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=d:\My Documents\Book2.xls;" & _
> "Extended Properties=""Excel 8.0;HDR=Yes"""
> Dim objConn As New
System.Data.OleDb.OleDbConnection(sConnectionString)
> objConn.Open()
>
> Dim da As New System.Data.OleDb.OleDbDataAdapter("Select * from
[Sheet4$]", objConn)
>
> Dim ds As New DataSet("ExcelDS")
>
> da.Fill(ds, "ExcelSheet")
>
> Dim dt As DataTable
> dt = ds.Tables("ExcelSheet")
>
> Dim drCurrent As DataRow
> For Each drCurrent In dt.Rows
> Console.WriteLine("{0} {1}", _
> drCurrent("Col1").ToString, _
> drCurrent("Col2").ToString)
> Next
>
> objConn.Close()
>
> If the first row of the Excel Worksheet does not contain column names then
remove the HDR=Yes
> Extended Property from the connection string.
>
>
> Paul ~~~ pclement@ameritech.net
> Microsoft MVP (Visual Basic)

Paul Clement

9/17/2002 1:16:00 PM

0

On Mon, 16 Sep 2002 16:40:33 -0400, "Murali" <cutekutty@hotmail.com> wrote:

¤ HI Thanks
¤ But i got it already.I want to get the name of the Sheet ?
¤ eg "Select * from SheetName"
¤

If you want the names of the Worksheets in the Workbook this can be done a couple of ways. You can
use automation, which requires Excel, or you can use data access methods. The data access method
that I know will work requires ADO and ADOX:

Dim cnn As New ADODB.Connection()
Dim cat As New ADOX.Catalog()
Dim xlSheet As ADOX.Table

cnn.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=d:\My Documents\Book2.xls;Extended Properties=Excel 8.0;")

' Open the catalog
cat.ActiveConnection = cnn

For Each xlSheet In cat.Tables
Console.WriteLine(xlSheet.Name)
Next

cat = Nothing
cnn.Close()
cnn = Nothing


Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)

Murali

9/17/2002 7:04:00 PM

0

Hi

First i exported a table to excel file and convert back.It works fine.
But when i directly import from another excel file which has no header(no
column name),it says error in "select * from sheetname" as

"The Microsoft Jet database engine could not find the object 'Sheet1'. Make
sure the object exists and that you spell its name and the path name
correctly."

even it fetches a sheet name sheet1 from the following code.

Dim Cn As New ADODB.Connection()
Dim rs As New ADODB.Recordset()
Dim Cat As New ADOX.Catalog()
Dim xlSheet As ADOX.Table
conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName &
";Extended Properties=""Excel 8.0;HDR=Yes"""

I tried both HDR=Yes and HDR=No

Cn.Open(conString)
Cat.ActiveConnection = Cn
cmd.ActiveConnection = Cn
For Each xlSheet In Cat.Tables
xlsheetName = xlSheet.Name.Replace("$", "")
Next
cmd.CommandText = "select * from " & xlsheetName 'ERROR HERE
rs = cmd.Execute()

Could u help me in this?

-Murali

"Paul Clement" <UseAdddressAtEndofMessage@swspectrum.com> wrote in message
news:7s6eou8pc65ncs1m0nmau596b42chk7jsp@4ax.com...
> On Mon, 16 Sep 2002 16:40:33 -0400, "Murali" <cutekutty@hotmail.com>
wrote:
>
> ¤ HI Thanks
> ¤ But i got it already.I want to get the name of the Sheet ?
> ¤ eg "Select * from SheetName"
> ¤
>
> If you want the names of the Worksheets in the Workbook this can be done a
couple of ways. You can
> use automation, which requires Excel, or you can use data access methods.
The data access method
> that I know will work requires ADO and ADOX:
>
> Dim cnn As New ADODB.Connection()
> Dim cat As New ADOX.Catalog()
> Dim xlSheet As ADOX.Table
>
> cnn.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=d:\My Documents\Book2.xls;Extended
Properties=Excel 8.0;")
>
> ' Open the catalog
> cat.ActiveConnection = cnn
>
> For Each xlSheet In cat.Tables
> Console.WriteLine(xlSheet.Name)
> Next
>
> cat = Nothing
> cnn.Close()
> cnn = Nothing
>
>
> Paul ~~~ pclement@ameritech.net
> Microsoft MVP (Visual Basic)

Alice K

9/18/2002 3:03:00 PM

0

I believe you need the $ at the end of your sheet name and=20
[] around the sheet name.=20

Select * from [Sheet1$].

Alice


>-----Original Message-----
>Hi
>
>First i exported a table to excel file and convert=20
back.It works fine.
>But when i directly import from another excel file which=20
has no header(no
>column name),it says error in "select * from sheetname" as
>
>"The Microsoft Jet database engine could not find the=20
object 'Sheet1'. Make
>sure the object exists and that you spell its name and=20
the path name
>correctly."
>
>even it fetches a sheet name sheet1 from the following=20
code.
>
>Dim Cn As New ADODB.Connection()
>Dim rs As New ADODB.Recordset()
>Dim Cat As New ADOX.Catalog()
>Dim xlSheet As ADOX.Table
>conString =3D "Provider=3DMicrosoft.Jet.OLEDB.4.0;Data=20
Source=3D" & FileName &
>";Extended Properties=3D""Excel 8.0;HDR=3DYes"""
>
> I tried both HDR=3DYes and HDR=3DNo
>
>Cn.Open(conString)
>Cat.ActiveConnection =3D Cn
>cmd.ActiveConnection =3D Cn
>For Each xlSheet In Cat.Tables
> xlsheetName =3D xlSheet.Name.Replace("$", "")
>Next
>cmd.CommandText =3D "select * from " & xlsheetName 'ERROR=20
HERE
>rs =3D cmd.Execute()
>
>Could u help me in this?
>
>-Murali
>
>"Paul Clement" <UseAdddressAtEndofMessage@swspectrum.com>=20
wrote in message
>news:7s6eou8pc65ncs1m0nmau596b42chk7jsp@4ax.com...
>> On Mon, 16 Sep 2002 16:40:33 -0400, "Murali"=20
<cutekutty@hotmail.com>
>wrote:
>>
>> =A4 HI Thanks
>> =A4 But i got it already.I want to get the name of the=20
Sheet ?
>> =A4 eg "Select * from SheetName"
>> =A4
>>
>> If you want the names of the Worksheets in the Workbook=20
this can be done a
>couple of ways. You can
>> use automation, which requires Excel, or you can use=20
data access methods.
>The data access method
>> that I know will work requires ADO and ADOX:
>>
>> Dim cnn As New ADODB.Connection()
>> Dim cat As New ADOX.Catalog()
>> Dim xlSheet As ADOX.Table
>>
>> cnn.Open("Provider=3DMicrosoft.Jet.OLEDB.4.0;" & _
>> "Data Source=3Dd:\My=20
Documents\Book2.xls;Extended
>Properties=3DExcel 8.0;")
>>
>> ' Open the catalog
>> cat.ActiveConnection =3D cnn
>>
>> For Each xlSheet In cat.Tables
>> Console.WriteLine(xlSheet.Name)
>> Next
>>
>> cat =3D Nothing
>> cnn.Close()
>> cnn =3D Nothing
>>
>>
>> Paul ~~~ pclement@ameritech.net
>> Microsoft MVP (Visual Basic)
>
>
>.
>

Paul Clement

9/18/2002 4:54:00 PM

0

On Wed, 18 Sep 2002 07:03:47 -0700, "AK" <alkhang@hotmail.com> wrote:

¤ I believe you need the $ at the end of your sheet name and
¤ [] around the sheet name.
¤
¤ Select * from [Sheet1$].

Correct.


Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)

(Andy)

12/26/2002 1:36:00 PM

0

"Murali" <cutekutty@hotmail.com> wrote in message news:<#0fTQPnXCHA.4200@tkmsftngp08>...
> Hi
>
> First i exported a table to excel file and convert back.It works fine.
> But when i directly import from another excel file which has no header(no
> column name),it says error in "select * from sheetname" as
<<>>

Maybe you ought to just password protect most of the spreadsheet you give
them?

But I wonder if this is the best approach for your task.
Are you intending sending out files to users...
Allowing them to edit as they fancy...
Expecting to re-import the data ...?

If so, your current problems might be the least of your worries, mate.

Perhaps you might do better thinking about another method.
Perhaps xml.

Good luck.