通过VBScript将数据导入SQL Server [英] Import data into SQL server via VBScript

问题描述

嗨！



我正在一个项目中将.txt中的表导入到数据库中，在这种情况下SQL Server 2008 r2

我想用这个代码我可以登录到数据库，但我总是遇到这个问题。

脚本：C：\users\PC\test.vbs 
 Ligne：17 
 Carect：3 
错误：字符串后的未结算引号
 OPENDATASOURCE （'Microsoft.Jet.OLEDB.4.0'，'数据源= C：\\\\ xltest.xls;扩展
属性= Excel 8.0'）... [Customers $]'。
源：用于SQL Server的Microsoft OLE DB提供程序

这是代码：

  Dim  oConn，sConn 
  Dim  strSQL 
  Dim  lngRecsAff 
 
 sConn =   Provider = SQLOLEDB ;数据源=< Server> ;;初始目录=< DataBaseName> ;;用户ID =<用户> ;;密码=<传递> ;; 
 设置 oConn = CreateObject（  ADODB.connection）
 oConn.Open sConn 
 
 ' 使用OPENDATASOURCE导入。 
 strSQL =   SELECT * INTO XLImport6 FROM& _ 
   OPENDATASOURCE（'Microsoft.Jet.OLEDB.4.0'，& _ 
  '数据源= C：\\\ xltest.xls;& _ 
  扩展属性= Excel 8.0'）... [Customers $] 
 oConn.Execute strSQL 
 Debug.Print  受影响的记录： & lngRecsAff 
 
 sConn.Close 
  Set  oConn =  Nothing  

我真的不知道问题在哪里，即使知道是引号。

请帮助!!

解决方案

'。
来源：用于SQL Server的Microsoft OLE DB提供程序

这是代码：

< pre lang =vb> Dim oConn，sConn
Dim strSQL
< span class =code-keyword> Dim lngRecsAff

sConn = Provider = SQLOLEDB; Data Source =< Server> ;; Initial Catalog =< DataBaseName> ;; User ID =< User> ;; Password =< Pass> ;;
设置 oConn = CreateObject（ ADODB.connection）
oConn.Open sConn

' 使用OPENDATASOURCE导入。
strSQL = SELECT * INTO XLImport6 FROM& _
OPENDATASOURCE（'Microsoft.Jet.OLEDB.4.0'，& _
'数据源= C：\ test \ xltest.xls;& ; _
扩展属性= Excel 8.0'）... [客户

oConn.Execute strSQL
Debug.Print 记录受影响：& lngRecsAff

sConn.Close
设置 oConn = 没什么

我真的不知道问题在哪里，即使知道是引号。

请帮忙!!

此查询应该是正确的：

  INSERT   INTO  XLImport6（DestField1，DestField2，DestField3）
  SELECT  *  FROM   OPENDATASOURCE （'  Microsoft.Jet.OLEDB.4.0'，
 ' 数据源= C：\ test \ xltest.xls;扩展属性= Excel 8.0'）... [客户

Hi!

I'm working in a project to import a table in .txt to a database in this case SQL server 2008 r2
I think with this code i can Login to the DB but i get always this problem.

Script:    C:\users\PC\test.vbs 
Ligne:     17 
Carect:    3 
Error:     Unclosed quotation mark after the character string
           OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=C:\test\xltest.xls;"Extended
           Properties=Excel 8.0')...[Customers$]'.
Source:  Microsoft OLE DB Provider for SQL Server 

This is the code:

Dim oConn, sConn 
Dim strSQL 
Dim lngRecsAff 

sConn = "Provider = SQLOLEDB; Data Source = <Server>; Initial Catalog = <DataBaseName>; User ID = <User>;Password = <Pass>;" 
Set oConn = CreateObject("ADODB.connection") 
oConn.Open sConn 
  
 'Import by using OPENDATASOURCE. 
    strSQL = "SELECT * INTO XLImport6 FROM " & _  
      "OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _  
      "'Data Source=C:\test\xltest.xls;" & _  
      "Extended Properties=Excel 8.0')...[Customers$]"     
oConn.Execute strSQL  
Debug.Print "Records affected: " & lngRecsAff 

  sConn.Close 
Set oConn = Nothing 

I really don't know where's the problem, even knowing is a quotation mark.
Please some help!!

解决方案

'. Source: Microsoft OLE DB Provider for SQL Server

This is the code:

Dim oConn, sConn 
Dim strSQL 
Dim lngRecsAff 

sConn = "Provider = SQLOLEDB; Data Source = <Server>; Initial Catalog = <DataBaseName>; User ID = <User>;Password = <Pass>;" 
Set oConn = CreateObject("ADODB.connection") 
oConn.Open sConn 
  
 'Import by using OPENDATASOURCE. 
    strSQL = "SELECT * INTO XLImport6 FROM " & _  
      "OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _  
      "'Data Source=C:\test\xltest.xls;" & _  
      "Extended Properties=Excel 8.0')...[Customers

" oConn.Execute strSQL Debug.Print "Records affected: " & lngRecsAff sConn.Close Set oConn = Nothing

I really don't know where's the problem, even knowing is a quotation mark.
Please some help!!

This query should be proper:

INSERT INTO XLImport6 (DestField1, DestField2, DestField3)
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers

这篇关于通过VBScript将数据导入SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！