Split CSV file based on column data

M0rph

New Member
Joined
Apr 1, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
We are now running reports that will generate a (comma) CSV file with approx 5000 entries per file. I'd like to know how I can split this file into seperate (comma) CSV files based upon the name within specific column (instructor).

Ideally, when complete, each CSV file would:
- have the new CSV files named after the name in the instructor column
- contain the header row (A1 - P1)
- All rows that include the same instructor name from COLUMN N"

I'm open to different method to do this in as long as it works. This is new territory for me, so might take a little to understand how to do it.

An example of the CSV file shown in Excel would look like this:

Q1Q2Q3Q4Q5Q6Q7Q8Q9Q10Q11FacultyCourseInstructorSectionEmail
54686864684EDU1000Joe BlowAjoe@email.com
76434682828ART2500Pam DoeCpam@email.com
18762546364EDU1000Joe BlowAjoe@email.com
76352433435SCI3500Frank TankCfrank@email.com
54545265646EDU3040Joe BlowCjoe@email.com
22563425545PHY2050Frank TankBfrank@email.com
33558258262ART4000Pam DoeApam@email.com
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Hi M0rph, welcome to the MrExcel Forum.

Does this get you close to what you want to do.

To use, copy this code to a new .xlsm workbook and save it (which you will have to do just once). Open this new workbook and the CSV file you want to work with. Only those two Workbooks should be open. The code will save the newly created CSV workbooks to the same file location as the original CSV is saved in.

VBA Code:
Sub SplitCSV()
    
    Dim arr
    Dim wb As Workbook, Nwb As Workbook, CeSV As Workbook
    Dim typ As Long, x As Long, i As Long, ct As Long
    Dim rng As Range
    Dim FilePath As String
    
    Application.ScreenUpdating = False
    ct = Application.Workbooks.Count
    If ct <> 2 Then
        MsgBox "Please Close All Other Workbooks except for This One and Your CSV File!"
        Exit Sub
    End If
    For Each CeSV In Application.Workbooks
        typ = CeSV.FileFormat
        If typ = 6 Then CeSV.Activate
        FilePath = Application.ActiveWorkbook.Path
        Exit For
    Next
    ActiveSheet.AutoFilter.ShowAllData
    arr = Range("N2:N" & Cells(Rows.Count, 14).End(xlUp).Row)
    With CreateObject("Scripting.Dictionary")
    For x = LBound(arr) To UBound(arr)
        If Not IsMissing(arr(x, 1)) Then .Item(arr(x, 1)) = 1
    Next
    arr = .Keys
    End With
    For i = LBound(arr) To UBound(arr)
        ActiveSheet.Range("N1").AutoFilter
        ActiveSheet.Range("$A$1:$Q" & Cells(Rows.Count, 14).End(xlUp).Row).AutoFilter Field:=14, Criteria1:=arr(i), Operator:=xlFilterValues
        Set rng = ActiveSheet.AutoFilter.Range
        Set wb = Workbooks.Add
        rng.Copy Range("A1")
        Set Nwb = ActiveWorkbook
        Nwb.SaveAs FilePath & "\" & arr(i) & ".csv", FileFormat:=xlCSV
        Nwb.Close SaveChanges:=True
    Next
    ActiveSheet.AutoFilter.ShowAllData
    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top