Create unique CSV files based on filtered data

Question

Help!

Here are the main points I need to communicate my problem...

1. Table in Access 2007 contains >1M records
2. Four fields in table; where Location and InventoryID are the most important
3. Want to separate the table into individual files filtered for each unique Location number;

        The exported data contains just the InventoryIDs associated with that Location.

4. The exported file should be a CSV file
5. The exported file name should correspond to the Location number being filtered for that file.  (Eg. 1234.csv, 1235.csv, 1236.csv, etc.)

Thank you in advance.

av

Answer 1

Ok... some untested air code here, you'll need to test and debug it I'm sure. A couple of things aren't clear: you want a comma-separated-values file, but it apparently only has one field (InventoryID); and I don't know the datatype or contents of the Location field so I'm making some guesses here.

First create an Export Specification for the data you want to export; save it as specInventory say.

Dim db As DAO.Database ' point to the current database

Dim rsLoc As DAO.Recordset ' define a Recordset object for Locations

Dim strSQL As String

Dim qd As DAO.Querydef

Set db = CurrentDb() ' instantiate the database object

Set rs = db.OpenRecordset "SELECT DISTINCT Location FROM yourtable;"

Do Until rs.EOF ' loop through all the locations

  strSQL = "SELECT InventoryID, <other fields> FROM yourtable WHERE Location = '" & rs!Location & "'"

  ' this assumes that Location is a text field which will never contain an apostrophe

  Set qd = db.CreateQuerydef("TempExport", strSQL) ' create a Query object to export

  DoCmd.TransferText acExportDelim,"specInventory", "TempExport", "C:\SomePath" & rs!Location & ".csv"

  rs.MoveNext

Loop

Take a look at the VBA help for TransferText for more options.

Answer 2

I replied to John below for some VBA coding to do this.  If you have some code I can use, I will certainly give it a try.  Thank you!

Answer 3

I need help with the VBA code to do just that.  Instead of one at a time, I would like the routine to send the InventoryIDs in the CSV file with the Location as the csv's file name.  I appreciate your assistance.

Answer 4

You'll need a VBA routine for this. I'd start with a recordset of all the locations - if you have a Locations table, great, otherwise a SELECT DISTINCT Location query on your table. You'll also need a Parameter Query selecting all the InventoryID's for a location as a parameter.

Your code would then step through the recordset of locations, and use the TransferText method to export the Query for the currently active Location.

If you need help with the code post back.

Answer 5

Create a query that filters by Location.

Run a loop that loops though a list of locations and does an export based on the query for each location.