Exporting Query Records to Indivedual .CSV files 1

[dw1]

I am looking for a way to export Query Records to Indivedual .CSV files? Any ideas?

 

[RickSpr]

Open a recordset over the query. Loop through the records, opening an output file for each one and Printing the fields from the record into the file, with separating commas.

Help topics you might need:
Database Object
OpenRecordset method
Close method
Recordset Object
EOF property
MoveNext method
FreeFile function
Open statement
Print statement
Close statement


Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[dw1]

Sounds simple now who can I find to do it?

 

[sfm6s524]

If you're looking for a manual method, open query, chose from menu: File, Export. Choose Text Files as type, and give destination filename. Click Save, and Export Wizard begins (Access 2000). If you're looking for a more automated method to use in a macro, or VBA code (preferred), then look at the TransferText method. VBA basic command is:

docmd.TransferText acExportDelim,,"qryName","DestinationFileName.csv",true

Ken

 

[dw1]

Thanks for the VB Ken,

One item needs to be clarified in my request. I need to eport one record from the query to one file from the query

 

[sfm6s524]

You mean for instance, your query returns 100 records...You want to write a separate CSV file for each of the 100 records? What do you mean by "one file from the query?" Are you saying the CSV filename is part of the records returned? Just curious.

Ken

 

[dw1]

Correct 100 records in the seleted queary returns 100 .csv file for each record

 

[sfm6s524]

Well, it looks like your going to have to take Rick's advice above and do some coding. Using DAO, maybe code like:

Set DB = CurrentDB()
Set RS = DB.OpenRecordset("qryName&quot
Do until RS.EOF
Filename = RS![ExportFileName]
OutF = FreeFile
Open Filename For Output As OutF
Print #OutF, RS![Field1] & "," & RS[Field2] & ",' & Etc.
Close #OutF
RS.MoveNext
Loop
RS.Close
DB.Close

 

[dw1]

Thanks, Thats a start I will play with this over the weekend. How do I make the file name from one of the fields?

Filename = RS![[Field1].csv]

 

[sfm6s524]

Filename = RS![Field1] & ".csv"

Assuming Field1 is a valid filename without the ".csv" extension.