import csv file and skip the first 5 lines that contain text

Hi,

I have 1000 files of which the first few lines all look like the ones below. There are 11 columns of data.

What I want to do is import these csv files but not the first 5 lines that contain text. The remaining part only contains numbers.

I think I have to work with textscan and headerlines, but I am not sure how to.

Thank you for your help!

StimName,"Subject","sequence","Date","Time","FirstTR","SessionNo","Trial","trseq","StimDuration","sound"
Fc1_incr,"ai","PupilToPupil_ai",#2011-03-29#,#1899-12-30 13:44:26#,19,3,1,19,4200,"dum"
StartPosX,"StartPosY","dum","dum","dum","dum","dum","dum","dum","dum","dum"
.5,.5,0,0,0,0,0,0,0,0,0
SystemTime,"MediaTime","Valid","GazeX","GazeY","LeftX","LeftY","LeftPupil","RightX","RightY","RightPupil"
13,0,3,.5333887,.5088381,.4567934,.5429853,3.403872,.6443502,.5561797,3.531345
31,0,3,.4986517,.4756647,.4566537,.5422695,3.449122,.6440785,.5554679,3.480466
47,0,3,.4954451,.4924191,.456565,.5420035,3.41274,.6440116,.5553075,3.527733
104,0,3,.5075845,.4980071,.4565195,.541966,3.402078,.6439878,.5554283,3.512885
104,0,3,.5068237,.4639177,.4564521,.5419917,3.34627,.643941,.5556482,3.48911

Answer 1

[Oleg Komarov]

Use textscan:

EDIT typo:

fid  = fopen('filename')
data = textscan(fid,'%f%f%f%f%f%f%f%f%f%f%f','Delimiter',',','HeaderLines',5)
fid = fclose(fid);

[Mariska Kret]

Thanks for the quick answer! But I get an error when I do this. Do you know what is wrong?

>> data = read_csv

fid =

-1

??? Error using ==> textscan

Param/value pairs must come in pairs.

Error in ==> read_csv at 4

data = textscan('%f%f%f%f%f%f%f%f%f%f%f','Delimiter',',','HeaderLines',5)

>> this is the .m file:

function data = read_csv

fid = fopen('filename');

data = textscan('%f%f%f%f%f%f%f%f%f%f%f','Delimiter',',','HeaderLines',5);

fid = fclose(fid);

end

[Oleg Komarov]

In filename you're supposed to put a string indicating the full path of the file you want to import.

[Mariska Kret]

Hi Oleg!

Thank you for your comment. This helps. But I get another error now.

??? Error using ==> textscan

Param/value pairs must come in pairs.

Error in ==> test2 at 4

data = textscan('%f%f%f%f%f%f%f%f%f%f%f','Delimiter',',','HeaderLines',5);

Best wishes,

Mariska

[Oleg Komarov]

Please recheck my code, I forgot last time to put the fid.

[Mariska Kret]

Hi Oleg,

thanks for the quick reply. I still get this error.

??? Error using ==> textscan

Invalid fid.

Error in ==> test2 at 4

data = textscan(fid,'%f%f%f%f%f%f%f%f%f%f%f','Delimiter',',','HeaderLines',5)

[Oleg Komarov]

Did you supply the full correct name to the file you want to open?

Example:

fid('C:\Users\Oleg\Desktop\myfile.txt'

[Mariska Kret]

Ah, that helps! Thanks a lot. I need to interpolate some missing data in columns 8 and 11. After adding the * next to the percentage sign, I now have the two columns I need. I want to run my interpolation script which starts as follows:

function data = interp_datacsv(data, method)

orig_valid = find(data(:,1));%orig_valid = find(data(:,5));

but I get this error: >> data = interp_datacsv(ans, 'spline')

??? Function 'find' is not defined for values of class 'cell'.

Error in ==> interp_datacsv at 6

orig_valid = find(data(:,1));%orig_valid = find(data(:,5));

>>

What can I do to solve this?

I appreciate your help very much!

best,

Mariska

[Oleg Komarov]

http://www.mathworks.com/matlabcentral/answers/6200-tutorial-how-to-ask-a-question-on-answers

[Mariska Kret]

Dear Oleg,

Thank you for your reply. I see the problem: I have a cell structure but I cannot make calculations with that so I cannot use the function 'find'... So, I should either adjust my read file to make it a structure rather than a cell, right? I typed cell2mat(ans) in the command promt and got what I want, but I do not manage to include it in the .m-file.

Or do you agree with Mohammed and do I need to convert my csv files to xls?

thank you for your help.

Mariska

[Oleg Komarov]

No, I don't agree with mohammed.

Add to the call to texscan this option:

data = textscan(...,'CollectOutput',1);

data = data{:};

Now you *should* be able to interpolate.

Post eventually whole error messages if you get any.

[Mariska Kret]

Hi Oleg,

thanks again. Maybe this CollectOutput is not supported by Matlab6? Or I do something wrong.

function data = read_csv

fid = fopen('E:\Experimenten\Social phobia\Data\participants_all data\Fc1.csv');

data = textscan(fid,'%*f%*f%*f%*f%*f%*f%*f%*f%f%*f%f','Delimiter',',','HeaderLines',5,'CollectOutput',1);

data = data{:}

fid = fclose(fid);

end

and here is the error:

??? Error using ==> textscan

Unknown option 'CollectOutput'.

Error in ==> read_csv at 4

data = textscan(fid,'%*f%*f%*f%*f%*f%*f%*f%*f%f%*f%f','Delimiter',',','HeaderLines',5,'CollectOutput',1);

>>

Mariska

[Oleg Komarov]

Uhm, never used Matlab 6. Then just use cell2mat.

[Mariska Kret]

I now have a script which works for each csv file separately. It starts like this:

data = csvread('participant1.csv', 5); % read the file, skip first five lines

my output looks like this:

participant1=struct('pathstr',pathstr,'name',name,'ext',ext,'postbaseline',[postbaseline_baseline_corrected_0_500ms,postbaseline_baseline_corrected_500_1000ms...etc]);

I would like to loop through all the csv files. So as a first step, I should read in all the csv files, right?

Why doesn't the following work?

data = csvread('*.csv', 5)

[Oleg Komarov]

You need to use a loop (http://matlab.wikia.com/wiki/FAQ#How_can_I_process_a_sequence_of_files.3F)

% to retrieve all the csv files in a folder

f = dir('*.csv');

f = {f.name};

% LOOP

for n = f

csvread(n{:})

end

[Mariska Kret]

Hi,

I get an error message when I run this code because the first five lines consist not only of numbers. That is why I skipped the first five lines in my previous code. How can I incorporate this in the last line of your code?

data = csvread('test2.csv', 5); %

??? Error using ==> textscan

Mismatch between file and format string.

Trouble reading number from file (row 1, field 1) ==> subje

Error in ==> csvread at 45

m=dlmread(filename, ',', r, c);

Error in ==> read_all_csvfiles at 6

csvread(n{:}) % m=dlmread(filename, ',', r, c);

[Oleg Komarov]

Use your data = csvread(...,5); in the loop.

data = cell(numel(f),1);

for n = 1:numel(f)

data{n} = csvread(f{n},5);

end

To consolidate (If all files with same num of cols):

data = cat(1,data{:});

Answer 2

[mohammed]

I advice you to convert your files to excel, then you can use the following code

inputs = xlsread('fileName.xls', 1, 'A6:D1000');

where A6 the first cell and D1000 the last cell

Answer 3

[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