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]

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]

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

 

[Guest]

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_BulkXmlInsert]
AS
DECLARE @c xml
BEGIN
SELECT @c = BulkColumn FROM OPENROWSET(BULK
'C:\Inetpub\wwwroot\uploads\Templates\XMLUpload.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('NewDataSet/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