RE: Reading XLSX files
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
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
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
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
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
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: Parallel tasks
Hi
If you are calling back to the ui thread it should be on as long as you don't
try to update the same resource with two different methods / threads at the
same time. If you do lock{} the update and wait for the interface to refresh.
In my experience querying the same web service from multiple threads from
multiple clients results it the web service slowing to an almost crawl. Cache
as much as you can on the web service.
Davy
Sent from my iPhone
On 27 Apr 2016, at 09:49, Greg Keogh wrote:
>> If you are not waiting for the tasks to finish, won't you run into a race
>> condition?
>
> The backend in my actual case is reading a web service, which can be hammered
> by overlapping calls. All of the callbacks will run on the UI thread, so they
> can't step on each other. I hope I'm right?! -- Greg
Re: Parallel tasks
On 27 April 2016 at 17:49, Greg Keogh wrote: > If you are not waiting for the tasks to finish, won't you run into a race >> condition? >> > > The backend in my actual case is reading a web service, which can be > hammered by overlapping calls. All of the callbacks will run on the UI > thread, so they can't step on each other. I hope I'm right?! -- *Greg* > Sounds right, as long as UpdateUI can run at any time, in any order. You might also need to deal with the situation where two async tasks are running concurrently for the same "operation", e.g. hitting refresh a second time before the first completes. One way to avoid this is to disable refresh while a refresh is already in progress, but that can be limiting/annoying for the user. Instead, I normally defer updating the UI until the latest request completes. That way it doesn't flicker with stale responses before settling down, and you are guaranteed to leave the form showing the result for the latest request. Note that since they are running concurrently, some stale responses may also come in after the one you were waiting for. These should be discarded as well. -- Thomas
Re: Parallel tasks
> > If you are not waiting for the tasks to finish, won't you run into a race > condition? > The backend in my actual case is reading a web service, which can be hammered by overlapping calls. All of the callbacks will run on the UI thread, so they can't step on each other. I hope I'm right?! -- *Greg*
Re: Parallel tasks
Hi Greg
If you are not waiting for the tasks to finish, won't you run into a race
condition?
Davy
Sent from my iPhone
On 27 Apr 2016, at 09:14, Greg Keogh wrote:
>> You can shorten "f.Invoke()" to just "f()". You can elide "r" as well:
>>
>> new Action(async () => UpdateUI(await GetStuffAsync("key1")))();
>> new Action(async () => UpdateUI(await GetStuffAsync("key2")))();
>> new Action(async () => UpdateUI(await GetStuffAsync("key3")))();
>
> I like that shortening, and it's in my code now. I forgot you can go
> GetFunc()() because it looks so weird. Exception handling isn't a worry in
> this case, luckily -- Greg
Re: Parallel tasks
>
> You can shorten "f.Invoke()" to just "f()". You can elide "r" as well:
>
> new Action(async () => UpdateUI(await GetStuffAsync("key1")))();
> new Action(async () => UpdateUI(await GetStuffAsync("key2")))();
> new Action(async () => UpdateUI(await GetStuffAsync("key3")))();
>
I like that shortening, and it's in my code now. I forgot you can go
GetFunc()() because it looks so weird. Exception handling isn't a worry in
this case, luckily -- *Greg*
