Open all .csv files in a folder and remove columns?

[Waimea]

Hi,

I am trying to open a folder in excel and open all .csv files in that folder and remove all columns but A,C and F.

The columns in my workbook is "A" to "AJ".

All help is appreciated

 

[Gokhan Aycan]

Would this work? Just change the strFolder variable to your folder. An output folder will be created under your folder and new csv files will be saved there.

Option Explicit

Public Sub CSV()
    
    Const ForReading = 1
    Const ForWriting = 2
    
    Dim i As Long
    
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    Dim strFolder As String
    strFolder = "D:\Test VBA\CSV"
    
    Dim oFile As Variant
    Dim oCSV As Variant
    
    Dim strCSV As String, strNew As String
    Dim arr As Variant
    
    If Not fso.FolderExists(strFolder) Then
        Set fso = Nothing
        Exit Sub
    End If
    
    If Not fso.FolderExists(fso.BuildPath(strFolder, "Output")) Then
        fso.CreateFolder (fso.BuildPath(strFolder, "Output"))
    End If
    
    For Each oFile In fso.GetFolder(strFolder).Files
        
        If UCase(fso.GetExtensionName(oFile.Path)) = "CSV" Then
            
            Set oCSV = fso.OpenTextFile(oFile.Path, ForReading)
            
            strNew = ""
            
            Do While Not oCSV.AtEndOfStream
            
                strCSV = oCSV.ReadLine
                arr = Split(strCSV, ",")
                strNew = strNew & arr(0) & "," & arr(2) & "," & arr(5) & vbCrLf
            Loop
            
            oCSV.Close
            
            Debug.Print strNew
            
            Set oCSV = fso.CreateTextFile(fso.BuildPath(fso.BuildPath(strFolder, "Output"), oFile.Name), True)

            oCSV.Write (strNew)
            
            oCSV.Close
            
        End If
        
    Next oFile
    
    Set oCSV = Nothing
    Set fso = Nothing
    
End Sub

 

[Gokhan Aycan]

Before:

After:

 

[Waimea]

Hi,

thank you for your reply!

It's late here so I will try your code tomorrow. But from the look of the before and after your code does what I want.

What would be a good way to delete the unwanted columns and just store the macro in my personal workbook.

So that I manually open the files and then the macro deletes all unwanted columns?

 

[Gokhan Aycan]

For that, you can just record a macro I believe.

Start recording a macro.
Delete columns as usual.
Stop Recording.

 

[Waimea]

Thank you for your reply!

I got it to work by recording a macro.