Importing a excel file with inconsistent row length, amount of columns, and blank cells.

Hello all,

   I am trying use Matlab to load an excel file that has multiple matrices of different sizes that sometimes have blank cells in the middle of the matrix. I save this file as a .txt and when I load it in Matlab an error message states inconsistent row length. Is there a way to replace blank cells in excel with NaN? or is there a way to solve this within Matlab?

-Thanks

Answer 1

[Walter Roberson]

Blank cells in the middle are not much problem with textscan():

>> textscan('12,,,34','%f%f%f%f','Delimiter',',')                   
ans = 
    [12]    [NaN]    [NaN]    [34]

However, with textscan() if you are using a numeric format, then lines that have fewer fields than expected will normally indicate to textscan() that it should proceed to the next line.

To handle insufficient fields, you could read each line and textscan() the string for each line. The positions that are not filled at the end will show up as empty cell arrays, whereas positions that are omitted in the middle will show up as nan if you scanned with a floating point format.

(If you scan with a %d format to get an integer data type, then by default empty positions in the middle show up as 0; you can use the EmptyValue parameter to change that; specifying "inf" (infinity) for that parameter will cause it to use the maximum value for that integer class.)

Answer 2

[Matt Tearle]

You can read in a cell array of all Excel cells using xlsread:

[~,~,raw] = xlsread('file.xls');

You can also specify a range to read. Given a cell array where everything is either numeric or empty, here's a way to turn the blanks into NaNs:

x = {1,2,[];[],3,4;[],[],6}
idx = cellfun(@isempty,x);
x(idx) = num2cell(NaN)
cell2mat(x)

[Walter Roberson]

Will that work on CSV files that have lines that have fewer columns than normal? If it will, then is that behaviour the same for Windows using COM to talk to Excel and for non-Windows systems that parse the file more manually ?

[John Collette]

This works to replace blank cells in a matrix. But the problem is I can't import the file into matlab. I tried to find and replace the blank cells within Excel, but Excel fills in an infinite amount of cells. The file is data from a 3D imagiging software and is a very large so manually editing it isn't an option.

[Walter Roberson]

John,

Could you confirm that some of the rows have fewer fields (not just fewer populated fields) ? e.g.,

Apple,,Nightshade,Tomato

Strawberry,Banana,Pecan

The missing field on the first line would not be difficult, but the second line only has 3 fields instead of 4 and that introduces complications. If we could be sure that such lines would instead be (e.g.)

Strawberry,Banana,Pecan,

then it would be easier.

[John Collette]

So the file looks like this

Apple,,Nightshade,Tomato

Strawberry,Banana,Pecan,Tomato, pecan, orange

puppy, light, android, car

I was suggested using a python code on the excell file saved as a .txt. Is there a way to use the Import function to get the fil into matlab?

[John Collette]

Okay so I am able to use theimport wizard on my csv file and I can generate a matrix with NaN's in the blank data spots. The problem is now that the file contains another set of data below the first that does not get input into the matrix. The second set is seperated from the first by an empty row and five header rows. This second set has more columns than the first matrix. Is there a way to use the wizard to make a second matrix containing the second set of data.