Solved

Batch upload of data from a PC to MSaccess db.

Posted on 2001-06-14
30
302 Views
Last Modified: 2010-05-18
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
Comment
Question by:perrybond
  • 14
  • 8
  • 6
  • +2
30 Comments
 

Expert Comment

by:mattdunlap
ID: 6192598
if you want them to upload files to the server try ASPUpload

http://www.aspupload.com/

it works pretty well.
0
 
LVL 4

Expert Comment

by:epeele
ID: 6192728
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
 

Author Comment

by:perrybond
ID: 6192763
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
 

Author Comment

by:perrybond
ID: 6192782
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
 
LVL 4

Expert Comment

by:epeele
ID: 6192787
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
 

Author Comment

by:perrybond
ID: 6192790
Sorry Epeele my previous answer was supposed to go before your comment.  Yours looks good, but I will take some time digesting it.
0
 
LVL 4

Expert Comment

by:epeele
ID: 6192793
on the bulk insert I mentioned, the fields in Excel and in the target table will need to match up.
0
 
LVL 4

Expert Comment

by:epeele
ID: 6192799
ahh cross post. :)
0
 
LVL 4

Expert Comment

by:epeele
ID: 6192860
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
 
LVL 4

Expert Comment

by:epeele
ID: 6192870
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
 
LVL 7

Expert Comment

by:weesiong
ID: 6193858
epeele,

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

Regards,
Wee Siong
0
 
LVL 20

Expert Comment

by:Silvers5
ID: 6194034
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
 
LVL 4

Expert Comment

by:epeele
ID: 6194926
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
 
LVL 4

Expert Comment

by:epeele
ID: 6194932
Or read and recreate spreadsheet with OWC if the spreadsheet does not implement Web queries.
0
 
LVL 7

Expert Comment

by:weesiong
ID: 6195541
epeele,

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

Regards,
Wee Siong
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 4

Expert Comment

by:epeele
ID: 6195624
You would know better than me, Wee Siong, since I really never use Excel spreadsheets for the web.  
0
 
LVL 7

Expert Comment

by:weesiong
ID: 6195684
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
 

Author Comment

by:perrybond
ID: 6214183
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
 

Author Comment

by:perrybond
ID: 6214546
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
 
LVL 4

Expert Comment

by:epeele
ID: 6214552
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
 
LVL 4

Accepted Solution

by:
epeele earned 300 total points
ID: 6215016
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
 
LVL 7

Expert Comment

by:weesiong
ID: 6216830
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
 
LVL 7

Expert Comment

by:weesiong
ID: 6216835
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
 
LVL 4

Expert Comment

by:epeele
ID: 6218385
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
 
LVL 7

Expert Comment

by:weesiong
ID: 6218419
epeele,

The code can work in my WinNT :)

Regards,
Wee Siong
0
 
LVL 4

Expert Comment

by:epeele
ID: 6218450
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
 
LVL 7

Expert Comment

by:weesiong
ID: 6218519
epeele,

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

Regards,
Wee Siong
0
 
LVL 4

Expert Comment

by:epeele
ID: 6218587
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
 
LVL 7

Expert Comment

by:weesiong
ID: 6219824
:)
0
 

Author Comment

by:perrybond
ID: 6311631
Sorry for the delay, you know how it is, sometimes reality gets in the way.
Thanks for all your help
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now