Can OpenDataSource use *SQLStatement when the DataSource is a CSV?

[Gerhard Weiss]

I am trying to figure out how to use a *SQLStatement on a CSV file.
I want to do something like this:
SELECT * FROM T:\PRT\ELGEXC14.CSV WHERE EGEXCODE="14"
What would my OpenDataSource look like?

 

[Peter Jamieson]

The simple answer to your question is that it would look something like

ActiveDocument.MailMerge.OpenDataSource _
Name:="T:\PRT\ELGEXC14.CSV", _
Connection:="", _
SQLStatement:="SELECT * FROM T:\PRT\ELGEXC14.CSV WHERE EGEXCODE='14'"

as long as Word recognised EGEXCODE as a text column. And you probably don't
really need the file name, e.g. you could probably get away with

ActiveDocument.MailMerge.OpenDataSource _
Name:="T:\PRT\ELGEXC14.CSV", _
Connection:="", _
SQLStatement:="SELECT * FROM X WHERE EGEXCODE='14'"


Going a bit beyond that, there are three main considerations here:
a. which version of Word?
b. do you want to do anything in the SQL statement other than specify the
field list, a fairly simple WHERE clause, and a fairly simple ORDER BY
clause?
c. do you need to use a particular connection method?

Word can open a .csv file using either
e. its internal text converter
f. ODBC
g. OLEDB (Word 2002 and later only)

When Word is using (e), the SQL you can issue is limited. With (f) and (g),
Word uses the Jet driver/provider to read the text file and can, broadly
speaking, use any of the features of Jet's dialect of SQL, including string
functions, joins, GROUP BY and HAVING clauses and so on. You may not be able
to force Word to use (e) even if you want to. And you may find it difficult
to get Word to recognise the correct delimiters with either (f) or (g) - in
the case of (f), you can specify the field delimiter in a SCHEMA.INI file.

 

[Gerhard Weiss]

The simple answer to your question is that it would look something like

ActiveDocument.MailMerge.OpenDataSource _
Name:="T:\PRT\ELGEXC14.CSV", _
Connection:="", _
SQLStatement:="SELECT * FROM T:\PRT\ELGEXC14.CSV
WHERE EGEXCODE='14'"

That did the trick. Thanks very much Peter Jamieson.

To answer some of your other questions.

Going a bit beyond that, there are three main considerations here:
a. which version of Word? Word 2000
b. do you want to do anything in the SQL statement other than specify the
field list, a fairly simple WHERE clause, and a fairly simple ORDER BY
clause?

I might use ORDER BY but that would be it.

Thanks
Gerhard