SQL syntax

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Spike - 08 Mar 2007 05:06 GMT

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.

Signature

with kind regards

Spike

Reply to this Message

Martin Fishlock - 08 Mar 2007 07:04 GMT

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

Signature

Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

> 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
[quoted text clipped - 3 lines]
> 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.

Reply to this Message

Bob Phillips - 08 Mar 2007 08:27 GMT

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

Signature

---
HTH

Bob

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

>I will be very grateful for the SQL syntax to use for an ADO query on a
>large
[quoted text clipped - 4 lines]
> 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.

Reply to this Message