Numeric column not read as string with readtable on XLS file
40 次查看(过去 30 天)
显示 更早的评论
I have an XLS data file with a column that looks like a number, but it is actually a unique string. I cannot control the design of this file type.
I am explicitly defining that the column (by variable name) is a string after generating a readtable opts sctruct, with either detectImportOptions or sheetImportOptions. The imported data must be passed through a sprintf/compose because the result is unworkable.
The following are some unique strings directly copied from my XLS file.
9.26433554
5.28131573
5.28131575
5.28131574
5.28131785
5.28131784
7.21999049
7.21999624
7.21999662
7.22000179
7.22001452
7.22004156
7.22004157
Yet, when I read in the XLS file with readtable, again explicitly stating the column is a string, it returns this:
"9.26434"
"5.28132"
"5.28132"
"5.28132"
"5.28132"
"5.28132"
"7.21999"
"7.22"
"7.22"
"7.22"
"7.22001"
"7.22004"
"7.22004"
Notice that several of the numbers are no longer unique. This is incredibly frustrating, because I generated a similar script to work on another matched dataset, and it reads those values correctly, though they are XLSX.
I've tried compose the doubles into strings with the correct width but it isn't matching about 5% of the data that is there. I've tried generating a script with uiopen, and set UseExcel to true and false; nothing is working.
I'm running MATLAB R2021b in Windows 10 with Office 365 (16.*).
回答(2 个)
Walter Roberson
2021-12-1
Excel stores "general numbers" in .xls files as binary floating point ("XNUM"), not as text.
You can open the spreadsheet and change the format; https://support.microsoft.com/en-us/office/format-numbers-as-text-583160db-936b-4e52-bdff-6f1863518ba4
Probably you could also use ActiveX to tell Excel to change the format.
Because the value is internally stored in floating point, when you tell MATLB to read it as string, it does string() on the value. Unfortunately there does not appear to be any way to control the precision when doing that :(
The situation is different, at least in theory, for .xlsx files: .xlsx files do not store values in binary floating point, and instead store numbers as text in a .XML file. With .xlsx files, it is at least possible in theory to unzip the .xlsx file and open the .xml and parse out the text of the number. Older versions of MATLAB did exactly that: from roughly R2013b to R2018a, readtable() for xlsx files was implemented as MATLAB code that pulled out the .xml text and used regexp() to pull out the pieces. But that is theory; in practice, for .xlsx files you get the same difficulty that forcing string output for what was coded as a numeric field results in string() being applied with no control over the number of digits.
This difficulty does not exist for fields stored as text -- though historically there have been releases where leading zeros were dropped from text fields that happened to look like numbers.
0 个评论
Star Strider
2021-12-1
The readtable function has no problems with that variable (that appears to be ‘History.UUT_ID’) and reads it as a double. I imported it here, not offline on my computer, however I suspect it will import the same way, since I also have R2021b there. The ‘History.SENSOR_SN’ variable was imported as a cell array of strings, however that is easily converted using the str2double function —
T1 = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/819254/EData0001.xls', 'VariableNamingRule','preserve')
T1.('History.SENSOR_SN') = str2double(T1.('History.SENSOR_SN'))
Col2 = T1.('History.SENSOR_SN');
Col4 = T1.('History.UUT_ID');
whos Col2 Col4
Try that approach, and be certain that all the latest updates are installed. The readmatrix function likely also works with the same conversion (I didn’t experiment with it). I prefer the extra information readtable provides.
.
3 个评论
Walter Roberson
2021-12-1
Comparing floating point numbers for equality was always going to be problematic. You can't count on systems to retain last-digit accuracy when handling numbers represented as text.
For example MATLAB's "format long g" skips outputing the last digit that would be needed to definitely resolve to the last bit, so about 1 time in 3 a value output using "format long g" format will not read in exactly the same as the original number.
Star Strider
2021-12-1
As Walter notes, exact equality is likely not an option. Tolerances are usually required, so depending on the application, ismembertol and uniquetol take this into account, and it is usually possible to include a tolerance in other instances, depending on the application. The discussion and examples in the documentation section on Group Scattered Data Using a Tolerance is particularly informative in that respect.
.
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!