SQL syntax

S

Spike

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.
 
M

Martin Fishlock

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
 
B

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top