Querying a CSV file from Excel

E

ExcelMonkey

I have a CSV file with 4 columns 100,000 rows (100,000 records). I wan
to import a portion of the data into an excel spreadsheet. And I wan
to do this on a regular basis. Currently the data is going to stay i
the CSV file (i.e. it will not be in Access unless it has to be).

Effectively I want to query the data based on the date field in one o
the columns. Is it possible to query data in a CSV file (i.e. tex
file) from VBA into Excel? I want to be able to enter a date in my VB
code and use this date to query the data to retrieve all the record
from that date.

Please and thank-yo
 
O

Ocmulgee

MSQuery is designed to do just that. You won't find a lot of documentation
to help you, but MSQuery will run against various databases, ODBC sets,
text/csv files, even against Excel worksheets. I use it mostly for doing
joins between worksheets.
You may have to create a 'New Data Source' with a unique name to handle the
specifics of your csv file. Once created, the New Data Source becomes a
".dsn" file which holds the instructions for the connection. The query
instructions (SQL) are built in an Access style interface. While it may
sound intimidating, the process becomes easier as you flail around in it (at
least it did for me). Once the query is returned to an Excel worksheet, it
can be 'refreshed' with new data (same text file name) very easily, and much
faster once the connection is made. I also believe that there is an object
model, so you should be able to run MSQuery programmatically. Hope this
helps.
 
O

onedaywhen

ExcelMonkey wrote ...
Is it possible to query data in a CSV file (i.e. text
file) from VBA into Excel?
Click to expand...

Yes. If you prefer VBA code you can use ADO with the MS OLEDB provider
for Jet, specifying Text in the extended properties e.g. this
connection string

Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\Tempo\;
Extended Properties='Text;HDR=Yes'

applies to all comma-delimited text files (other delimiters or fixed
width text requires a scheme.ini file) in the folder specified. To
query use the filename as a table name e.g.

SELECT ID, lanme, fname FROM MyFile#txt

Because the folder is the 'database' you can create joins between
files e.g.

SELECT T1.ID, T1.lanme, T1.fname
FROM MyFile#txt T1
INNER JOIN MyFile#txt T2
ON T1.ID = T2.ID

You can use the Jet provider to create text files:

CREATE TABLE MyNewFile#txt
(
ID INTEGER,
lname VARCHAR(35),
fname VARCHAR(35)
)
;
INSERT INTO MyNewFile#txt
(ID, lname, fname)
VALUES (1, 'day', 'when')
;

One thing that won't work while *connected* to a 'Text' Jet connection
(as above), but will work if connected to, say, an 'Excel 8.0' Jet
connection, is the following syntax e.g. to create a new text file
based on data from an Excel workbook:

SELECT ID, lanme, fname
INTO [Text;C:\Tempo\;].ANewFile#txt
FROM [Excel 8.0;database=C:\Tempo\db.xls].[PersonalDetails$]

Should you prefer GUI tools you can use MS Query with an ODBC text
driver. The approach is largely the same. Here is a detailed
description for tab delimited (i.e. schema.ini file required):

http://groups.google.com/[email protected]

--
 
Top