Why won't my excel file import onto matlab?

16 views (last 30 days)
Alicia
Alicia on 20 Mar 2021
Edited: dpb on 20 Mar 2021
I have two files of data. They are formatted the exact same, with different numbers. The noncorrected data imports perfectly fine onto Matlab using the Import button. However, I keep receiving an error when I try to import the corrected data. The error is: "Import operation failed. The most likely reason is that there are unimportable cells in the selection. Try adding a rule in the toolstrip to convert unimportable cells into numbers." This does not make sense as they are formatted the exact same way, with only the numbers being different, and the noncorrected data imported perfectly fine. I have also set it to replace nonimportable cells with Nan, as it was set for the uncorrected data, however this does not seem to work for importing the corrected data. How can I fix this issue and allow my corrected data file to be imported? Please help.

Accepted Answer

dpb
dpb on 20 Mar 2021
Edited: dpb on 20 Mar 2021
You've got a very inefficeint and hard-to-read for a generic code format in the spreadsheet ...there's text scattered around in same columns as the numeric data and blank lines as well. This is going to be very difficult to parse and do anything with, no matter what is the problem you're having...
data=importdata('Airfoil Data Corrected.xlsx');
worked ok here on r2019b (albeit it took at least a minute of struggle; I went to get a cup of coffee while waiting so not sure just how long actually was, but "a long time")...
Atmospheric Pressure 29.82
Temperature 74.2
p 0-1
Port Number
1 2 3 4
29.82 29.81973 29.81991 29.81991
Atmospheric Pressure 29.82
Temperature 74.2
p 0-2
Port Number
1 2 3 4
29.82 29.82 29.81991 29.81991
Atmospheric Pressure 29.82
Temperature 74.2
p 0-3
Port Number
1 2 3 4
29.81983 29.81973 29.81991 29.81991
I'd suggest to reformat the table in the workbook by columns for the Pressure, Temperature and label, then leave the actual data as the record...the above instead could be
Pressure Temperature CaseID P1 P2 P3 P4 ...
29.82 74.2 p 0-1 29.82 29.81973 29.81991 29.81991
29.82 74.2 p 0-2 29.82 29.82 29.81991 29.81991
29.82 74.2 p 0-3 29.81983 29.81973 29.81991 29.81991
...
Then you can directly read into a MATLAB table with variable names and values and will be much faster and far easier to process once you do get it into MATLAB--no NaN to remove and text to sort out and try to associate with the numerics, etc., etc., etc., ...
If you've got a lot of these, it wouldn't be too difficult to read each line-by-line and recast into the other form programmatically.
ADDENDUM:
Actually, it's not really quite so bad as it appears to do after the above...for the file you attached--
data=importdata('Airfoil Data Corrected.xlsx'); % read in the ugly-aranged spreadshet
isBad=all(isnan(p),2); % find the all-NaN records
p=data.data(~isBad,:); % and get rid of them; create temporary
Patm=p(1:4:end,2); % the Atmospheric pressure
T=p(2:4:end,2); % and Temperature
Pport=p(4:4:end,:); % and the various port pressures
tPort=data.textdata(3:7:end); % then the port ID text (?)
tAirCorr=table(Patm,T,tPort,Pport, ...
'VariableNames',[data.textdata([1 2 4]); {'Port Pressure'}]); % convert into table
The above yields:
>> head(tAirCorr)
ans =
8×4 table
Atmospheric Pressure Temperature Port Number Port Pressure
____________________ ___________ ___________ _____________
29.82 74.20 {'p 0-1'} [1×52 double]
29.82 74.20 {'p 0-2'} [1×52 double]
29.82 74.20 {'p 0-3'} [1×52 double]
29.82 74.20 {'p 0-4'} [1×52 double]
29.82 74.20 {'p 0-5'} [1×52 double]
29.82 76.50 {'p 1-1'} [1×52 double]
29.82 76.50 {'p 1-2'} [1×52 double]
29.82 76.50 {'p 1-3'} [1×52 double]
>>
I retained the port pressures as an array -- this lets address them by logical or numeric indexing by position easily rather than by an individual variable/column name. You can choose the other way if so desire by using array2table on the Pport array instead of including it in the table argument with the others. Of course then you're appending the two tables and so have to make the name arguments match, etc., ...
  1 Comment
dpb
dpb on 20 Mar 2021
Edited: dpb on 20 Mar 2021
The above 'Port Number' variable is probably a 'Test ID' instead, I'm guessing? It might make sense to split that apart and keep two variables as Test# and Sequence# so could easily group by either/or as well.

Sign in to comment.

More Answers (0)

Tags

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!