From excel file into MS SQL server

I need to find a way to upload an Excel file into an MS SQL database
using a web control front end. I have my ASP.Net control (using C#)
uploading a file to a directory, but the server people now tell me that
I cannot have a writeable area for the web and have a DTS see it as this
is too much of a security risk. So, I need a way to read the file
directly into the database. I've no idea how to do this. Does anyone
have ideas? I know loading MS Office into the web server is out of the
question. The webserver and database server are not the same physical
machine.
Thanks.
Yes you can upload any file directly into SQL Server.
Here's an example:
http://SteveOrr.net/Articles/EasyUploads.aspx
I hope this helps,
Steve C. Orr, MCSD, MVP
http://SteveOrr.net
"no one" <noone@.yahoo.com> wrote in message
news:41FEF4E4.7EED35A4@.yahoo.com...
>I need to find a way to upload an Excel file into an MS SQL database
> using a web control front end. I have my ASP.Net control (using C#)
> uploading a file to a directory, but the server people now tell me that
> I cannot have a writeable area for the web and have a DTS see it as this
> is too much of a security risk. So, I need a way to read the file
> directly into the database. I've no idea how to do this. Does anyone
> have ideas? I know loading MS Office into the web server is out of the
> question. The webserver and database server are not the same physical
> machine.
> Thanks.
>
|||Thanks for the link, but this is not what I want to do. I want to put the
data from the file into a table, not the file itself.
"Steve C. Orr [MVP, MCSD]" wrote:
[vbcol=seagreen]
> Yes you can upload any file directly into SQL Server.
> Here's an example:
> http://SteveOrr.net/Articles/EasyUploads.aspx
> --
> I hope this helps,
> Steve C. Orr, MCSD, MVP
> http://SteveOrr.net
> "no one" <noone@.yahoo.com> wrote in message
> news:41FEF4E4.7EED35A4@.yahoo.com...
|||"no one" <noone@.yahoo.com> wrote in message
news:41FF763D.6BFF199A@.yahoo.com...
> Thanks for the link, but this is not what I want to do. I want to put the
> data from the file into a table, not the file itself.
> "Steve C. Orr [MVP, MCSD]" wrote:
>
|||"no one" <noone@.yahoo.com> wrote in message
news:41FF763D.6BFF199A@.yahoo.com...
> Thanks for the link, but this is not what I want to do. I want to put the
> data from the file into a table, not the file itself.
> "Steve C. Orr [MVP, MCSD]" wrote:
>
<snip>
Have a look at DTS - it can load directly from Excel (or most other things)
to MSSQL, and you can change the source and destination connections at
runtime. This link discusses executing a package from ASP:
http://www.sqldts.com/default.aspx?207
Otherwise, you can parse the file and generate your own INSERT statements
(slow), or convert it to a flat text file and then use bcp.exe or BULK
INSERT to load the data.
Simon
|||I'm not sure what all security restrictions they want you to
follow, but one option is to use Openrowset and read the
file into a table using T-SQL, e.g.
insert into YourTable
SELECT * FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0; HDR=NO;Database=C:\YourFilet.xls',
'SELECT * FROM [Sheet1$]')
-Sue
On Tue, 01 Feb 2005 12:25:52 GMT, no one <noone@.yahoo.com>
wrote:
[vbcol=seagreen]
>Thanks for the link, but this is not what I want to do. I want to put the
>data from the file into a table, not the file itself.
>"Steve C. Orr [MVP, MCSD]" wrote:
|||Oh, I now see your dilemma. That's fairly complex functionality.
My only idea is this 3rd party product that can open an excel file from a
memory stream and will allow you to extract data from it:
http://www.SteveOrr.net/Reviews/AsposeWord.aspx
http://www.aspose.com/Products/Aspose.Excel/
I hope this helps,
Steve C. Orr, MCSD, MVP
http://SteveOrr.net
"no one" <noone@.yahoo.com> wrote in message
news:41FF763D.6BFF199A@.yahoo.com...
> Thanks for the link, but this is not what I want to do. I want to put the
> data from the file into a table, not the file itself.
> "Steve C. Orr [MVP, MCSD]" wrote:
>