Uploading a csv file into mdb

ASP Free Forums Sponsor:

October 29th, 2009, 05:37 PM

loy_ramos

Contributing User

Join Date: Oct 2008

Posts: 42

Time spent in forums: 16 h 35 m 16 sec
Reputation Power: 2

Uploading a csv file into mdb

Hi everyone,

can someone point me on how to upload a csv file into an access databse named MPC.mdb with one table named ops with five columns. 1) is the named Ticket Auto number, 2) date 3)status 4) name 5) address. (Using classic asp)

I would be doing this using a form with a browse file field.
Also on the file, one column would be empty which should go to the status column in the mdb.

Im sorry if Im not even able to post a start upcode.
Im totally clueless on how this is done

Thanks in advanced

October 30th, 2009, 07:35 AM

keep_it_simple

KIS

ASP Free Intermediate (1500 - 1999 posts)

Join Date: Jul 2007

Location: USA

Posts: 1,536

Time spent in forums: 3 Weeks 4 Days 20 h 53 m 22 sec
Reputation Power: 845

Send a message via Yahoo to keep_it_simple

the following code assumes this:

Quote:

an access databse named MPC.mdb with one table named ops with five columns. 1) is the named Ticket Auto number, 2) date 3)status 4) name 5) address

no error checking was done - ie: ensuring the file is a .csv (many things to check - this would be another topic/post)

you did not mention if the csv file contains a header (first row has field names)

1. if the csv conatins a header then the HDR = YES. A *(splat) can be used as long as the field names are the same in the database - otherwise use the fieldnames as they appear in the csv file versus the *.

2. If NO then field names would be F!,F2,F3,F4,F5 (Excel default) where the splat is.

you may have to tweak to adjust toward YOUR specs

Code:

<%
  p = Request.Form("p")

  If p <> "" Then
 
   a = Split(p,"\")
   f = a(UBound(a))
   p = Replace(p,f,"")

   Set oConn = Server.CreateObject("ADODB.Connection")
   oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & Server.MapPath("MPC.mdb") & ";" 

   oConn.Execute("INSERT INTO ops (theticket,thedate,thestatus,thename,theaddress) " & _
                 "SELECT * " & _
                 "FROM [Text;HDR=YES;DATABASE=" & p & ";].[" & f & "];")

   m = "FIle uploaded"

  End If
%>


<form action="<%=Request.ServerVariables("SCRIPT_NAME")%>" method="post">
 File: <input type="file" name="p">
 
 <p>

 <input type="submit" value="Upload">
</form>

 <p>

  <%=m%>

__________________
Please give respect to those that helped solve an issue by clicking on the reputation icon

October 30th, 2009, 12:02 PM

loy_ramos

Contributing User

Join Date: Oct 2008

Posts: 42

Time spent in forums: 16 h 35 m 16 sec
Reputation Power: 2

Thanks for the reply sir.

I tried the code and it shows no error but when I check the database its empty

Also, the "file uploaded" is not appearing,

Im not sure but when we place

Code:

Request.ServerVariables

on the form action, what does it do?

why cant i just place the filename of the upload script directly?

also,I forgot to mention that the http keep alive of IIS is disabled.

Thank you for your time

October 30th, 2009, 03:28 PM

keep_it_simple

KIS

Join Date: Jul 2007

Location: USA

Posts: 1,536

Time spent in forums: 3 Weeks 4 Days 20 h 53 m 22 sec
Reputation Power: 845

the 'File Uploaded' is misleading...it simply 'points' to where the file exists and 'imports/extracts" the data (via sql) to an access database. this was done on a development server (my pc), therefore, the file (client side) is on the server - making it very easy to browse and import.

you will need to upload the csv (browse for) with more advanced script...

see here:

http://forums.aspfree.com/code-bank...ures-94647.html

once the file is uploaded to the server you can then use the sql statement i provided to import the data.

since a .csv file is a 'flat' database you can use sql to 'select' the data from the csv and 'insert into' an access database.

to test the snippet immediately (without uploading), ensure the csv file and the access database are in the same directory on the iis. ensure the csv file contains the same field names in the first line and the HDR=YES....or if no header then HDR=NO and provide F1, F2,...to represent the columns

Code:

<%
  f = "thecsvfile.csv" 
  p = Replace(Server.MapPath(f),f,"")

  Set oConn = Server.CreateObject("ADODB.Connection")
  oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & Server.MapPath("MPC.mdb") & ";" 

  oConn.Execute("INSERT INTO ops (theticket,thedate,thestatus,thename,theaddress) " & _
                "SELECT * " & _
                "FROM [Text;HDR=YES;DATABASE=" & p & ";].[" & f & "];")
%>

Comments on this post

loy_ramos agrees: Thanks ansd sorry for the late feedback,I tried on my developement server and it worked,I'll be
searching for the file upload script then! many thanks

Last edited by keep_it_simple : October 30th, 2009 at 03:35 PM.

Viewing: ASP Free Forums > Programming > ASP Development > Uploading a csv file into mdb

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump

Free IT White Papers!

How to Present Effectively Online
This white paper offers practical and actionable advice on the key steps that any presenter should consider as they plan and execute a Webinar or online meeting.

Open Source Security Myths
Open Source Software (OSS) is computer software whose source code is available to the general public with relaxed or non-existent intellectual property restrictions (or arrangement such as the public domain), and is usually developed with the input of many contributors.

Power and Cooling Capacity Management for Data Centers
This paper describes the principles for achieving power and cooling capacity management.

Scalable, Fault-Tolerant NAS for Oracle - The Next Generation
For several years NAS has been evolving as a storage alternative for Oracle databases, and for good reason: NAS is quite often the simplest, most cost-effective storage approach for Oracle. Learn about the benefits that HP's approach to scalable NAS brings to Oracle environments in this comprehensive white paper.

Understanding Web Application Security Challenges
This white paper discusses many common threats and preventive measures for Web application security, and explains what you can do to help protect your organization.

More Free IT White Papers!