?
Solved

Problem importing excel 2003 file into ms access 2003 database table using ASP forms

Posted on 2004-11-12
10
Medium Priority
?
241 Views
Last Modified: 2008-02-01
Plan:
create an ASP page that uploads an ms excel 2003 file. Once file is uploaded create another ASP page with an import button, when the button on the form is pressed the server will read the just uploaded excel 2003 file then import the data into an ms access 2003 database table located at the server, as a way to assure that data import worked succesfully have the same ASP page notify that import was succesfull and it should show how many records were succesfully imported into database.

Current File Structure:
MS Excel 2003 File - with 15 columms and 75 rows, some fields on the rows contain no data
MS Access 2003 database table with field names exactly the same as on the excel file.

Problem:
I had figured out all this by using a CSV file instead of the excel file but the problem was that due to the csv file having some cell with no data the import would work fine on the cells with data but it would fail on the empty cells by returning an error message on the ASP page about values on the csv not matching the number of field on the database.
I have reviewed 2 other postings on this website about a similar issue, but none seemed to resolve the problem. Any help would be appreciated!
0
Comment
Question by:victhetech
  • 5
  • 4
10 Comments
 
LVL 23

Accepted Solution

by:
Saqib Khan earned 250 total points
ID: 12568876
Connect to Excel SPread Sheet using dns less connection and read it as a Regular Data File.


<%




'*******************

' in Excel [Workbook] is the [Table name] and the very [first ROW] in the Work book is the [Field Name]
' Connection String is Same as Ms Access

'*******************



      ' Hard code the Path of Excel Spread Sheet
Xpath = Server.Mappath("db/profile.xls")

Set X = Server.CreateObject("ADODB.CONNECTION")
Set Rx = Server.CreateObject("ADODB.RECORDSET")

X.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
"DBQ=" & Xpath


      ' users is the name of Workbook (Same as Table Name)
xSql = "Select * from [users$]"

Rx.open xSql, X

            ' Remember very First field in EXCEL is the field name in the Workbook(Table)
      while not Rx.eof
            Response.write Rx("Name") &" - "& Rx("Age") & "<br>"
      Rx.moveNext
      Wend



%>

0
 

Author Comment

by:victhetech
ID: 12569067
Thank you! I will try that and let you know
0
 
LVL 7

Expert Comment

by:Brad Dobyns, CSM
ID: 12569282
Here is one that might help you unless this is one you looked at:

http://www.experts-exchange.com/Databases/MS_Access/Q_20979395.html

Brad

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:victhetech
ID: 12570254
Brad,

Thanks for the link, but the issue I had was quite different.
0
 

Author Comment

by:victhetech
ID: 12572191
adilkhan,

the code you provided prints the excel contents on the asp page itself, what I need is to import the contents of the excel file into the access database table once is finished importing I need the numbers or records imported into the database displayed on the asp page. Any suggestions?
0
 
LVL 23

Expert Comment

by:Saqib Khan
ID: 12572301
Well in the Code where it Prints the Lines on the WebPage RUN your Insert Statement.


 > Response.write Rx("Name") &" - "& Rx("Age") & "<br>"
    Insert into Table(Value1) Values('Something')
    iCount = iCoint + 1


This way you will keep on writing on Access Table and iCoutn vairable will return Total Number of Infected Rows.

Good Luck
0
 

Author Comment

by:victhetech
ID: 12572883
When I use the following code:

________________________________________________________________________________

' Open database connection
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open ConnString

'my database table name is "tester" and the fields in it are ID, name and age for testing purposes      
SQL = INSERT INTO "tester" ("name","age") values ("name","age")    
       
    ' Execute SQL
Conn.Execute (SQL)
   
    iCount = iCount + 1

         
     Rx.moveNext
     Wend

Response.write "Done :)<BR><BR>"

Set Conn = Nothing

_______________________________________________________________________


I get the following error:
Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/test/tester.asp, line 48, column 13
SQL = INSERT INTO "tester" ("name","age") values ("name","age")

Please help!
0
 
LVL 23

Expert Comment

by:Saqib Khan
ID: 12575833
SQL = "INSERT INTO tester (name,age) values ('name','age')  
0
 

Author Comment

by:victhetech
ID: 12577594
adilkhan You missed the " at the end of the statement which is ok with me. Could you please email me your email address just in case I need help in the future, I may need from your paid services my email is victorvillatoro@hotmail.com, I really appreciate your help on this and yes I'll give you the points! but for those who are still looking for a complete ASP code to import the contents of an MS Excel File to an MS Access Database feel free to use the one below and customize it to our own needs. Enjoy!


<body>

<%

'************************************
'The code in this page will automatically read the MS Excel file located at the server
'then it will import the data into an the MS Access Database located at the server
'************************************

      'Inform user where to locate the results of import
Response.write "Please scroll to the bottom of this page to view the results of the import, the following data is being imported...<br>"
Response.write "<br>"      

      'Identify the path of MS Excel file
Xpath = Server.Mappath("tester.xls")

      'Create server variables      
Set X = Server.CreateObject("ADODB.CONNECTION")
Set Rx = Server.CreateObject("ADODB.RECORDSET")

      'Open the MS Excel file using the server Excel drivers
X.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
"DBQ=" & Xpath

     'Identify the name of the MS Excel Workbook where data is located
xSql = "Select * from [qRptWorkOrdersForContractors$]"
      
      'Open workbook of Excel file
Rx.open xSql, X

      'Don't include the firs row of recordset from first row in Excel
while not Rx.eof

      'Print on the html page the records located on the Excel file
Response.write Rx("Name") &"   |   "& Rx("Age") & "<br>"
         
      'Open MS Access database connection
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open = "DSN=test"      

      'Use a SQL statement to insert the data from Excel columns and rows to Access database table fields
SQL = "INSERT INTO tester (name,age) values ('" & Rx("Name") &"','" & Rx("Age") &"')"

      'Show on html page that this records has been imported
Response.write "The above line was succesfully read from Excel and imported into server database!<br>"      

      'Define a variable to count how many records are imported
iCount = iCount + 1

      'Execute the SQL statement    
Conn.Execute (SQL)

      'Execute the next line of records from Excel into Access
Rx.moveNext

      'End the execution when the records are done importing
Wend

      'Print results on html page
Response.write "<br>"
Response.write "The above information has been succesfully processed <br>"
Response.write "A total of " & iCount & " records were succesfully imported into the server database"

      'Close the opened connection to Excel file
Set X = nothing

      'Close the opened connection to Access Database
Set Conn = Nothing

%>


</body>
0
 
LVL 23

Expert Comment

by:Saqib Khan
ID: 12579836
you can see my email address under my profile.
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Integration Management Part 2
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

840 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