Export to txt files?

[Guest]

I have a table and would like to export one of the fields in each record to a
seperate .txt file. The field is a memo field and the table has 18k records
so it will be a lot of small files.

Selecting the field with a query and/or creating a record or form is simple
enough, but I haven't been able to figure out how to make individual files.

I don't care what the file name is. Does anyone have a suggestion as to the
easiest way to do this? Thank you.

Andrew

 

[John Nurick]

Hi Andrew,

If there's a field in the record that you can use to provide a unique
filename, just download the WriteToFile() function from
http://www.j.nurick.dial.pipex.com/Code/index.htm , and use it in a
query. E.g.:

SELECT *
FROM (
SELECT
[ID],
WriteToFile([MemoField],
'D:\Folder\\Export ' & [ID] & '.txt'
) AS ErrNumber
FROM MyTable
)
WHERE ErrNumber > 0
ORDER BY ID
;

The subquery does the actual work, calling WriteToFile() once for each
record to create the files. WriteToFile() returns zero if successful, so
the outer query simply lists any records for which files could not be
created.

If you can't base the filenames on something in the table, I feel it's
simpler to use VBA code to iterate through a recordset and generate
unique filenames than to generate the filenames in SQL. But you may
still find WriteToFile() convenient.

 

[Guest]

Thank you VERY MUCH!!!! I've been putting off a conversion project because I
thought it was going to be a major chore. You hint & code saved me a lot of
time.

Thank you,
Andrew

Hi Andrew,

If there's a field in the record that you can use to provide a unique
filename, just download the WriteToFile() function from
http://www.j.nurick.dial.pipex.com/Code/index.htm , and use it in a
query. E.g.:

SELECT *
FROM (
SELECT
[ID],
WriteToFile([MemoField],
'D:\Folder\\Export ' & [ID] & '.txt'
) AS ErrNumber
FROM MyTable
)
WHERE ErrNumber > 0
ORDER BY ID
;

The subquery does the actual work, calling WriteToFile() once for each
record to create the files. WriteToFile() returns zero if successful, so
the outer query simply lists any records for which files could not be
created.

If you can't base the filenames on something in the table, I feel it's
simpler to use VBA code to iterate through a recordset and generate
unique filenames than to generate the filenames in SQL. But you may
still find WriteToFile() convenient.

I have a table and would like to export one of the fields in each record to a
seperate .txt file. The field is a memo field and the table has 18k records
so it will be a lot of small files.

Selecting the field with a query and/or creating a record or form is simple
enough, but I haven't been able to figure out how to make individual files.

I don't care what the file name is. Does anyone have a suggestion as to the
easiest way to do this? Thank you.

Andrew