importing into an access database using an uploaded file

aamaker · Jun 14, 2004

I have an Access database that Im using to store contact records - businesses and individuals and the personal data for each, in a simple table.

I am able to upload a file using a simplistic asp upload routine, but my question is how I might go about taking a newly uploaded file (excel, .txt???) and importing it into access.

Does anyone have a working example of how something like this is achieved? Anyone seen a tutorial related to this?

Thanks.

CassidyHunt · Jun 14, 2004

The problem I see with this is making sure the fields are going to be the same every time you upload the file. If your the only one doing it then that is fine. If you are going to have multiple users doing this then in my mind you might be better off having them use a standard export utility like Microsofts contacts in Outlook export to an Access database; then upload that file and use ADO to merge the lists. This way you know the fields will be the same everytime.

You can still achieve this using ADO with a txt file or xls file its just you have to make sure its all the same every time. The code isn't hard and I can post an example of how to open up the objects and get data if you can tell me exactly the method your going to use.

Thanks

Cassidy

aamaker · Jun 14, 2004

Awesome. There will be 2 to 3 users that will be appointed to receive, check then upload the files.

I'll be providing each person with a 'template' to follow / use (in this sense, excel would be better I guess - as opposed to a text file).

The method, I guess, is best described as

1.) user is able to browse their local machine for a file (excel file?) then uploads that file to a specified folder (already have a routine I'll use for this function).

2.) once the file is uploaded successfully, there is a variable passed that has name of the newly uploaded file,

3.) then - I guess - a script of some type 'looks at' that file, and imports or appends it to the existing table.

- I may even go so far as to always create a copy (assuming this is something I can achieve with ADO) of the database before the append/import function is run)... that way if something 'ugly' happens, I can always refer to an earlier copy of the database.

Thanks for any light you can shed on this for me.

-A

CassidyHunt · Jun 14, 2004

First here is the code to open up an excel file with ADO

Code:

<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
</HEAD>
<BODY>

<% Option Explicit

Dim adoCn
Dim adoRs
Dim adoFld
Dim strQuery
Dim strResults

Const adUseClient = 3
Const adClipString = 2

Const strColDelim = "&nbsp;&nbsp;</td><td>"
Const strRowDelim = "</td></tr><tr><td>"

Set adoCn = CreateObject("ADODB.Connection")
Set adoRs = CreateObject("ADODB.Recordset")
With adoCn
	.Provider = "Microsoft.Jet.OLEDB.4.0"
	.ConnectionString = _
		"Data Source=d:\ExcelAdo\Island.xls;" & _
		"Extended Properties=Excel 8.0;"
	.CursorLocation = adUseClient
	.Open
End With

Here is the SQL statement you will have to use to extract the data into a recordset

Code:

SELECT * FROM [Sheet1$]
or
SELECT * FROM Range1
or
SELECT * FROM [Sheet1$E11:F23]

Now the SQL code to open your database in Access

Code:

dim cn
dim rs

Set cn = Server.Createobject("ADODB.Connection")
Set rs = Server.Createobject("ADODB.Recordset")

with cn
   .Provider = "Microsoft.Jet.OLEDB.4.0"
   .Connectionstring = "Path to your Microsoft Database"
   .open
end with

rs.open "Select * from Table",cn,3,3

If you need a more complete example of handling excel in ASP at this location:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnasdj01/html/asp0193.asp

I used the code from there to create the example. If you still need more information let me know. Might be tomorrow before I can answer because I am heading home for the night but I will respond.

Thanks

Cassidy

mgagliardi · Aug 12, 2005

I need help in appending an excel spreadsheet to a table in my web based access database. The spreadsheet and the table have the exact same field names. Any help would be DEEPLY appreaciated.

DotNetGnat · Aug 12, 2005

please post your question in a new thread instead of pulling up the old thread...

if i understand it correctly...you just need to use

<%response.ContentType="application/vnd.ms-excel"%>

on top of your asp page to show your results in the excel format..

please clarify the question if this is not what you want to do...

-DNG

Sheco · Aug 12, 2005

DNG - I kinda like it when people acutally use the "Search" feature before asking the same old questions over and over.

mgagliardi - Are you trying to copy data out of an excel spreadsheet into Access or copy it out of Access and display it to the user as an Excel spreadsheet?

DotNetGnat · Aug 12, 2005

Sheco, I agree that using the search functionality is a good idea...but the posters can refer to the old thread using a link and describe their problem in the new thread...i think this is convenient because...if the old thread already as around 20 posts then the poster posts his or her own post as 21 then i am sure he is losing his chances to get a correct solution or attention for his post...

hope you got my point...

thanks

-DNG

Sheco · Aug 12, 2005

good point

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

importing into an access database using an uploaded file

aamaker

Programmer

CassidyHunt

IS-IT--Management

aamaker

Programmer

CassidyHunt

IS-IT--Management

mgagliardi

Technical User

DotNetGnat

Programmer

Sheco

Programmer

DotNetGnat

Programmer

Sheco

Programmer

Similar threads

Part and Inventory Search

Sponsor