Matlab not recognizing data is scientific notation?

28 visualizzazioni (ultimi 30 giorni)
Okay, I don't have the best handle on wrangling data types in Matlab, but to me it looks like Matlab is just not recognizing that I have a selection of numbers of which some are written in scientific notation.
My data comes in like this:(see confusion document attached), where some numbers are written in scientific notation and some are not. Excel seems to be able to recognize the scientific numbers as numbers and offers to change the data type if I click on the cells. However, Matlab just reads in the data (i'm using readtable) as characters(I think) and I cannot figure out how I can make matlab see the table as numerical values.
I've been running around the forums for a while but I can't see what I'm missing here or a previously asked question that helps me.
My goal here is that I am coalescing the data of many files(600+) that are like confusion.xlsx into one larger file, but needs to be written into this larger file as a number, not as a character.
  2 Commenti
Kathryn Vernon
Kathryn Vernon il 18 Giu 2021
I figured it out! For future users, here's what I did:
opts = detectImportOptions('confusion.xlsx')
opts = setvartype(opts,{'Var3'},'double')
a=readtable('confusion.xlsx',opts)
dpb
dpb il 18 Giu 2021
Huh. That's definitely a PIT proverbial appendage; that would seem worthy of an enhancement request/almost bug report.
I even resaved the spreadsheet after converting the data format storage to numeric instead of general and it still couldn't recognize it. That's just not nice for a scientific/engineering toolset.

Accedi per commentare.

Risposte (1)

Kiran Felix Robert
Kiran Felix Robert il 21 Giu 2021
Hi Kathryn,
In your excel file - 'confusion.xlsx', I observed that the numeric data was stored as text data.
There was also a warning/error shown in the excel.
After converting the cells to store data as a number/in numeric format (by using Convert to Number option in Excel), readtable reads it as a numeric value (by default).
Refer the readtable documentation as it mentions that the appropriate data-type of the first cell is used for the entire column, since the cell format was set as text data, MATLAB reads the entire column as text (string) data.
  2 Commenti
dpb
dpb il 21 Giu 2021
As noted, I resaved the spreadsheet with the format as NUMERIC and got the same result...what is different in that case...
Kathryn Vernon
Kathryn Vernon il 21 Giu 2021
I had assumed that some editing within excel would fix Matlab reading the data as characters. That is a solution, but not a feasible one since I have 600+ files to do this process with.
Thanks for your answer, but I figured out my question(see my comment)

Accedi per commentare.

Prodotti


Release

R2020b

Community Treasure Hunt

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

Start Hunting!

Translated by