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

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!
victhetechAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib KhanSenior DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
victhetechAuthor Commented:
Thank you! I will try that and let you know
0
Brad Dobyns, CSMScrumMasterCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

victhetechAuthor Commented:
Brad,

Thanks for the link, but the issue I had was quite different.
0
victhetechAuthor Commented:
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
Saqib KhanSenior DeveloperCommented:
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
victhetechAuthor Commented:
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
Saqib KhanSenior DeveloperCommented:
SQL = "INSERT INTO tester (name,age) values ('name','age')  
0
victhetechAuthor Commented:
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
Saqib KhanSenior DeveloperCommented:
you can see my email address under my profile.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.