How to read numerics as strings with readtable?

312 visualizzazioni (ultimi 30 giorni)
I have a table in spreadsheet (xlsx). One of the column is composed of either a numerical number, or several of them, like the below:
2
3
5,7
9
7
4
2,8,9
5
When I use readtable:
T1 = readtable ('Observations.xlsx');
the results are like this:
2
3
NaN
9
7
4
NaN
5
My question is how do I read the info as a string, so that the groups like '5,7', and '2,8,9' are all captured?
I tried:
T1 = readtable ('Observations.xlsx', 'Format','%s %s %s');
But got the below error:
Invalid value for "Format". Must be "auto" for spreadsheet files.
  1 Commento
Ruger28
Ruger28 il 4 Mar 2020
Modificato: Ruger28 il 4 Mar 2020
Strange, I copied and pasted your values into excel, used readtable, and got this:
T =
7×1 table
x2
_______
'3'
'5,7'
'9'
'7'
'4'
'2,8,9'
'5'
Can you share you .xlsx file? I am assuming it is a number format issue.

Accedi per commentare.

Risposta accettata

Guillaume
Guillaume il 4 Mar 2020
opts = detectImportOptions(yourfile);
opts = setvartype(opts, whichevervariable, 'string'); %or 'char' if you prefer
data = readtable(yourfile, opts)
Where whichevervariable is a char vector, cell array of char vector, string array containing the name(s) of the variable(s) that must be read as string/char or a numeric vector of variable indices.
  2 Commenti
Leon
Leon il 4 Mar 2020
Works well. Exactly what I need!
Thank you so much!
Al in St. Louis
Al in St. Louis il 21 Apr 2021
Given that Mathworks has broken readtable, it's good that they have provided this workaround so that we can keep using our data.

Accedi per commentare.

Più risposte (0)

Categorie

Scopri di più su Structures in Help Center e File Exchange

Prodotti


Release

R2019b

Community Treasure Hunt

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

Start Hunting!

Translated by