Read in numbers as text from excel

34 次查看（过去 30 天）
Blake
Blake 2021-8-23
评论： Stephen23 2021-8-24
I have a spreadsheet with a mix of numbers and text. However, I need to read the numbers in as text to preserve the exact formatting used in the excel spreadsheet.The readtable(), readcell(), and xlsread() functions all automatically convert the numbers to a double format, even if it is formatted as text in the source spreadsheet. As a result, formatting information (e.g. scientific notation vs decimal) is lost.
I tried using detectImportOptions, then setting the VariableType, but they're still getting converted to double:
filename = 'Test_File.xlsx';
opts = detectImportOptions(filename);
opts = setvartype(opts,'char'); % Set all variable types to char
raw_data = readcell(filename,opts); % Numbers here are still converted to double
Is there a way to keep all excel data as text while reading it in to Matlab? Thanks for your help.
  3 个评论
显示 1更早的评论
Blake
Blake 2021-8-24
I do have excel installed, and I wish I could avoid Excel in this case! I've attached the example. After having expanded my example, I see that this is partially an excel issue - one of the numbers is successfully imported as text, but two others are not. They are still being treated by excel as numbers, despite having the text format selected. B2 in this example is the particular problem. Though I suppose by nature of it not being treated as text, we can assume it's a decimal. I can work around that, though it would be a pain.
I assumed I was doing something simple wrong, since the import options seem to give you the choice of forcing it to text. Instead they don't do anything, as far as I can tell.
Stephen23
Stephen23 2021-8-24
"I do have excel installed, and I wish I could avoid Excel in this case!"
If you are using XLSREAD then use BASIC mode, as my last comment shows.

请先登录，再进行评论。

请先登录，再回答此问题。

回答（1 个）

Wan Ji
Wan Ji 2021-8-24
编辑：Wan Ji 2021-8-24
IF in excel, a number with high precision is not stored with text format, it cannot be read by readcell with string format for high precision purpose but only be read with double precision, actually excel does the same way with matlab. So why do you force matlab to do that?
When you store a high precision number with text format in excel. then, matlab can read it as a string.
Usually when we write a high precision number in excel, we need to write '3.1415926535897932384626433832795 rather than directly write 3.1415926535897932384626433832795. Because the latter will lose precision in excel.
  2 个评论
Blake
Blake 2021-8-24
The number is stored in a text format in excel. Matlab still converts it into a number, despite the steps outlined in the question. It doesn't have the lead apostrophe, but the fields are formatted as text.
Wan Ji
Wan Ji 2021-8-24
you can use istext function in the excel to detect whether the fields are formatted as text as you said

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

产品


版本

R2020b

Community Treasure Hunt

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

Start Hunting!

Translated by