Concatenate tables with variable data types in columns

I'm trying to vertically concatenate the tables attached here.

tbl = [readtable('tsttbl1.xlsx'); readtable('tsttbl2.xslx')];  

Then, I get the message:

 >> Cannot concatenate the table variable 'Field61_FloatArray_1_53_' because it is a cell in one table and a non-cell in another. 

For some reason when the excel file is loaded into MATLAB, the columns take on differing datatypes. In this case I've got cell vs. non-cells and the concatenation can't occur. How can I do this? Can I convert all the columns with cell values to non-cell values or visa-versa?

Thanks.

Answer 1

[Jon]

I think part of the problem is the NAN's in your files. They don't automatically get imported as MATLAB NaN's as you hope. It just thinks they are text.

For this you can use

readtable('tsttbl1.xlsx','TreatAsMissing','NAN')

Unfortunately I think there are further complications where you have a whole column of NAN in your input file and it thinks the whole column is text.

Hopefully this gets you a little further

[Jon]

Maybe use setvartype explicitly to double for all columns except the first. Unfortunately I don't have much experience with this, to give you exact instructions. Maybe someone else can help with details if this is in fact the issue.

[Eric Escoto]

This is the result. Doesn't seem benificial to do this.

It's odd that the readtable command reads some of the 'NAN' as NaN vs. the very next column as 'NAN'.

[Jon]

Without using the treat as missing the NAN's in the excel just get treated as text. I think you still have a problem though when an entire column is NAN. Then I think it treats the whole column as non numeric and doesn't see them as NaN's. Sorry I don't have time to dig into it deeper, but maybe this helps, or someone else can pick it up from here.

Answer 2

[Seth Furman]

As Jon mentioned, we can set the variable type explicitly for those variables we know to be floating point numeric.

file1 = "tsttbl1.xlsx";
opts = detectImportOptions(file1, "VariableNamingRule", "preserve");
floatVars = contains(opts.VariableNames, "Float");
opts.VariableTypes(floatVars) = {'double'};
t1 = readtable(file1, opts);

file2 = "tsttbl2.xlsx";
opts = detectImportOptions(file2, "VariableNamingRule", "preserve");
floatVars = contains(opts.VariableNames, "Float");
opts.VariableTypes(floatVars) = {'double'};
t2 = readtable(file2, opts);

t = [t1; t2];

t(:, 1:3)

ans =

10×3 table

TIMESTAMP RECORD Field61_FloatArray(1,1)

____________________ ______ _______________________

06-Feb-2019 01:15:30 0 0.762

06-Feb-2019 01:16:00 1 1.455

06-Feb-2019 01:16:30 2 0.877

06-Feb-2019 01:17:00 3 0.747

06-Feb-2019 01:17:30 4 1.023

02-Jan-2020 00:23:00 4034 0.544

02-Jan-2020 00:31:00 4035 2.27

02-Jan-2020 00:32:00 4036 0.733

02-Jan-2020 00:33:00 4037 1.023

02-Jan-2020 00:34:00 4038 0.742