RE: Reading XLSX files

[June Xue]

Hi Greg,

Check 
http://www.mikesdotnetting.com/article/297/the-best-way-to-import-data-from-excel-to-sql-server-via-asp-net
 to see if it helps.

Cheers,
June

From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] On 
Behalf Of Greg Keogh
Sent: Thursday, 28 April 2016 7:17 AM
To: ozDotNet
Subject: Re: Reading XLSX files

I asked my cat about this overnight, he suggested I do this and look at the 
schema:

var table = connect.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { 
null, null, null, "TABLE" });

Breaking in the debugger I could see one row in the table, and inside was the 
suspicious value Foo$.

By using sheet name Foo$ in the code from yesterday ... the Excel sheet rows 
are retrieved okay. No samples I found in my hours of searching suggested or 
use this naming.

GK



On 28 April 2016 at 01:18, Davy Jones 
<djones...@gmail.com<mailto:djones...@gmail.com>> wrote:
Hey again

Just looked at the code here, don't specify a query in the constructor pass it 
to the property after the connection is open.

Hth
Davy

Sent from my iPhone

On 27 Apr 2016, at 14:20, Greg Keogh 
<gfke...@gmail.com<mailto:gfke...@gmail.com>> wrote:
Folks, maybe 10 years ago I easily read the rows out of an XLS file using OleDb 
and a reader. Today I tried to read an XLSX file the same way. First I 
discovered I had to install AccessDatabaseEngine_x64.exe to be able to register 
a provider to open the file. My test file has a single sheet named Foo with a 
few rows of numbers. All the code combinations I've tried like the one below 
die with:

System.Data.OleDb.OleDbException : The Microsoft Access database engine could 
not find the object 'Foo'. Make sure the object exists and that you spell its 
name and the path name correctly. If 'Foo' is not a local object, check your 
network connection or contact the server administrator.

So I guess the sheet is acting like a table, but it's not found and I'm 
flummoxed. Any ideas? Anyone done this sort of thing?

Greg K

string excelConnect = $"Provider=Microsoft.ACE.OLEDB.12.0;Data 
Source={filename};Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";
using (var connect = new OleDbConnection(excelConnect))
{
 connect.Open();
 using (var command = new OleDbCommand("SELECT * FROM [Foo]", connect))
 {
  using (var adapter = new OleDbDataAdapter(command))
  {
   var ds = new DataSet();
   adapter.Fill(ds);
   return new SchemaDef();
  }
 }
}

Re: Reading XLSX files

[Greg Keogh]

I asked my cat about this overnight, he suggested I do this and look at the
schema:

var table = connect.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new
object[] { null, null, null, "TABLE" });

Breaking in the debugger I could see one row in the table, and inside was
the suspicious value Foo$.

By using sheet name Foo$ in the code from yesterday ... the Excel sheet
rows are retrieved okay. No samples I found in my hours of searching
suggested or use this naming.

*GK*



On 28 April 2016 at 01:18, Davy Jones  wrote:

> Hey again
>
> Just looked at the code here, don't specify a query in the constructor
> pass it to the property after the connection is open.
>
> Hth
> Davy
>
> Sent from my iPhone
>
> On 27 Apr 2016, at 14:20, Greg Keogh  wrote:
>
> Folks, maybe 10 years ago I easily read the rows out of an XLS file using
> OleDb and a reader. Today I tried to read an XLSX file the same way. First
> I discovered I had to install AccessDatabaseEngine_x64.exe to be able to
> register a provider to open the file. My test file has a single sheet named
> Foo with a few rows of numbers. All the code combinations I've tried like
> the one below die with:
>
> *System.Data.OleDb.OleDbException : The Microsoft Access database engine
> could not find the object 'Foo'. Make sure the object exists and that you
> spell its name and the path name correctly. If 'Foo' is not a local object,
> check your network connection or contact the server administrator.*
>
> So I guess the sheet is acting like a table, but it's not found and I'm
> flummoxed. Any ideas? Anyone done this sort of thing?
>
> *Greg K*
>
> string excelConnect = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={
> filename};Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";
> using (var connect = new OleDbConnection(excelConnect))
> {
>  connect.Open();
>  using (var command = new OleDbCommand("SELECT * FROM [Foo]", connect))
>  {
>   using (var adapter = new OleDbDataAdapter(command))
>   {
>var ds = new DataSet();
>adapter.Fill(ds);
>return new SchemaDef();
>   }
>  }
> }
>
>

Re: Reading XLSX files

[Davy Jones]

Hey again

Just looked at the code here, don't specify a query in the constructor pass it 
to the property after the connection is open. 

Hth 
Davy

Sent from my iPhone

> On 27 Apr 2016, at 14:20, Greg Keogh  wrote:
> 
> Folks, maybe 10 years ago I easily read the rows out of an XLS file using 
> OleDb and a reader. Today I tried to read an XLSX file the same way. First I 
> discovered I had to install AccessDatabaseEngine_x64.exe to be able to 
> register a provider to open the file. My test file has a single sheet named 
> Foo with a few rows of numbers. All the code combinations I've tried like the 
> one below die with:
> 
> System.Data.OleDb.OleDbException : The Microsoft Access database engine could 
> not find the object 'Foo'. Make sure the object exists and that you spell its 
> name and the path name correctly. If 'Foo' is not a local object, check your 
> network connection or contact the server administrator.
> 
> So I guess the sheet is acting like a table, but it's not found and I'm 
> flummoxed. Any ideas? Anyone done this sort of thing?
> 
> Greg K
> 
> string excelConnect = $"Provider=Microsoft.ACE.OLEDB.12.0;Data 
> Source={filename};Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";
> using (var connect = new OleDbConnection(excelConnect))
> {
>  connect.Open();
>  using (var command = new OleDbCommand("SELECT * FROM [Foo]", connect))
>  {
>   using (var adapter = new OleDbDataAdapter(command))
>   {
>var ds = new DataSet();
>adapter.Fill(ds);
>return new SchemaDef();
>   }
>  }
> }

Reading XLSX files

[Greg Keogh]

Folks, maybe 10 years ago I easily read the rows out of an XLS file using
OleDb and a reader. Today I tried to read an XLSX file the same way. First
I discovered I had to install AccessDatabaseEngine_x64.exe to be able to
register a provider to open the file. My test file has a single sheet named
Foo with a few rows of numbers. All the code combinations I've tried like
the one below die with:

*System.Data.OleDb.OleDbException : The Microsoft Access database engine
could not find the object 'Foo'. Make sure the object exists and that you
spell its name and the path name correctly. If 'Foo' is not a local object,
check your network connection or contact the server administrator.*

So I guess the sheet is acting like a table, but it's not found and I'm
flummoxed. Any ideas? Anyone done this sort of thing?

*Greg K*

string excelConnect = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={
filename};Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";
using (var connect = new OleDbConnection(excelConnect))
{
 connect.Open();
 using (var command = new OleDbCommand("SELECT * FROM [Foo]", connect))
 {
  using (var adapter = new OleDbDataAdapter(command))
  {
   var ds = new DataSet();
   adapter.Fill(ds);
   return new SchemaDef();
  }
 }
}