Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querying a CSV file from Excel
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querying a CSV file from Excel
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. "ExcelMonkey " wrote in message ... I have a CSV file with 4 columns 100,000 rows (100,000 records). I want to import a portion of the data into an excel spreadsheet. And I want to do this on a regular basis. Currently the data is going to stay in 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 of the columns. Is it possible to query data in a CSV file (i.e. text file) from VBA into Excel? I want to be able to enter a date in my VBA code and use this date to query the data to retrieve all the records from that date. Please and thank-you --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querying a CSV file from Excel
ExcelMonkey wrote ...
Is it possible to query data in a CSV file (i.e. text file) from VBA into Excel? 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/groups?selm...g. google.com -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Querying SQL Server from Excel | Excel Discussion (Misc queries) | |||
Querying 2 Excel files | Excel Worksheet Functions | |||
Querying a range within Excel | Excel Worksheet Functions | |||
Importing/querying data from .mdb file into Excel | Excel Programming | |||
Web Querying from excel. | Excel Programming |