Upload Excel Sheet -> Import to SS 2005

[Kbalz]

Hello, I'm trying to create an application for a friend's business. He
gets Excel Spreadsheets from a lab - he would like for his partners to
be able to Upload this Sheet to a website, and have the page import the
sheet and add the data to his existing SS 2005 database.

Is this possible, and where should I begin looking? Thanks,

[JosephByrns]

Re: Upload Excel Sheet -> Import to SS 2005

Sounds all very doable, depends I suppose on the content of the spreadsheet
as to how difficult it might be.

You can use the FileUpload control to upload the file to the server, once on
the server you can use ADO.NET to access the spreadsheet and enter the data
into the database. You will have to search for the necessary connection
string for accessing an Excel document as I have never tried it.


"Kbalz" <kbalczak@edg e-sweets.comwrote in message
news:1162916321 .019594.60520@e 3g2000cwe.googl egroups.com...

Hello, I'm trying to create an application for a friend's business. He
gets Excel Spreadsheets from a lab - he would like for his partners to
be able to Upload this Sheet to a website, and have the page import the
sheet and add the data to his existing SS 2005 database.
>
Is this possible, and where should I begin looking? Thanks,
>

[Kbalz]

Re: Upload Excel Sheet -&gt; Import to SS 2005

Thanks for the Reply Joseph - I will look into those steps. The Excel
Sheet is always the same columns & types so that part should be
regular. I'll reply in a few days when I have some code to work with -
thx a bunch,

Kurt

JosephByrns wrote:

Sounds all very doable, depends I suppose on the content of the spreadsheet
as to how difficult it might be.
>
You can use the FileUpload control to upload the file to the server, once on
the server you can use ADO.NET to access the spreadsheet and enter the data
into the database. You will have to search for the necessary connection
string for accessing an Excel document as I have never tried it.
>
>
"Kbalz" <kbalczak@edg e-sweets.comwrote in message
news:1162916321 .019594.60520@e 3g2000cwe.googl egroups.com...

Hello, I'm trying to create an application for a friend's business. He
gets Excel Spreadsheets from a lab - he would like for his partners to
be able to Upload this Sheet to a website, and have the page import the
sheet and add the data to his existing SS 2005 database.

Is this possible, and where should I begin looking? Thanks,

[barranr]

RE: Upload Excel Sheet -&gt; Import to SS 2005

The way i do it is upload the excel file, i then run an application that
extracts the data to an xml file (there are samples on codeproject, don't use
the saveas in excel to create the xml)
Then i run an sp like:
CREATE PROCEDURE [dbo].[usp_BulkXmlInse rt]
AS
DECLARE @c xml
BEGIN
SELECT @c = BulkColumn FROM OPENROWSET(BULK
'C:\Inetpub\www root\uploads\Te mplates\XMLUplo ad.xml', SINGLE_BLOB) AS A
INSERT INTO thetable SELECT T.C.value('F1[1]', 'nVARCHAR(50)') ,
T.C.value('F2[1]', 'nVARCHAR(50)') ,
T.C.value('F3[1]', 'nVARCHAR(50)') ,
T.C.value('F4[1]', 'nVARCHAR(50)') ,
T.C.value('F5[1]', 'nVARCHAR(150)' ),
T.C.value('F6[1]', 'nVARCHAR(250)' ),
T.C.value('F7[1]', 'nVARCHAR(50)') ,
T.C.value('F8[1]', 'nVARCHAR(50)') ,
T.C.value('F9[1]', 'nVARCHAR(50)') ,
T.C.value('F10[1]', 'nVARCHAR(50)') ,
T.C.value('F11[1]', 'nVARCHAR(50)')
FROM @c.nodes('NewDa taSet/Import_x007C_A3 _x003A_J9815') AS T(C)
END

note Import_x007C_A3 _x003A_J9815' is the name of the Sheet and a node in the
xml file

Rod

"Kbalz" wrote:

Hello, I'm trying to create an application for a friend's business. He
gets Excel Spreadsheets from a lab - he would like for his partners to
be able to Upload this Sheet to a website, and have the page import the
sheet and add the data to his existing SS 2005 database.
>
Is this possible, and where should I begin looking? Thanks,
>
>