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.xm l', 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
"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,