Create many CSV files from one CSV file

1. import your csv file into an access table
2. create/open a distinct recordset (rs) based on the column data you mentioned above

3. while looping on the first recordset (rs) create an querydef using the sql

       strSQl="select * from tableName where [columnName]='" & rs(0) & "'"

       export the modified querydef with

       docmd.transfertext acexportdelim, "exportSpecName","queryName","c:\folder\myCsv.csv", true

So you mean that the criteria to split the data is in a specific column?

Thanks Cap,
is it possible for the CSV file name to be based on the unique record from that field?

<is it possible for the CSV file name to be based on the unique record from that field? >

yes

dim strCsv as string

strCsv= rs(0) & ".csv"

 docmd.transfertext acexportdelim, "exportSpecName","queryName","c:\folder\" & strCsv, true

OK, you have no need to use access...
The following code will create a new csv file based upon the criteria using Excel.
(It will ofc need a little work to suit your needs)
It assumes the csv file is in the same location as the workbook
Will also need to set reference to ActiveX Data Objects Library

strPath = ThisWorkbook.Path & "\"

Set cn = CreateObject("ADODB.Connection")

strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";Extended Properties=""text;HDR=Yes;FMT=Delimited"";"
 
cn.Open strcon

strSQL = "SELECT Field1, Field2, Field3 INTO New.csv FROM OriginalFile.csv WHERE (((Field1) Like '%txt%'));"
'Creates new csv file
cn.Execute strSQL
cn.Close
Set cn = Nothing 

Select allOpen in new window

Cap,
I'm a little rusty with this stuff.
Getting an object required error on my first line.

    Set rs = db.OpenRecordset("tblAcctList")
    Dim strCsv As String
    strCsv = rs(0) & ".csv"
    DoCmd.TransferText acExportDelim, Acct#, "tblacctlist", "c:\CSVfolder\" & strCsv, True

change the line

Set rs = db.OpenRecordset("tblAcctList")

with

dim rs as dao.recordset    'add this line
Dim strCsv As String
Set rs = currentdb.OpenRecordset("tblAcctList")

what is the first column/field of table  tblAcctList ?

The first field is now [AccountNumber]
This mostly works
---
    Dim rs As DAO.Recordset    'add this line
    Dim strCsv As String
    Set rs = CurrentDb.OpenRecordset("tblAcctList")

    strCsv = rs(0) & ".csv"
    DoCmd.TransferText acExportDelim, AcctNumber, "tblacctlist", "c:\CSVfolder\" & strCsv,   True
---
But it only created one CSV file will all records

does the CSV file have a header row?
is the CSV file well formed?

Otherwise, capricorn1's approach seems reasonable.

Alternatives include:
* sorting the CSV file and the processing the records, line-at-a-time, and output to same or new file, depending on the first field value.
* Use a recordset, like cap1, but Transform the row data for each group using the recordset GetRows method and Join() function with a comma delimiter.  Standard VB I/O operations would be used to create lines in the output CSV files.
* you might be able to use Powershell.

Cap, that was beautiful.  Thank you.
One last query,
 can the header row and the first column [acctnumber] be removed at export?

to remove the header, change this

DoCmd.TransferText acExportDelim, AcctNumber, "qryAcctlist", "c:\CSVfolder\" & strCsv,   True

with

DoCmd.TransferText acExportDelim, AcctNumber, "qryAcctlist", "c:\CSVfolder\" & strCsv,   False

to exclude the first column, you need to itemized the fields you want to export in the sql statement of the query

ssql="select col2,col3,col4, .. coln from tblAcctList where [accountnumber]=" & rs(0)

Cap -  Perfect!

You're the best.

Thank you again.