SQL syntax

[Guest]

I will be very grateful for the SQL syntax to use for an ADO query on a large
csv to pull in all the rows of data for one specific fund to an Excel
spreadsheet. There are about 35 funds listed in the first column headed
"Funds".

I can get all the data across but i would like to just import the data for
one fund say fund "XYZ" in the "Funds" field.

 

[Guest]

I ran the macro recorder on 2007 and got the following sql for a three column
table in a csv file amend as required.:


"SELECT test1.FUNDS, test1.NO, test1.YN" & Chr(13) & "" & Chr(10) &
"FROM test1.csv test1" & Chr(13) & "" & Chr(10) & "WHERE (test1.FUNDS='XYZ')"
_
)


Sub Macro2()
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _

"ODBC;DBQ=C:\USERS\ADMIN\DESKTOP;DefaultDir=C:\USERS\ADMIN\DESKTOP;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;FIL=text" _
), Array( _

";MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;" _
)), Destination:=Range("$A$1")).QueryTable
.CommandText = Array( _
"SELECT test1.FUNDS, test1.NO, test1.YN" & Chr(13) & "" & Chr(10) &
"FROM test1.csv test1" & Chr(13) & "" & Chr(10) & "WHERE (test1.FUNDS='XYZ')"
_
)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query_from_textfile"
.Refresh BackgroundQuery:=False
End With
End Sub

 

[Bob Phillips]

Use a where clause of column_name = "XYZ", or maybe filter the recordset
when retrieved.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)