Using "Select-Into" text file from XL (Attn: Jamie Collins, others

[quartz]

Does anyone know how to execute a "Select ...Into" SQL query from Excel to
create a text file?

This query would run from an Excel XP module, query an Oracle database and
create a text file.

If you know how to do this, could you please post example code.

Jamie Collins indicated in an earlier post it could be done, but his example
seemed to be for Access. HELP.

 

[keepITcool]

Following will work..
(most of the strings etc come from Jamie.

The formatting of the delimiters etc for the exported text file can be
achieved by placing a schema.ini file in the export directory.

See JetSql40.chm: Initializing the Text and HTML Data Source Driver
(if you get errors re field and decimal separators try with USenglish
settings first..)


Sub TxtToXls()

Dim txtPath
Dim txtFile
Dim xlsFile

txtPath = "d:\ado test"
txtFile = "myImport.txt"
xlsFile = "d:\ado test\MyExport.xls"

With CreateObject("Scripting.FileSystemObject")
If .FileExists(xlsFile) Then .DeleteFile (xlsFile)
End With


With CreateObject("adodb.connection")
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";" & _
"Data Source=" & xlsFile & ";"

.CursorLocation = 3 'adUseClient
.Open
.Execute " SELECT * " & _
" INTO Import " & _
" FROM [Text;HDR=Yes;Database=" & txtPath & ";].[" & _
txtFile & "];"
.Close

End With

End Sub

Sub XlsToTxt()

Dim txtPath
Dim txtFile
Dim xlsFile
Dim xlsRange
txtPath = "d:\ado test"
txtFile = "myExport.txt"
xlsFile = "d:\MySource.xls"
xlsRange = "Import" 'Sheet1 or Sheet1$a1:d400

With CreateObject("Scripting.FileSystemObject")
If .FileExists(txtPath & "\" & txtFile) Then .DeleteFile (txtPath &
"\" & txtFile)
End With


With CreateObject("adodb.connection")
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties=""Text;HDR=Yes;"";" & _
"Data Source=" & txtPath & ";"



.CursorLocation = 3 'adUseClient
.Open
.Execute " SELECT * " & _
" INTO " & Replace(txtFile, ".", "#") & _
" FROM [" & xlsRange & "] IN '' [Excel 8.0;HDR=Yes;Database="
& xlsFile & "]"
.Close

End With

End Sub


Sub XlsToTxt2()
'More ways lead to rome...
Dim txtPath
Dim txtFile
Dim xlsFile
Dim xlsRange
txtPath = "d:\ado test"
txtFile = "myExport.txt"
xlsFile = "d:\ado test\MySource.xls"
xlsRange = "Import" 'Sheet1 or Sheet1$a1:d400

With CreateObject("Scripting.FileSystemObject")
If .FileExists(txtPath & "\" & txtFile) Then .DeleteFile (txtPath &
"\" & txtFile)
End With


With CreateObject("adodb.connection")
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";" & _
"Data Source=" & xlsFile & ";"

.CursorLocation = 3 'adUseClient
.Open
.Execute " SELECT * " & _
" INTO " & Replace(txtFile, ".", "#") & _
" IN '' [Text;HDR=Yes;Database=" & txtPath & ";]" & _
" FROM [" & xlsRange & "]"
.Close

End With

End Sub




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[quartz]

Thanks, I will give this a try...

Following will work..
(most of the strings etc come from Jamie.

The formatting of the delimiters etc for the exported text file can be
achieved by placing a schema.ini file in the export directory.

See JetSql40.chm: Initializing the Text and HTML Data Source Driver
(if you get errors re field and decimal separators try with USenglish
settings first..)


Sub TxtToXls()

Dim txtPath
Dim txtFile
Dim xlsFile

txtPath = "d:\ado test"
txtFile = "myImport.txt"
xlsFile = "d:\ado test\MyExport.xls"

With CreateObject("Scripting.FileSystemObject")
If .FileExists(xlsFile) Then .DeleteFile (xlsFile)
End With


With CreateObject("adodb.connection")
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";" & _
"Data Source=" & xlsFile & ";"

.CursorLocation = 3 'adUseClient
.Open
.Execute " SELECT * " & _
" INTO Import " & _
" FROM [Text;HDR=Yes;Database=" & txtPath & ";].[" & _
txtFile & "];"
.Close

End With

End Sub

Sub XlsToTxt()

Dim txtPath
Dim txtFile
Dim xlsFile
Dim xlsRange
txtPath = "d:\ado test"
txtFile = "myExport.txt"
xlsFile = "d:\MySource.xls"
xlsRange = "Import" 'Sheet1 or Sheet1$a1:d400

With CreateObject("Scripting.FileSystemObject")
If .FileExists(txtPath & "\" & txtFile) Then .DeleteFile (txtPath &
"\" & txtFile)
End With


With CreateObject("adodb.connection")
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties=""Text;HDR=Yes;"";" & _
"Data Source=" & txtPath & ";"



.CursorLocation = 3 'adUseClient
.Open
.Execute " SELECT * " & _
" INTO " & Replace(txtFile, ".", "#") & _
" FROM [" & xlsRange & "] IN '' [Excel 8.0;HDR=Yes;Database="
& xlsFile & "]"
.Close

End With

End Sub


Sub XlsToTxt2()
'More ways lead to rome...
Dim txtPath
Dim txtFile
Dim xlsFile
Dim xlsRange
txtPath = "d:\ado test"
txtFile = "myExport.txt"
xlsFile = "d:\ado test\MySource.xls"
xlsRange = "Import" 'Sheet1 or Sheet1$a1:d400

With CreateObject("Scripting.FileSystemObject")
If .FileExists(txtPath & "\" & txtFile) Then .DeleteFile (txtPath &
"\" & txtFile)
End With


With CreateObject("adodb.connection")
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";" & _
"Data Source=" & xlsFile & ";"

.CursorLocation = 3 'adUseClient
.Open
.Execute " SELECT * " & _
" INTO " & Replace(txtFile, ".", "#") & _
" IN '' [Text;HDR=Yes;Database=" & txtPath & ";]" & _
" FROM [" & xlsRange & "]"
.Close

End With

End Sub




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

Does anyone know how to execute a "Select ...Into" SQL query from
Excel to create a text file?

This query would run from an Excel XP module, query an Oracle database
and create a text file.

If you know how to do this, could you please post example code.

Jamie Collins indicated in an earlier post it could be done, but his
example seemed to be for Access. HELP.

 

[Jamie Collins]

Thanks, I will give this a try...

Quartz,
The examples posted by keepITcool use Excel rather than Oracle as the
data source. A Jet driver/provider must be used for this syntax to
work. If your source database is non-Jet, you will need to be able to
query it using odbc and thereby use Jet's 'pass through' query
functionality.

I don't have Oracle, so here's an example that uses an odbc connection
to a SQL Server database:

SELECT
fname, minit, lname
INTO
[Text;Database=C:\My Folder\;].MyFile#txt
FROM
[ODBC;Driver={SQL
Server};SERVER=MYSERVER;DATABASE=pubs;UID=***;Pwd=***;].employee
;

keepITcool,
I see you used the fso to delete the existing text file. Because in
the Jet context a file is a table, you may use the DROP TABLE syntax
e.g.

DROP TABLE
[Text;Database=C:\My Folder\;].[MyFile#txt]
;

Jamie.

--