• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3609
  • Last Modified:

vbscript excel to sql

I need to open an Excel spreadsheet and export it to an SQL database.  I copies some code I found here but am getting errors.  I culled all the table stuff and am trying to just display the data I find on the spreadsheet.  I'm currently getting "object required: 'server'"  on line 1. I understand that I need to set the value, but to what?
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "DBQ=" & Server.MapPath("tsitimesheet20090212.xls") & ";DRIVER={Microsoft Excel Driver (*.xls)};"
 
'Set rs = Server.CreateObject("ADODB.Recordset")
 
 
' cell range as defined in excel
sql="select * from [Range1];"
 
 
 
rs.Open sql, cn
 
For counter = 0 To rs.fields.count - 1
 
' Write out the field names
response.write rs.fields.item(counter).name 
 
' Move to the next field
next
 
' Move to the first record
rs.movefirst
 
' Write out the record set
do while not rs.eof 
 
' Loop through all of the fileds
for counter = 0 to rs.fields.count - 1
 
 
' Write out the field values
response.write rs.fields.item(counter).value
 
' Move to the next field
next
 
 
' Move to the next record
rs.movenext
loop

Open in new window

0
larksys
Asked:
larksys
  • 3
  • 2
1 Solution
 
reb73Commented:
Server object is usually used for ASP coding where you use the IIS's native server object, for plain vbscript use just a straightforward createobject  like -

Set cn = CreateObject("ADODB.Connection")

Have you considered using the OPENDATASOURCE transact-sql function to directly import MS-Excel data to an SQL table?
0
 
larksysAuthor Commented:
I need to be able to schedule a task to run daily. The name of the Excel file has to be changed on the fly. I can import the data manually, no problem.

0
 
larksysAuthor Commented:
Your comment lead me to do a little research on it.  Now I'm just slightly smarter than I was.  I've changed the script to the following that appears to work except it doesn't populate the columns with data;

Option Explicit

'--------------------------------------------------------

'

Const adOpenStatic = 3
Const adLockOptimistic = 3
dim strSqlInsertString,objConnection2,objRecordSet2
dim objExcel,objWorkBook,intRow

Set objConnection2 = CreateObject("ADODB.Connection")
Set objRecordSet2 = CreateObject("ADODB.Recordset")

objConnection2.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=humpty;Password=dumpty;Initial Catalog=xxdev;Data Source=xxlive"

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open _
    ("D:\xx_Exports\JobPulseExport2-17-09CC.xls")

intRow = 2

dim CN,Company,City,State,Source,Title,Found,Compensation


strSqlInsertString = "INSERT INTO xx_Imports (cN,Company,City,State,Source," & _
"Title,Found,Compensation) " & _
"VALUES('" & CN & "','" & Company & "','" & City & "','" & State& "','" & Source& "'," & _
"'" & Title& "','" & Found& "','" & Compensation & "')"

Do Until objExcel.Cells(intRow,1).Value = ""
   
    CN = objExcel.Cells(intRow, 1).Value
    Company = objExcel.Cells(intRow, 2).Value
    City = objExcel.Cells(intRow, 3).Value
    State = objExcel.Cells(intRow, 4).Value
    Source = objExcel.Cells(intRow, 5).Value
    Title = objExcel.Cells(intRow, 6).Value
    Found = objExcel.Cells(intRow, 7).Value
    Compensation = objExcel.Cells(intRow, 8).Value
    intRow = intRow + 1


set objRecordSet2=objConnection2.execute(strSQLInsertString)

loop

objconnection2.close

set objConnection2 = Nothing


objExcel.Quit



0
 
reb73Commented:
Debug the contents of the string strSqlInsertString  using a msgbox..

You should ideally refer to the sheet explicitly - objWorkbook.Sheets(1).cells(intRow, 1).value

Executing an Insert statement will not return a recordset, so change line as follows -

objConnection2.execute strSQLInsertString
0
 
larksysAuthor Commented:
I also had to move the sqlstring build inside the loop.  Thanks very much.
0

Featured Post

Industry Leaders: 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!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now