Hello All, I have multiple CSV files saved in a folder and need a script that will open all the csv files, delete the first row and first column, save back leaving the file format unchanged.
thanks
Hello All, I have multiple CSV files saved in a folder and need a script that will open all the csv files, delete the first row and first column, save back leaving the file format unchanged.
thanks
Re: Delete First Row & Column in multiple CSV
Why ?
Re: Delete First Row & Column in multiple CSV
because i need to upload the csv file and do not need the first row and first column. so i need to delete the first row and column and the remaining cells adjusted to fill the deleted space.
Re: Delete First Row & Column in multiple CSV
hello kindly help
Re: Delete First Row & Column in multiple CSV
You could try this method - this code will allow you to browse to the folder with CSV files - it will then iterate through all the csv files and remove row 1 and column 1. BEFORE running this code BACKUP all the files - this code will alter the csv files so make sure you have a BACKUP. I cannot emphasise this enough.
Option Explicit
Sub FixCsvFiles()
Dim SelectFolder As String
Dim csvFiles As Variant
Dim csvWb As Workbook
Dim x As Integer
'browse for folder with csv files
On Error GoTo FixCsvFiles_Error
SelectFolder = GetFolder("c:\")
Application.ScreenUpdating = False
'Check user did not cancel folder selection
If SelectFolder = "" Then
MsgBox "No Folder Selected - Cannot continue", vbCritical
End
End If
SelectFolder = SelectFolder & "\"
csvFiles = Dir(SelectFolder & "*.csv")
Do While csvFiles <> ""
Set csvWb = Workbooks.Open(SelectFolder & csvFiles)
Rows("1:1").Delete Shift:=xlUp
Columns("A:A").Delete Shift:=xlToLeft
x = x + 1
csvWb.Close True
csvFiles = Dir
Loop
Application.ScreenUpdating = True
MsgBox "A total of " & CStr(x) & " files processed", vbInformation
On Error GoTo 0
Exit Sub
FixCsvFiles_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure FixCsvFiles of Module2"
End Sub
Function GetFolder(strPath As String) As String
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "BROWSE TO FOLDER LOCATION WITH CSV FILES"
.AllowMultiSelect = False
.InitialFileName = strPath
If .Show <> -1 Then GoTo NextCode
sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Function
Display More
See attached workbook.
Option 2 - You could open the CSV for binary access and then manipulate the resultant array - this is more complicated - sample code:
Open fileName For Binary As #fnum
csvData = Space$(LOF(1))
Get #1, , csvData
Close #1
arrData() = Split(csvData, vbCrLf)
'By starting loop at 1 this will delete the first row of data
For k = 1 To UBound(arrData)
'This will remove the first value in the row which is same as deleting the first column
arrData(k) = Mid(arrData(k), InStr(arrData(k), ",") + 1)
Next
Display More
However option 1 I think would be easier
Re: Delete First Row & Column in multiple CSV
You just made my day, thanks a lot bro. I prefer the first option
Don’t have an account yet? Register yourself now and be a part of our community!