Click here to Skip to main content
15,868,292 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:
VB
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!!
Posted
Updated 3-Jun-13 21:10pm
v3
Comments
Richard C Bishop 3-Jun-13 12:45pm    
You have an extra apostrophe between " and Data Source. Try removing that.
PabbVZ 4-Jun-13 2:48am    
I've tried but i get this error: Incorrect syntax near 'DATA'.
Maciej Los 3-Jun-13 13:46pm    
Why VBScript?
PabbVZ 4-Jun-13 2:52am    
Because I found that very simple for create Scripts, and I thought to find the solution with that but i get error...

This query should be proper:
SQL
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$];


More about:
OPENDATASOURCE[^]
INSERT Examples[^]

Alternative: OPENROWSET[^]
 
Share this answer
 
Comments
_Amy 4-Jun-13 4:18am    
Nice links. +5!
Maciej Los 4-Jun-13 4:29am    
Thank you, Amit ;)
PabbVZ 4-Jun-13 7:51am    
Thank you very much but i get another error But after try alway with VBScript i decided to chage to batch
Thanks Macielj!!!!
Maciej Los 4-Jun-13 7:57am    
You're welcome ;)
If your issue is solved, please, mark this answer as "solved" (green button) - formally.
;)
To solve the Problem i wrote it inside a Query for create the file in .sql to do exactly i want

here's the code (The name for it is SQLQuery1.sql)

SQL
USE DataBase;

/*Here I delete the table for re-create it again*/
DROP TABLE Texample

/*Here I create it and fill it with my .txt file*/
CREATE TABLE Texemple (
FirstName varchar(10),
LastName Varchar(15),
Email Varchar(20)
);
/*With this command I put in the DB my .txt*/
BULK INSERT Texemple FROM 'C:\TxtFile1.txt' WITH (FIELDTERMINATOR = '","')



After we gonna create the .bat Script

@echo off@echo off
cls

set /p sname= ServerName
set /p dbname= DataBase
set /p Username= User
set /p pwd= Pass

set /p choice=ARE YOU SURE TO EXECUTE SCRIPTS in %DbName% (y/n) ?

if '%choice%'=='y' goto begin
goto end

:begin
if exist _Deploy.txt del _Deploy.txt

@echo on

sqlcmd -S <servername> -U <user> -P <pass> -d <database> -I -i C:\SQLQuery1.sql >> _Deploy.txt 2>&1



@notepad _Deploy.txt

</database></pass></user></servername>



Finally the Batch Script runs the .sql script to import our data file (.txt) in the Database
 
Share this answer
 
Comments
Maciej Los 4-Jun-13 8:15am    
Good work!
+5!
"SELECT * INTO XLImport6 FROM " & _
" OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0;" & _
" Data Source=C:\test\xltest.xls;" & _
" Extended Properties=Excel 8.0')...[Customers$]"

Try this and see.
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900