importing excel

  • Thread starter Thread starter Rob T
  • Start date Start date
R

Rob T

I need to write a little utility that can read in an excel file.

Currently I could have the end-user could save the file as a .txt and I
could import that. I would like to avoid this since I would have to rely on
the end-user to export the data properly....and we all know how well that
would work!

Also I could create an odbc connection to the file, but the name of the file
changes each time...again, I have to rely on the intelligence of the
end-user.......

Thanks.

-Rob T.
 
Rob,

You can very easy create a dataset from an excel file.

\\\
Dim ConnectionString As String
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\myExcel.xls;" & _
"Extended Properties=""Excel 8.0;HDR=NO"""
Dim Conn As New System.Data.OleDb.OleDbConnection(ConnectionString)
Dim da As New System.Data.OleDb.OleDbDataAdapter _
("Select * from [Sheet1$]", Conn)
Dim ds As New DataSet
da.Fill(ds, "Sheet1")
///

Be aware that Sheet1 can be in any language different.

I hope this helps a little bit?

Cor
 
Hi Cor,
I have heard others ask about filling a DS from Excel. What does the
table structure look like when that is done? Is each column a field?
and each row a row? Or what?

TIA,
John
 
John,
I have heard others ask about filling a DS from Excel. What does the
table structure look like when that is done? Is each column a field?
and each row a row? Or what?
Click to expand...

Yes, and there are two option, one is to skip a headerline from Excel and
one is to get all the columns as string "Text"

Cor
 
Thanks Cor, that is very helpful.

John

John,


Yes, and there are two option, one is to skip a headerline from Excel and
one is to get all the columns as string "Text"

Cor
Click to expand...
 
I have been searching all over the internet for days now with a similar problem as Rob had. See I want to import from excel as well and OLEDB connection worked great at allowing me to do this I still would like to improve uppon what you have the end user worry about how this is done even less. I want to dynamically import from different sheets. My excel files will not always have a 'sheet1' and I have no idea how to get Cor's code to do that.

Thanks.

J L said:
Hi Cor,
I have heard others ask about filling a DS from Excel. What does the
table structure look like when that is done? Is each column a field?
and each row a row? Or what?

TIA,
John

On Tue, 10 May 2005 15:09:33 +0200, "Cor Ligthert"
wrote:

>Rob,
>
>You can very easy create a dataset from an excel file.
>
>\\\
>Dim ConnectionString As String
>ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=c:\myExcel.xls;" & _
> "Extended Properties=""Excel 8.0;HDR=NO"""
>Dim Conn As New System.Data.OleDb.OleDbConnection(ConnectionString)
>Dim da As New System.Data.OleDb.OleDbDataAdapter _
> ("Select * from [Sheet1$]", Conn)
>Dim ds As New DataSet
>da.Fill(ds, "Sheet1")
>///
>
>Be aware that Sheet1 can be in any language different.
>
>I hope this helps a little bit?
>
>Cor
>
Click to expand...
 
Back
Top