how to insert a Excel data in SQL server using ASP

[yogesh m]

hi
i want to insert a data into a SQL Server from Excel file using ASP
plz help me

[chaitanya02]

hi
i want to insert a data into a SQL Server from Excel file using ASP
plz help me

===
Hi

I have the same question too. But I tried using DTS to import the excel file to SQL, and while selecting the source file driver- it gives and error that its an invalid driver selected. I used excel 5/4/3, 97-2000, all options mentioned- but didnt work

-Tanya

[jhardman]

Tanya, (and this goes for Yogesh, too)

Show me what you have so far. Also, what is the data format of the excel file? How do you have the tables organized?

Jared

[chaitanya02]

Tanya, (and this goes for Yogesh, too)

Show me what you have so far. Also, what is the data format of the excel file? How do you have the tables organized?

Jared

Hi Jared, Thanks for your interest.
Here is what I have to do:
I have four tables, as posted in my recent question- sql1,sql2,sql3,sql4- which are all excel files. Data inside these tables are charecters, intergers etc...
like email id, account number, names etc...
Now i have copied these files to the sql server desktop and launched dts wizard
It asked me to choose files...in data source type- selected as excel 3.0
and clicked on next-- it went to destination type- selected our sql server...
it then says invalid input data source connection type....
So- I am not sure, if there is any other way of exporting all the excel files to sql ..server 2000. I saw some code in google for moving data from excel to sql, but where should it be run, on server side??
please suggest
Tanya

[jhardman]

Tanya,

Yes, There are several methods to copy them over. You can use asp, but a db manager should do a better job.

Anyway, don't connect to the excel file with dns, just specify a driver in your asp like this:

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.open "Provider = Microsoft.Jet.OLEDB.4.0;Data Source=c:\inetpub\myspreadsheet.xls; Extended Properties=Excel 8.0;"
set objRS = server.createObject("ADODB.recordset")
objRS.open objConn, "SELECT * FROM sql1"

Some people create new tables and new DBs using asp, but I would not recommend it. at very least, make empty db tables with the correct field names.

Now you should be able to open a connection and recordset for the SQL db at the same time, but for that one you will need a DNS entry. (this is a slightly different topic and steps in making one depend on your OS. If you need help with that, let me know)

set conn=Server.CreateObject("ADODB.Connection") 
conn.Open "mySQLtable"
set RS = server.createObject("ADODB.recordset")
RS.open conn, "SELECT * FROM sql1", adOpenDynamic, adLockOptimistic

Now you have both dbs open. objRS is the recordset from the excel file, and RS is from the SQL db. Notice I needed to open the sql db dynamically so I could update it. Copy from one to the other like this:

do until objRS.eof
   RS.addnew
   for each x in objRS
      RS(x) = objRS(x)
   next
   RS.update
   objRS.moveNext
loop

the do loop scrolls through each record in the excel file. It creates a new record in the SQL db, populates each field as it is in the excel file then updates, and moves to the next row of the excel table. This should work pretty straight-forward except there are a lot of things that can go wrong. SQL server does not accept the same formats as excel (I've always noticed boolean fields -True or false- give it trouble). anyway, try this. If it doesn't work let me kjnow and I will see if I can work thru the errors.

Jared

[yogesh m]

Tanya, (and this goes for Yogesh, too)

Show me what you have so far. Also, what is the data format of the excel file? How do you have the tables organized?

Jared

hi jared
i have a excel file in that i contain the attendance of employee (employeeid,name,logindate,time,logoutdate etc) and in SQL Server i have one table hrms_attends and same fields i can created hear a now i want to add excel contain in SQL table plz help me
Yogesh

[jhardman]

hi jared
i have a excel file in that i contain the attendance of employee (employeeid,name,logindate,time,logoutdate etc) and in SQL Server i have one table hrms_attends and same fields i can created hear a now i want to add excel contain in SQL table plz help me
Yogesh

so the fields in both tables are named the same? Then the example I outlined below for tanya should work. Try it and let me know.

Jared

[yogesh m]

hi sir
i can write the following code

set obj=Server.CreateObject ("Adodb.Connection")
obj.Open "Provider=Microsoft.Jet.OLEDB.4.0;Excel 8.0;Database=D:\March_2007_attendance.xls;"
set objrs=Server.CreateObject ("ADODB.Recordset")
Sql="select * from [March_2007_attendance$]"
bjrs.Open Sql,obj



set RS = server.createObject("ADODB.recordset")
RS.open cnn, "SELECT * FROM HRMS_Attends1", 1

do until objrs.EOF
'response.Write "in loop"
'response.End
rs.AddNew
for each x in objrs
rs(x)=objrs(x)
next
rs.Update
objrs.MoveNext
loop


=========
but it can show the error message like

Microsoft JET Database Engine error '80040e37'

The Microsoft Jet database engine could not find the object 'March_2007_attendance$'. Make sure the object exists and that you spell its name and the path name correctly.

And March_2007_attendance$ is my Excle file and Work sheet name

[lyne_asp]

Hello jhardman,

I have this code pattered from this forum

Set objConnEx = Server.CreateObject("ADODB.Connection")
objConnEx.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Netroot\WebApp\testprograms\malen\Shuttl e_Service\Attachment\"&FileName&;Extended Properties=Excel 8.0;HDR=Yes;IMEX=1;"
set objRS = server.createObject("ADODB.recordset")
objRS.open objConnEx, "SELECT * FROM tbl_Request"

"SELECT * FROM tbl_Request" - This is my SQL table, my question is
objConnEx is my connection to excel, why it is I have SQL Select statement here on excel connection.

PLease help..THanks

[jhardman]

"SELECT * FROM tbl_Request" - This is my SQL table, my question is
objConnEx is my connection to excel, why it is I have SQL Select statement here on excel connection.

PLease help..THanks

SQL stands for standard (or possibly structured) query language, it is just a standard way to connect to any db, and you are using excel like a db. Microsoft did not invent SQL, and using the name SQL does not necessarily mean you are connecting to Microsoft's SQL Server.

Jared

[pexp]

Dear Jared,

I was trying to use your code for insertion of excel data into a database but I am getting the following error

*************[Error]*************************
ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
************[/Error]*************************

Please help!
----------------------------------------------

Tanya,

Yes, There are several methods to copy them over. You can use asp, but a db manager should do a better job.

Anyway, don't connect to the excel file with dns, just specify a driver in your asp like this:

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.open "Provider = Microsoft.Jet.OLEDB.4.0;Data Source=c:\inetpub\myspreadsheet.xls; Extended Properties=Excel 8.0;"
set objRS = server.createObject("ADODB.recordset")
objRS.open objConn, "SELECT * FROM sql1"

Some people create new tables and new DBs using asp, but I would not recommend it. at very least, make empty db tables with the correct field names.

Now you should be able to open a connection and recordset for the SQL db at the same time, but for that one you will need a DNS entry. (this is a slightly different topic and steps in making one depend on your OS. If you need help with that, let me know)

set conn=Server.CreateObject("ADODB.Connection") 
conn.Open "mySQLtable"
set RS = server.createObject("ADODB.recordset")
RS.open conn, "SELECT * FROM sql1", adOpenDynamic, adLockOptimistic

Now you have both dbs open. objRS is the recordset from the excel file, and RS is from the SQL db. Notice I needed to open the sql db dynamically so I could update it. Copy from one to the other like this:

do until objRS.eof
   RS.addnew
   for each x in objRS
      RS(x) = objRS(x)
   next
   RS.update
   objRS.moveNext
loop

the do loop scrolls through each record in the excel file. It creates a new record in the SQL db, populates each field as it is in the excel file then updates, and moves to the next row of the excel table. This should work pretty straight-forward except there are a lot of things that can go wrong. SQL server does not accept the same formats as excel (I've always noticed boolean fields -True or false- give it trouble). anyway, try this. If it doesn't work let me kjnow and I will see if I can work thru the errors.

Jared

[jhardman]

Dear Jared,

I was trying to use your code for insertion of excel data into a database but I am getting the following error

*************[Error]*************************
ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
************[/Error]*************************

Please help!
----------------------------------------------

The error means one of the data types in the excel file is slightly different than the data type in the database, dates always give me this error if they are in the wrong format, that is why I always suggest using unambiguous date types like "2008-Oct-01".

Anyway, what you need to do first is find the exact location of the error, then you can figure out in what sense the data type is wrong. An easy way to pinpoint the location is to add comments for each line:

 for each x in objRS
      response.write "<!-- Adding " & x & " -->" & vbNewLine
       RS(x) = objRS(x)
   next

then the last comment lists the field that caused the error. Let me know if you have any other questions or if this helps.

Jared