Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using "Select-Into" text file from XL (Attn: Jamie Collins, others
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using "Select-Into" text file from XL (Attn: Jamie Collins, others
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 ?B?cXVhcnR6?= wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using "Select-Into" text file from XL (Attn: Jamie Collins, ot
Thanks, I will give this a try...
"keepITcool" wrote: 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 ?B?cXVhcnR6?= wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using "Select-Into" text file from XL (Attn: Jamie Collins, ot
quartz wrote ...
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. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
text string: "91E10" in csv file auto converts to: "9.10E+11" | Excel Discussion (Misc queries) | |||
"Control" plus "click" doesn't allow me to select multiple cells | New Users to Excel | |||
.csv drops "0" when saved, even if file was set as "text". | Excel Discussion (Misc queries) | |||
METHOD "SELECT" OF OBJECT '_WORKSHEET' FAILED when opening a file | Excel Programming | |||
Using "Cells" to write "Range("A:A,H:H").Select" | Excel Programming |