|
|
|
Grasshopper
 
Group: General Forum Members
Last Login: Saturday, February 13, 2010 11:03 AM
Points: 13,
Visits: 48
|
|
So this works fine
SELECT *
FROM
OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=D:\TestJET.xls;
Extended Properties=''Excel 12.0;HDR=NO;IMEX=1;ImportMixedTypes=Text''')...[Sheet1$]
But i'd like to not have to use files on the system at all...
We take excel files uploaded (via asp.net) and store them directly into a DB.... I'm looking for a way to just directly read that file from the DB....
Is there a way to do the above but set the DATASOURCE to a column in a DB?
or is it possible to somehow use
sp_addlinkedserver? to create a linked server to that data in the DB? and than use that?
or other?
Thank you in advance
|
|
|
|
|
SSChampion
Group: Administrators
Last Login: Yesterday @ 10:25 PM
Points: 23,148,
Visits: 6,914
|
|
This is directly reading from the DB. The command is sent to the server and then the server executes this.
You can set this up as a linked server, but you want to be careful as the server is then accessing the spreadsheet. If something changes or it moves, you could throw lots of errors.
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Saturday, February 13, 2010 11:03 AM
Points: 13,
Visits: 48
|
|
??
the excel file lives in the Database....
I want to eliminate the step of having to read from a file on the system...
The user uploads an excel file
This file is saved directly to a database column
I don't want to have to save that excel file to the file system at any point in the process....
so right now the only way I've figured out to read the excel file data into C# is to do something like this
in C#
"select ExcelFile From ExcelTable" //Excel file is a varbinary field which holds an excel file
Than take that byte data and save it to some temporary file on the file system
Then create an OLEDBDataReader and pass it
SELECT *
FROM
OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=D:\TestJET.xls;
Extended Properties=''Excel 12.0;HDR=NO;IMEX=1;ImportMixedTypes=Text''')...[Sheet1$]
Which yields a datareader or a datatable that I can then use to access the data....
What I'd PREFER to be able to do is pass it something like
SELECT *
FROM
OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=select ExcelFile From ExcelTable;
Extended Properties=''Excel 12.0;HDR=NO;IMEX=1;ImportMixedTypes=Text''')...[Sheet1$]
(I know I can't pass it a direct select statment but you get the idea)
or
SELECT *
FROM
OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=http://localhost/TestJET.xls;
Extended Properties=''Excel 12.0;HDR=NO;IMEX=1;ImportMixedTypes=Text''')...[Sheet1$]
(I've tried this as well but I get the error)
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Invalid internet address.".
And I can't find any documentation on "Invalid internet address"
What I would just do in the past was use the office web components to read the byte data from the uploaded file and than manage it all that way...
But those have been obsolesced in the presence of Excel Services which I guess I 'll end up setting up if my above scenarios are impossible
Thanks
|
|
|
|
|
SSChampion
Group: Administrators
Last Login: Yesterday @ 10:25 PM
Points: 23,148,
Visits: 6,914
|
|
There's no way to do this I'm aware of right now. The iFTS indexing service in 2008 can index an Excel file, but I'm not sure you can consume it as a table since it's stored in the table already.
If these are XLSX files, they're XML anyway, so you could potentially read them that way, but if it's a binary file, there isn't a way to do it that I'm aware of.
|
|
|
|
|
SSChampion
Group: Administrators
Last Login: Yesterday @ 10:25 PM
Points: 23,148,
Visits: 6,914
|
|
I go this from a developer at MS:
= = = = =
Yes, using DQ, the Jet OLE/DB provider, and the Excel IISAM.
I recommend you get the Jet Programmer's Reference Guide (or whatever it was
called). Should be in MSDN. There may even be an example in BOL.
You may need to install Excel on your server.
= = = =
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Thursday, November 12, 2009 10:24 AM
Points: 1,
Visits: 0
|
|
| I have the same problem now. Somebody found the solution for this?
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: 2 days ago @ 10:57 AM
Points: 136,
Visits: 430
|
|
| How about using SSIS to handle importing the data into tables?
|
|
|
|
