Removing the top rows of a csv file

I have a folder with multiple .csv files, similar to the one attached here. I am trying to remove the top rows of this csv file (everything until the row with Time and Trace). I need a script to run through the folder, delete all the top row portion of the files and resave them. Could you please help me with that? Thank you.

[Harsimran Singh]

use this link, it works perfectly for me:

https://www.mrexcel.com/board/threads/delete-first-2-rows-of-each-csv-file-in-a-folder-1-000-csv-files.890203/

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:2").Delete

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

Answer 1

[Jan]

Folder   = 'D:\Your\Folder'; 
FileList = dir(fullfile(Folder, '*.csv'));
for iFile = 1:numel(FileList)
    File = fullfile(Folder, FileList(iFile).name);
    
    % Read the file:
    FID  = fopen(File, 'r');
    if FID < 0
        error('Cannot open file: %s', File);
    end
    C = fread(FID, [1, inf], '*char');  % [EDITED] inf -> [1, inf]
    fclose(FID);
    index = strfind(C, 'Time (s)');
    
    if numel(index) ~= 1    
        error('Key not found in file: %s', File);
    end
    
    % Write cropped contents:
    FID  = fopen(File, 'w');
    if FID < 0
        error('Cannot open file: %s', File);
    end
    fwrite(FID, C(index:numel(C)), '*char');
    fclose(FID);
end

Create a backup at first or write to a new folder:

InFolder  = 'D:\Your\Folder';
FileList  = dir(fullfile(InFolder, '*.csv'));
OutFolder = 'D:\Your\FolderModified';
mkdir(OutFolder);
for iFile = 1:numel(FileList)
    InFile = fullfile(InFolder, FileList(iFile).name);
     ...
    OutFile = fullfile(OutFolder, FileList(iFile).name);
     ...
end

[MATLAB90]

Thanks a lot for your time and detailed answer. I ran the script, but it gives me an error for strfind:

Error using strfind

First argument must be a string array, character vector, or cell array

of character vectors.

Error in doric (line 19)

index = strfind(C, 'Time (s)');

I didn’t change anything from your script, except for the slashes in the folder path since I’m using a mac. Also, “File = fullfile(Folder, FileList(iFile).name);”

Returns a folder path with “,” in between all the file names from FileList and does not separate the files. Could you please help me with that

[Jan]

I cannot imagine how

C = fread(FID, inf, '*char');

can reply something, which is not a CHAR array. But you can use the debugger to find out, what C is.

If fullfile(Folder, FileList(iFile).name) replies something, which contains a comma, this comma is either a part of the folder or file name. Please check this again and post the class and contents of File.

[MATLAB90]

Thanks again for the reply. Right now when I run the script, here's what I get for the variables:

FileList =

2×1 struct array with fields:

…

iFile =

1

File =

'/Users/annie.v/Desktop/Test/2019_07_10_0000a.csv'

FID =

3

And C is a 5538640x1 char that it cant display.

What I was trying to say in my previous comment was when I run your script line by line in the command window, and when I get to “File = fullfile(Folder, FileList(iFile).name);” line (without using the for loop) it gives me a folder path containing all the file names. If I have 2 .csv files in that folder, a and b lets say, File would return something like : D:/Your/Folder/a.csv/b.csv

[Jan]

Because iFile is the loop counter, it is not meaningful to run this line of code outside the loop.

The output looks, like my code does what it should.

[MATLAB90]

Ok, thank you. Any idea why it’s still giving me the same error (copied below)?

For C outcome, it gives all the characters separately (please see below). Is that why it can't look for "Time (s)" with strfind? C is a 5538640x1 char that won't even display due to space limit, but below is part of the outcome copied from the command window.

Error using strfind

First argument must be a string array, character vector, or cell array

of character vectors.

Error in doric (line 19)

index = strfind(C, 'Time (s)');

C =

…

'3'

'1'

'.'

'6'

'9'

'2'

'7'

','

'-'

'1'

'0'

'.'

'9'

'2'

'5'

'3'

'←'

'↵'

[Jan]

Now I see it: the FREAD command relied a column vector but STRFIND expects a ow vector. I've fixed this in the code above.

[MATLAB90]

Thank you for your time and all the help. It works perfectly now.