Export to Text File based on Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using Access 2002, and have a db with one Table containing the following fields: ID, Ticker, Date, Prices. The table currently has 2.5 MM records. I need to generate individual text files for all tickers & prices on a daily basis, and have each text file be saved as the Date (ie file 040198.txt will contain all stocks and prices for April 01, 1998). Can someone please help identify and direct the best way to accomplish this

Thanks in advance for your help!

Trevor
 
Hi Trevor,

I'm not sure I understand what you mean by "on a daily basis". Do you
want to export
(a) one file every day, containing that day's (or the previous day's)
data?
(b) individual files each containing the data for a day you specify?
(c) one file for every day for which there are records in the table?

For any of these, you'll need to
1) create a query that returns a day's data formatted and sorted the way
you want it. For (a) this would contain a criterion on the Date field of
something like Date()for "today" or Date()-1 for "yesterday".

By the way, this is one main reason it's a bad idea to use words like
"Date" and "Name" as the names of fields: It's confusing, because
they're also names of common functions or properties in Access.

For (b) I'd use a parameter query that asks for the date.

For (c) I'd start with a parameter query, but would later just use its
SQL statement in VBA code.

2) Export the query manually, clicking the Advanced... button in the
text export wizard and saving an export specification.

3)(a) Set up a command button on a form that has code like this in its
Click event procedure:

Dim strFileSpec As String

strFileSpec = "D:\Folder\Subfolder\" _
& Format(Date(), "mmddyy") & ".txt"
DoCmd.TransferText specifying the kind of export, query name,
specificiation name and strFileSpec

3(b) As for (a): the query will ask for the date.

3(c) gets a bit more complicated. How much do you know about SQL and
VBA?
 
John,

Thanks for your response, and my apologies for not being more specific. I am in fact referring to your scenario (c) - exporting one file for every day for which there are records. Essentially, i'm starting 6 years ago, and need to generate a file for every day between 1/1/98 - 12/1/03.

1. I've already created and formatted a parameter query that has 2 parameters: 1. start date and 2. end date.
2. I built a Form with 2 Date Fields (start date and end date) that generates the desired output and format.
~ a. i have another command button that executes a TransferText macro, but from here there are two problems:

1. How can I get the macro or code to actually step through or loop through the date range creating a different file for each date?
2. How can i get the macro or code to name the file with the date name (ie "010198.txt" for January 1, 1998)?

Thanks so much for your help. These queries aren't terribly fast, and I'm not sure if I have the patience to run 1800 individual prices files...

Trevor
 
Hi Trevor,

This should get you started:

Sub TestSSS()
Dim j As Date
Dim strFileSpec As String
Dim strSQLBase As String
Dim strSQL As String
Dim qdQ As DAO.QueryDef

'get SQL from query and delete terminal ";"
Set qdQ = CurrentDb.QueryDefs("QT")
strSQLBase = Left(qdQ.SQL, Len(qdQ.SQL) - 3)
For j = #3/22/2002# To #3/25/2002# 'start date to end date
'Modify SQL
qdQ.SQL = strSQLBase & " WHERE TheDate = #" _
& Format(j, "mm/dd/yyyy") & "#;"
'create filespec
strFileSpec = "C:\temp\xx\" & Format(j, "mmddyy") & ".txt"
'export it
DoCmd.TransferText acExportDelim, , "QT", strFileSpec, True
Next
'tidy up
qdQ.SQL = strSQLBase
Set qdQ = Nothing
End Sub
 
John,

Thanks so much for the code, I was able to get it to work! The process is still pretty slow, but at least it's something I can run when I'm doing something else. If you have any suggestions to speed up the process (about 2 min / file) that would be much appreciated, but a major accomplishment in my book just to get it to work

Thanks again,
Trevor
 
Trevor,

Make sure there are indexes on the relevant fields of your table(s).

Consider converting your query into a make-table query; use this to
create a temporary table containing just the data you want to export
(all six years worth) indexed on the date field. Then run the export
code against this.

(Or, if you enjoy working with tools such as Perl, Python or awk, export
the whole six years worth to a text file sorted in date order, and then
write a script to split it into multiple files, one per date.)
 
Back
Top