ASP database importing

[wolftek]

I have taken over a website where there is a list of stores.
I was told someone used to import an excel file onto the server and that it would update.

What would you need to tell me what it is that I must do for the importing of the .xls file ?

[waolly]

I'm assuming that you are using a SQL database running on SQL server.

What you need to do is import the data in the excel document into a table. Strangely enough I had to do this exact thing today :)

There is a good article detailing a few ways to accomplish this here http://www.databasejournal.com/features/mssql/article.php/3331881

Here is a code snippet if you don't want to read the article. It will read an excel file (c:\excel1.xls) into a SQL table. You can then obviously use an insert to put the output from this query into one of your tables.


select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=c:\excel1.xls', 'Select * from [book1$]')

[miranda]

You can also upload the excel file onto the server and using ado read the data from there.

[Gary Williams]

Hi Waolly and Miranda,

I have exactly the same problem as Wolftek. I need to insert many hundred rows of data from a worksheet in an excel workbook into a table in an Access database.

I have tried to edit the code examples described in this forum and at http://www.databasejournal.com/feat...cle.php/3331881, but I'm missing something fundamental (again!).

This is the situation.

I have an asp compliant server happily running on the net. I have an asp based application on the server that happily works with an Access (.mdb) database which contains several tables.

The database is located at d:\websites\test\data\testdata.mdb

I have uploaded the excel files cantaining the new, additional data to d:\websites\test\data\excel.xls

I do not have MS Access or MS Excel installed on the server.

I can connect to, and read data from, the access database using the following code:

==============

<%
Dim conDatabase
Dim rstDatabase
Dim strdbPath
Dim strSQL

Set conDatabase = Server.CreateObject("ADODB.Connection")

' path to the database
strdbpath = "d:\websites\test\data\testdata.mdb"

' open the connection to the database
conDatabase.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & strdbpath

' Display All Records
strSQL = "SELECT * FROM TABLENAME;"
Set rstDatabase = conDatabase.Execute(strSQL)
Set objRecordset = Server.CreateObject("ADODB.Recordset")
objRecordset.Open strSQL, conDatabase, adOpenStatic, adLockReadOnly, adCmdText

' Test this works
response.write "" & objRecordset.RecordCount & "" & vbCrLf

objRecordset.Close
Set objRecordset = Nothing

conDatabase.Close
Set conDatabase = Nothing
%>

=================

I have tried to cut the following code from Miranda and Will Riley into the above code but can't get the resulting script to work

strSQL = "select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;DATABASE=d:\websites\test\data\excel.xls', 'Select * from [WORKSHEETNAME$]')"

strSQL = "INSERT INTO TABLENAME _
SELECT * FROM [WORKSHEETNAME_Imp$] IN '" _
& ThisWorkbook.FullName & "' 'Excel 8.0;'"

I have tried other suggested script where I can see that the script is looping through the excel worksheet, then using .AddNew to insert into the access database but I can't get this working either.

Any guidance, script gratefully received :)

Regards

Gary