Reading a CSV file with header and times

Good Afternoon,

I am having difficulty loading csv files into Matlab. The problem seems to be with the header (which contains strings) and the data values in two of the columns (they contain time values in 00:00 format).

I am able to ignore the header through use of the 'csvread' function, but Matlab won't load the time values and gives me the below errors.

EDU>> x = csvread('SUNY_070054005.csv', 1, 0)

??? Error using ==> dlmread at 145 Mismatch between file and format string. Trouble reading number from file (row 1, field 5) ==> :15,0

Error in ==> csvread at 50 m=dlmread(filename, ',', r, c);

The data in the CSV file are in the following format...

Date,Itime,Uglo,Udir,Udif,Stime,Sglo,Sdir,Sdif

1998-01-01,00:15,0,0,0,01:00,0,0,0

1998-01-01,01:15,0,0,0,02:00,0,0,0

I would use the wizard, but I have to import 281 csv files.

Any help would be appreciated.

Many Thanks,

Steven

Answer 1

[Sven]

Hi Steven,

The string you've got for the dates and times aren't automatically recognised as simple dates - you'll need to read them in as strings:

fid = fopen('myFile.txt')
HDRS = textscan(fid,'%s %s %s %s %s %s %s %s %s',1, 'delimiter',',')
DATA = textscan(fid,'%s %s %d %d %d %s %d %d %d','delimiter',',')
fclose(fid)

outCell = cell(size(DATA{1},1), length(HDRS));
for i = 1:length(HDRS)
    if isnumeric(DATA{i})
        outCell(:,i) = num2cell(DATA{i});
    else
        outCell(:,i) = DATA{i};
    end
end
myStruct = cell2struct(outCell, [HDRS{:}], 2);

Does this work for you? It worked for a small text file that I made from your input. You can convert stringed dates (and times) using the datestr function.

[Steven]

Thank you so much Sven. It worked :-)

I now have a 9x1 cell array. Each cell, however, is 70128x1 and I want to open each cell as a separate variable based upon the original file's header. For example, I want to open the first cell and make it into a 70128x1 array called "date".

I've tried using the cell2struct function but am not sure if I'm on the right path.

Below is my code.

fid = fopen('SUNY_070054005.csv');

DATA = textscan(fid,'%s %s %d %d %d %s %d %d %d','delimiter',',','headerlines',1);

fclose(fid);

Headings = 'Date';

DATE = cell2struct(DATA(1,1),Headings,1);

Any idea?

Thanks,

Steven

[Sven]

Hi Steven, I've updated the answer to do this second part in a way that I thought would be useful. Keep in mind that I've used "%d" which will convert all numbers in the text files to integers. Use "%f" if you need floats.

[Steven]

Sven,

I tried the code you provided and it works :-). I wish I could have thanked you sooner, but I wasn't able to get on earlier in the week.

Thanks again :-)

-Steven

Answer 2

[Stanislav]

The easiest way for me is :

path='C:\folder1\folder2\';
data = 'data.xls';
data = dataset('xlsfile',sprintf('%s\%s', path,data));

Of cource you could also do the following:

[data,path] = uigetfile('C:\folder1\folder2\*.xls');
data = dataset('xlsfile',sprintf('%s\%s', path,data));