Exporting Tables/Queries into csv files

[ckrows]

I am working with large text files.
I have a request to break up 1 large text file into indiviudal files.

My data example is, i have more fields, but in hopes of keeping it simple this is what i have.
CustID, CustName.

The large file currently has over 100 different CustID's. The request is to create a csv file at each change in the custID and to name the file "CustName".csv

i was able to create VBA code to export to CSV at the click of a button, however it exports the whole table or the whole query, how could i create it creates a file per?

export code...
Private Sub Command19_Click()
DoCmd.TransferText acExportDelim, , "qryMiffcoIncoming", "c:\my documents\\test_csv.csv"
'Shell "excel.exe C:\test_csv.xls ", vbNormalFocus ' Opens Specified file
End Sub

[Khriskin]

It would depend on what you need the program to do. My first inclination would be to suggest getting a list of the unique IDs (by query) and then looping through them (using a recordset). On each loop you could set the export query to filter by the current ID and kick out a csv file.

However I see that you are opening the file right after you create it, did you want to do this for every file created?

Let me know if you would like examples in code as opposed to generalities! ^_^

[ckrows]

It would depend on what you need the program to do. My first inclination would be to suggest getting a list of the unique IDs (by query) and then looping through them (using a recordset). On each loop you could set the export query to filter by the current ID and kick out a csv file.

However I see that you are opening the file right after you create it, did you want to do this for every file created?

Let me know if you would like examples in code as opposed to generalities! ^_^

I would love some code examples. Opening the file at the end was just for me to check the output. Once it is coded it should run and no need to open.

[Khriskin]

Okay , here's a rough outline for you. It should give you general idea of how I was thinking of handling the problem, but please keep in mind I'm by no means an expert. ^_^;; <-(sheepish look) ((baa!))

1. I'm assuming you're importing the original text file into Access as a table.

2. I'm naming this table [tblData]

2a. I'm assuming [tblData] has two fields (in this mockup), [CustID] and [CustName]

2b. I'm assuming that [CustID] is a Long (autonumber) and [CustName] is a string.

2c. I'm assuming the [CustID] is unique and that [CustName] may have duplicates. (ie. two John Smith's)

3. I'm creating a throw-away query called [qrySQL]

So with these assumptions:

Private Sub ExportFile()
Dim rsCustID As DAO.Recordset
Dim strSQL As String
Dim strFileName As String
 
'First we need to get a list of the unique CustIDs
strSQL = "SELECT DISTINCT tblData.CustID"
strSQL = strSQL & " FROM tblData"
strSQL = strSQL & " ORDER BY tblData.CustID;"
 
'Open a copy of this query as a recordset
Set rsCustID = CurrentDb.OpenRecordset(strSQL)
 
'I always assume an empty recordset is possible, so...
If Not ((rsCustID.EOF) And (rsCustID.BOF)) Then
rsCustID.MoveFirst
 
'Loop until you hit the end of the query/recordset
Do While Not (rsCustID.EOF)
 
'First we need to filter the table to the current CustID
strSQL = "SELECT tblData.CustID, tblData.CustName"
strSQL = strSQL & " FROM tblData"
strSQL = strSQL & " WHERE (((tblData.CustID)=" & rsCustID!CustID & "));"
CurrentDb.QueryDefs("qrySQL").SQL = strSQL
 
'Since we pulled the unique CustID from the table, it should be safe to
'  assume that there will be data to export
strFileName = "c:\my documents\" & rsCustID!CustName & ".csv"
DoCmd.TransferText acExportDelim, , "qrySQL", strFileName
 
rsCustID.MoveNext
Loop
 
MsgBox "Export Complete."
End If
 
End Sub
 
 

Now obviously there isn't as much error trapping in this as there should be, but that should give you a rough idea of what I was thinking. ^_^