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: [email protected] [mailto:[email protected]] 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 <[email protected]<mailto:[email protected]>> 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 <[email protected]<mailto:[email protected]>> 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(); } } }
