Read in numbers as text from excel

Question

Blake 2021-8-23

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1439329-read-in-numbers-as-text-from-excel

评论： Stephen23 2021-8-24

Test_File.xlsx

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更早的评论隐藏 1更早的评论

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 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.

请先登录，再进行评论。

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

Answer 1

Wan Ji 2021-8-24

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1439329-read-in-numbers-as-text-from-excel#answer_773179

编辑：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 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 2021-8-24