[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:

Batch upload of data from a PC to MSaccess db.

I have an ASP input form for people to add a record to my database. I now want to give people a 'batch submit' option, where they can add a number of records at once.
People using this option will already have the data, most likely on an excel spreadsheet. So I think I will give them a preformatted spreadsheet to use.  Then online, I want a way where they can point to the file on their own PC and upload it into the online database. Any ideas, suggestions/examples of how to go about this?
 
0
perrybond
Asked:
perrybond
  • 14
  • 8
  • 6
  • +2
1 Solution
 
mattdunlapCommented:
if you want them to upload files to the server try ASPUpload

http://www.aspupload.com/

it works pretty well.
0
 
epeeleCommented:
To add to what mattdunlap said, you could use an Upload component to upload the Excel file from the client machine to the server.  Once on the server, you can create a connection to the spreadsheet, much like a database, and do a bulk insert into your database.

For the upload component, you could build your own (how to with source code):
http://www.devx.com/upload/free/features/vbpj/2000/05may00/id0500/id0500.asp

Once on the server, you could do something like:

<%

set objConn = Server.CreateObject("ADODB.Connection")
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=\somepath\myExcelSpreadsheet.xls;" & _
    "Extended Properties=""Excel 8.0;HDR=Yes;"";"

'Where "HDR=Yes" means that there is a header row in the
'cell range (or named range), so the provider will not
'include the first row of the selection into the
'recordset.  If "HDR=No", then the provider will include
'the first row of the cell range (or named ranged) into
'the recordset.

objConn.Open (strConn)

strSQL = "INSERT INTO tblYour (SELECT * FROM myExcelSpreadsheet.xls)"

objConn.Execute(strSQL)

%>
0
 
perrybondAuthor Commented:
Sorry, this is a community project, so no money is available! Furthermore, I've only offered to do it as a learing process; I'm not going to learn much buying an off-the-shelf solution.
I was thinking more along the lines of exporting the spreadsheet to a text file, then OpenAsTextStream and working down the file submitting each line into the database.......or something like that.  I know nothing about excel always using MSaccess, so I don't know if it can be read directly
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
perrybondAuthor Commented:
Sorry, this is a community project, so no money is available! Furthermore, I've only offered to do it as a learing process; I'm not going to learn much buying an off-the-shelf solution.
I was thinking more along the lines of exporting the spreadsheet to a text file, then OpenAsTextStream and working down the file submitting each line into the database.......or something like that.  I know nothing about excel always using MSaccess, so I don't know if it can be read directly
0
 
epeeleCommented:
The component link I listed above is free. No cost for anything I've listed above.

Excel can be read directly.  Or you can export to delimited text and read the stream as you indicated.  Either way, you'll need to upload to the server.  You can either build a component yourself for free as I suggested or use VBScript which is not as scalable.  Instructions with code:
http://www.aspzone.com/articles/john/aspUpload/
0
 
perrybondAuthor Commented:
Sorry Epeele my previous answer was supposed to go before your comment.  Yours looks good, but I will take some time digesting it.
0
 
epeeleCommented:
on the bulk insert I mentioned, the fields in Excel and in the target table will need to match up.
0
 
epeeleCommented:
ahh cross post. :)
0
 
epeeleCommented:
In my Excel query it should have been:

SELECT * FROM 'myExcelSpreadsheet'

not

SELECT * FROM myExcelSpreadsheet.xls

Example from Microsoft below:

<%@Language=VBScript %>
<html>
<head>
<title> Displaying An Excel Spreadsheet in an Web Page </title>
</head>
<body bgcolor="#FFFFFF" text="#000000" >
<h1>ASP Table of Contents</h1>
<%
?Creates an instance of an Active Server Component
 Set oConn = Server.CreateObject("ADODB.Connection")
?Connects to the Excel driver and the Excel spreadsheet
?in the directory where the spreadsheet was saved
strConn = "Driver={Microsoft Excel Driver (*.xls)}; DBQ=C:\Inetpub\Wwwroot\Tutorial\ASPTOC.xls;"
?Opens the connection to the data store
 oConn.Open strConn
?Selects the records from the Excel spreadsheet
strCmd = "SELECT * from `ASPTOC`"
Set oRS = Server.CreateObject("ADODB.Recordset")
?Opens the recordset
oRS.Open strCmd, oConn
?Prints the cells and rows in the table
Response.Write "<table border=1><tr><td>"
?Gets records in spreadsheet as a string and prints them in the table
Response.Write oRS.GetString (, , "</tr><td>", "</td></tr><tr><td>", NBSPACE)
%>
</body>
</html>
0
 
epeeleCommented:
Reposting because the previous post did not format well.

<%@Language=VBScript %>
<html>
<head>
<title> Displaying An Excel Spreadsheet in an Web Page </title>
</head>
<body bgcolor="#FFFFFF" text="#000000" >
<h1>ASP Table of Contents</h1>
<%
  'Creates an instance of an Active Server Component
   Set oConn = Server.CreateObject("ADODB.Connection")

  'Connects to the Excel driver and the Excel spreadsheet
  'in the directory where the spreadsheet was saved
  strConn = "Driver={Microsoft Excel Driver (*.xls)};DBQ=C:\Inetpub\Wwwroot\Tutorial\ASPTOC.xls;"

  'Opens the connection to the data store
  oConn.Open strConn

  'Selects the records from the Excel spreadsheet
  strCmd = "SELECT * from `ASPTOC`"
  Set oRS = Server.CreateObject("ADODB.Recordset")

  'Opens the recordset
  oRS.Open strCmd, oConn

  'Prints the cells and rows in the table
  Response.Write "<table border=1><tr><td>"

  'Gets records in spreadsheet as a string and prints them in the table
  Response.Write oRS.GetString (, , "</tr><td>", "</td></tr><tr><td>", NBSPACE)

%>
</body>
</html>
0
 
weesiongCommented:
epeele,

But the ODBC for Excel just can select out the data, it no support Edit/Delete function() :(

Regards,
Wee Siong
0
 
Michel SakrCommented:
you can let them upload access databases and perform a fetch from 1 to another db on the server.. you can make a VB application that will scan the databases each time a db is uploaded and perform the updates then delete the uploaded database if no errors were encountered, this solution is optimal if you don't want to give the IUSR extra privelieges to delete uploaded mdb's.. anyways epeele's solution is the one you seek, but you need to optimise it

rgrds
0
 
epeeleCommented:
Wee Siong,

You're right.  Fortunately, you now can use Web queries in MSExcel to update data within a spreadsheet.

http://www.15seconds.com/issue/991021.htm

However, in this instance, perrybond is inserting the data from a spreadsheet into a table and no modification of the spreadsheet is required.
0
 
epeeleCommented:
Or read and recreate spreadsheet with OWC if the spreadsheet does not implement Web queries.
0
 
weesiongCommented:
epeele,

It is a nice site, but i more like using Excel.Application to update/edit the Data :p

Regards,
Wee Siong
0
 
epeeleCommented:
You would know better than me, Wee Siong, since I really never use Excel spreadsheets for the web.  
0
 
weesiongCommented:
epeele,

actually i also no like using this, when small record, it run as well, but run more will be slowly, and easy down.

Sample code:

<%
ExColumn = 2 'the second column want to edit
Application.Lock
Set Application("ExcelSheet") = CreateObject("Excel.Application")
Application("ExcelSheet").Workbooks.Open (Server.MapPath("Book1")).Activate
Application("ExcelSheet").Application.Visible = False

Application("ExcelSheet").Cells(ExColumn, 2) = "'" & TRIM(Request("T1"))
Application("ExcelSheet").Cells(ExColumn, 3) = "'" & TRIM(Request("T2"))
Application("ExcelSheet").Cells(ExColumn, 4) = "'" & TRIM(Request("T3"))
Application("ExcelSheet").Cells(ExColumn, 5) = TRIM(Request("T4"))
Application("ExcelSheet").Cells(ExColumn, 6) = TRIM(Request("T5"))
Application("ExcelSheet").Cells(ExColumn, 7) = TRIM(Request("T6"))
Application("ExcelSheet").Cells(ExColumn, 8) = TRIM(Request("T7"))
Application("ExcelSheet").Cells(ExColumn, 9) = TRIM(Request("T8"))
Application("ExcelSheet").Cells(ExColumn, 10) = TRIM(Request("T9"))
Application("ExcelSheet").Cells(ExColumn, 11) = TRIM(Request("T10"))
Application("ExcelSheet").SaveApplication("ExcelSheet").Application.Quit
Set Application("ExcelSheet") = Nothing
Application.UnLock
%>

Regards,
Wee Siong
0
 
perrybondAuthor Commented:
For epeele
I've been trying to use your code, but I get an error with the SQL:

Microsoft JET Database Engine error '80040e14'
Syntax error in INSERT INTO statement.
/Newforest/thediary/upload.asp, line 21

The code is:

<%
'   "Data Source=" & Server.MapPath & "diaryaddtest.xls;" & _

set objConn = Server.CreateObject("ADODB.Connection")
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
   "Data Source=" & Server.MapPath("diaryadd.xls") & ";" & _
   "Extended Properties=""Excel 8.0;HDR=Yes;"";"
'Where "HDR=Yes" means that there is a header row in the
'cell range (or named range), so the provider will not
'include the first row of the selection into the
'recordset.  If "HDR=No", then the provider will include
'the first row of the cell range (or named ranged) into
'the recordset.

objConn.Open (strConn)

strSQL = "INSERT INTO DiaryData (SELECT * FROM 'diaryaddtest')"

objConn.Execute(strSQL)

Response.write "strConn = " & strConn & "<br>"
Response.write "strSQL = " & strSQL & "<br>"
%>


should the strSQL have a ";" on the end ?
should the database be DiaryData or 'DiaryData' or DiaryData.mdb or 'DiaryData.mdb'


0
 
perrybondAuthor Commented:
Sorry, silly question.
Iguess you have to open the database and the spreadsheet before appending the info. but how do you do that?

I've tried this (but it doesn't work):

<%
Dim DB_CONNECTIONSTRING
     DB_CONNECTIONSTRING = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("DiaryData.mdb") & ";"
     Dim objRecordset
     Set objRecordset = Server.CreateObject("ADODB.Recordset")
     objRecordset.Open "Diary", DB_CONNECTIONSTRING, adOpenKeyset, adLockPessimistic, adCmdTable
     objRecordset.CacheSize = 15


set objConn = Server.CreateObject("ADODB.Connection")
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
   "Data Source=" & Server.MapPath("diaryadd.xls") & ";" & _
   "Extended Properties=""Excel 8.0;HDR=Yes;"";"
'Where "HDR=Yes" means that there is a header row in the
'cell range (or named range), so the provider will not
'include the first row of the selection into the
'recordset.  If "HDR=No", then the provider will include
'the first row of the cell range (or named ranged) into
'the recordset.

objConn.Open (strConn)

strSQL = "INSERT INTO Diary (SELECT * FROM 'diaryaddtest');"

objConn.Execute(strSQL)

Response.write "strConn = " & strConn & "<br>"
Response.write "strSQL = " & strSQL & "<br>"


%>

0
 
epeeleCommented:
Working on a working example now.  You have to create two connection objects, one for the database and one for the spreadsheet, then select into a recordset from the Excel spreadsheet, loop through it while inserting the data into the Access db.  I'll have some code for you soon.
0
 
epeeleCommented:
ok, the following code works and imports data into an Access db using an Excel spreadsheet as the source.

<%@ Language=VBScript %>
<%
on error resume next

'create connection and recordset for MS Excel Spreadsheet
set objConn = Server.CreateObject("ADODB.Connection")
set objRS = Server.CreateObject ("ADODB.Recordset")

'create connection and recordset for MS Access DB
set objConn1 = Server.CreateObject ("ADODB.Connection")
set objRS1 = Server.CreateObject ("ADODB.Recordset")


'create connection string for MS Excel Spreadsheet
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  "Data Source=" & Server.MapPath("Book1.xls") & ";" & _
  "Extended Properties=""Excel 8.0;HDR=Yes;"";"

'create connection string for MS Access DB
strConn1 = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  "Data Source=" & Server.MapPath("Posters.mdb")

'open connections  
objConn.Open (strConn)
objConn1.Open (strConn1)

'Select from worksheet  $ must follow name of sheet
strSQL = "SELECT * FROM [Sheet1$]"
objRS.Open strSQL, objConn, 3, 3

'select from table in db
strSQL1 = "SELECT * FROM Poster_Index"
objRS1.Open strSQL1, objConn1, 3, 3

'loop through rows in spreadsheet
While not objRS.EOF
     
     'add new record to db
     objRS1.AddNew
     
     'fields must match up
     for i = 0 to objRS.Fields.Count - 1
          objRS1.Fields(i) = objRS.Fields(i)
     next

     objRS.Movenext
Wend

'commit all added records to db
objRS1.UpdateBatch ()

'check to see if it worked
if err <> 0 then
     Response.Write "Migration Failed: " & Err.Description
else
     Response.Write "Migration Succeeded."
end if

'clean up
objRS.Close()
objRS1.Close()
objConn.Close()
objConn1.Close()
set objRS = nothing
set objRS1 = nothing
set objConn = nothing
set objConn1 = nothing

%>
0
 
weesiongCommented:
perrybond,

No need a long code, if you want import data from Excel to Access

<%
Set Access = Server.CreateObject("Access.Application")
Access.DoCmd.OpenCurrentDatabase Server.MapPath("db1.mdb")
Access.DoCmd.Visible = True
Access.DoCmd.TransferDatabase acImport, "Microsoft Excel", Server.MapPath("xxx.xls"), acTable, "AccessTable", "ExcelTable"
Access.Docmd.Quit
Set Access = Nothing
%>

Regards,
Wee Siong
0
 
weesiongCommented:
perrybond,

Sorry, Access.DoCmd.Visible = False 'visible the Access

<%
Set Access = Server.CreateObject("Access.Application")
Access.DoCmd.OpenCurrentDatabase Server.MapPath("db1.mdb")
Access.DoCmd.Visible = False
Access.DoCmd.TransferDatabase acImport, "Microsoft Excel", Server.MapPath("xxx.xls"), acTable, "AccessTable", "ExcelTable"
Access.Docmd.Quit
Set Access = Nothing
%>

Regards,
Wee Siong
0
 
epeeleCommented:
Wee Siong may have a point.  Can't test his code here at work because were still on WinNT, but if you have Win2000 you may be better off with Wee Siong's solution.  Otherwise, to use that solution on WinNT, you must set the AspAllowOutOfProcComponents metabase setting in order to call Access.Application.  You can do that, although I don't know how now that MSDN has changed to their new format (cant' find anything and the proper links are pulling up 404 errors)
0
 
weesiongCommented:
epeele,

The code can work in my WinNT :)

Regards,
Wee Siong
0
 
epeeleCommented:
Perhaps your server is setup to allow out of process components.  The error I'm getting from two different NT Servers is this:

Server object error 'ASP 0196 : 80040154'

Cannot launch out of process component

/ddllc/exceltodb.asp, line 3

Only InProc server components should be used. If you want to use LocalServer components, you must set the AspAllowOutOfProcComponents metabase setting. Please consult the help file for important considerations.
0
 
weesiongCommented:
epeele,

You need to change AspAllowOutOfProcComponents to be True in your regedit, this is same in Win2k, PWS :)

Regards,
Wee Siong
0
 
epeeleCommented:
It's obsolete in Win2K (at least that's what MS says) and I know nothing about PWS since I never use it.  I like your solution though and would prefer it over mine.


AspAllowOutOfProcComponents Is Obsolete in IIS 5.0
http://support.microsoft.com/support/kb/articles/Q233/9/68.ASP
Microsoft Internet Information Services (IIS) version 5.0 now ignores the AspAllowOutOfProcComponents metabase property value and sets the default value to TRUE. This is a change from IIS 4.0, where this property was set to FALSE by default.


BTW, found how to change metabase:

PRB: Cannot Launch Out of Process Component Under IIS 4
http://support.microsoft.com/support/kb/articles/Q184/6/82.ASP

The following ASP code segment demonstrates the steps required to set the AspAllowOutOfProcComponents parameter on the IIsWebVirtualDir Admin Object.

   <%
     ' Get the IIsWebVirtualDir Admin Object
     Set VDirObj = GetObject("IIS://LocalHost/W3svc/1/Root/vdir_name")

     ' Enable the AspAllowOutOfProcComponents Parameter
     VDirObj.Put "AspAllowOutOfProcComponents", True

     ' Save the changed value to the metabase
     VDirObj.SetInfo
    %>

NOTE: You will need to restart the Web server after making these changes. To restart the Web server, type the following at a command prompt:

   c:\> net stop iisadmin /y
   c:\> net start w3svc


0
 
weesiongCommented:
:)
0
 
perrybondAuthor Commented:
Sorry for the delay, you know how it is, sometimes reality gets in the way.
Thanks for all your help
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 14
  • 8
  • 6
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now