Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 535
  • Last Modified:

Create many CSV files from one CSV file

I have a single CSV file and need to create individual CSV files based on one column of data.  
How can I split the information so that I end up with 1 CSV file for each unique set of records in my original CSV file.
1 Solution
Rey Obrero (Capricorn1)Commented:
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
NorieData ProcessorCommented:
So you mean that the criteria to split the data is in a specific column?
ShawnGrayAuthor Commented:
Thanks Cap,
is it possible for the CSV file name to be based on the unique record from that field?

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Rey Obrero (Capricorn1)Commented:
<is it possible for the CSV file name to be based on the unique record from that field? >


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
Set cn = Nothing 

Open in new window

ShawnGrayAuthor Commented:
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
Rey Obrero (Capricorn1)Commented:
change the line

Set rs = db.OpenRecordset("tblAcctList")


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 ?
ShawnGrayAuthor Commented:
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
Rey Obrero (Capricorn1)Commented:
first create a query with

    select * from tblAcctList

then save as  qryAcctList

use this codes to create the .csv files

    Dim rs As DAO.Recordset, db as dao.database
    Dim strCsv As String, qd as dao.querydef, sSql as string
    set db=currentdb
    set qd=db.querydefs("qryAcctList")

    Set rs = CurrentDb.OpenRecordset("select distinct AccountNumber from tblAcctList")

    do until rs.eof
    strCsv = rs(0) & ".csv"
          ssql="select * from tblAcctList where AccountNumber=" & rs(0)

' if  AccountNumber is Text Data type, comment the above ssql and uncomment the ssql below

'  ssql="select * from tblAcctList where AccountNumber='" & rs(0) & "'"


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

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.
ShawnGrayAuthor Commented:
Cap, that was beautiful.  Thank you.
One last query,
 can the header row and the first column [acctnumber] be removed at export?
Rey Obrero (Capricorn1)Commented:
to remove the header, change this

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


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)
ShawnGrayAuthor Commented:
Cap -  Perfect!

You're the best.

Thank you again.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now