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

Waimea

Active Member
Joined
Jun 30, 2018
Messages
447
Office Version
  1. 365
Platform
  1. Windows
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 :)
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
399
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
 
Solution

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
399
Office Version
  1. 365
Platform
  1. Windows
Before:
1631827946834.png


After:
1631827984874.png
 

Waimea

Active Member
Joined
Jun 30, 2018
Messages
447
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Aug 8, 2021
Messages
399
Office Version
  1. 365
Platform
  1. Windows
For that, you can just record a macro I believe.

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

Waimea

Active Member
Joined
Jun 30, 2018
Messages
447
Office Version
  1. 365
Platform
  1. Windows
Thank you for your reply!

I got it to work by recording a macro.
 

Forum statistics

Threads
1,147,748
Messages
5,742,978
Members
423,769
Latest member
LongToast

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
Top