How do I convert row vector cell array into a matrix cell array, when cell arrays contain both strings and numbers?

I know there should be a simple way to do this, just can't see to figure it out.

Say I have a csv file that looks like below:

$ABC,1233.00,101716,555.7645777,N,666.2382955,W,5,07,0.0389,10.0,M*10

$ABC,1234.00,101816,555.7645636,N,666.2381602,W,3,14,0.0012,11.0,M*16

$ABC,1235.00,101816,555.7645674,N,666.2381625,W,3,12,0.0015,12.0,M*1E

$ABC,1236.00,101816,555.7647076,N,666.2390001,W,5,13,0.0085,13.0,M*1B

$ABC,1237.00,101816,555.7645615,N,666.2381570,W,5,14,0.0032,14.0,M*1D

I'm trying to load this text file into MATLAB and have it come out as a cell array that is {5,12}. What I'm getting with the code below is a cell array that is {1,12} with each entry sized to [1,5] or {1,5}. I know I can probably manually set it to be correct but was looking for the elegant (simple) solution.

Here's the code below:

clear all

close all

clc

fileID=fopen('test.csv');

formatSpec='%s %f %f %f %s %f %s %f %f %f %f %s';

test=textscan(fileID, formatSpec,'Delimiter',',');

Thanks!

Answer 1

[Walter Roberson]

temp = [test(1), cellfun(@num2cell, test(2:4), 'Uniform', 0), test(5), {num2cell(test{6})}, test(7), {num2cell(test{8})}];
output = horzcat(temp{:});

That is, you need to num2cell() each of the numeric columns, producing a cell array. Put that beside the cell arrays with the strings, so now you have a set of cell arrays in a vector. horzcat() together the expansion of that.

Or you could xlsread() it and pull out the raw output. Or you could readtable()

[sipsj11]

Yeah, I've done this. Was looking for a bit more of an elegant solution that I didn't have to manually set up the cell array

test1=[test{1} num2cell(cell2mat(test(1,2:4))) test{5} num2cell(cell2mat(test(1,6))) test{7} num2cell(cell2mat(test(1,8:11))) test{12}]

Issue with xlsread or readtable is my file format in actuality isn't .csv it's essentially .abc and I get an error message (too many files that I will be looping through to change file extensions on all of them).

[Guillaume]

readtable does not care about file extension. To force it to read the file as text:

readtable('c:\pathto\file.abc', 'FileType', 'text')

Answer 2

[Walter Roberson]

Adopting from a routine I had sitting around. This reads in a file in csv format, and converts the columns to numbers if it can; columns that cannot be converted to numbers are tried to convert to datetime; columns that cannot be converted to numbers or datetime are tried to convert to serial date numbers; columns that cannot be converted to any of those are left alone. At the end, either a table or a cell array of values is produced.

    filename = 'test.abc';

    fid = fopen(filename, 'rt');

    all_lines = fread(fid, [1 inf], readmode);
    fclose(fid);

    header_line = sscanf(all_lines, '%[^\n]');
    data_cell = textscan(header_line, '%s', 'delimiter', ',');
    header_fields = reshape(data_cell{1}, 1, []);
    num_field = length(header_fields);
    fmt = repmat('%s', 1, num_field);
    data_cell = textscan(all_lines, fmt, 'delimiter', ',', 'HeaderLines', 1);

    data_fields_text = horzcat(data_cell{:});

    data_fields = data_fields_text;

    %okay, now we have a cell array of strings. But some of them might look
    %numeric, and some of them might look like dates. Convert what we can.

    for field_number = 1 : num_field
        this_field = data_fields(:,field_number);

        %try converting it as a number. If no NaN show up then we accept
        %that. Note that this does not handle "missing" values at all, not
        %even a plain empty cell.

        data_as_numeric = str2double(this_field);
        if ~any(isnan(data_as_numeric))
            data_fields(:,field_number) = num2cell(data_as_numeric);
            continue;
        end

        was_date = false;
        try
                data_as_date = datetime(this_field);
                was_date = true;
         catch
         end

        if ~was_date
            %it is deliberate that we try datenum even if we tried and
            %failed on datetime . datenum conversion is more forgiving on
            %mixed formats and some details such as whether there are 3
            %digits for milliseconds
            try
                data_as_date = datenum(this_field);
                was_date = true;
            catch
            end
        end

        if was_date
            data_fields(:,field_number) = num2cell(data_as_date);
        else
            %nope, not a datetime or a date, leave it alone
        end
    end

    %if you want the result as a table:

    data_table = cell2table(data_fields, 'VariableNames', header_fields);

    %if you want the result as a cell array of individual values

    %data_table = data_fields;