Keep leading zeros on import from excel?

13 次查看（过去 30 天）
C G
C G 2018-4-4
评论： Walter Roberson 2018-4-5
I have 5 columns in excel that I would like to read in (and 48216 rows). In excel, I have formatted the cells to display a leading zero, so 09 instead of 9.
I am trying to read these into MATLAB and when I check a subset of these values, the leading zero is dropped. Is there a way to keep the leading 0?
Here is my code so far:
num= xlsread('Book1.xls',xlRangehour) % Only lists the hour column here, eventually will need all of them.
xlRangeyear = 'C1:c4'; %48216 rows in that excel file, for simplicity, only the first four are here.
xlRangemonth = 'd1:d4'; % the month column
xlRangeday= 'e1:e4'; % the day column
xlRangehour = 'f1:f4'; % the hour column
[num,txt,raw] = xlsread('Book1.xls') ; %Not really sure this line is correct.
subsetyear = xlsread(HYSPLITDATE,sheet,xlRangeyear)
subsetmonth = xlsread(HYSPLITDATE,sheet,xlRangemonth)
subsetday = xlsread(HYSPLITDATE,sheet,xlRangeday)
subsethour = xlsread(HYSPLITDATE,sheet,xlRangehour)
  4 个评论
显示 2更早的评论
Stephen23
Stephen23 2018-4-4
编辑：Stephen23 2018-4-4
"I am trying to read these into MATLAB and when I check a subset of these values, the leading zero is dropped. Is there a way to keep the leading 0?"
It depends. Numeric classes do not store any formatting information whatsoever, so they certainly do not store anything like a "leading zero". If you import the data as char vectors then you could keep the data looking exactly as it does in Excel. But of course char vectors are essentially useless for doing any numeric processing or operations.
So, which do you want: char vectors with leading zeros (but useless for numeric operations), or numeric values which you can use for calculations (but have no formatting information whatsoever)?
https://www.mathworks.com/help/matlab/numeric-types.html
https://www.mathworks.com/help/matlab/matlab_prog/fundamental-matlab-classes.html
C G
C G 2018-4-4
At this time, character vectors might be fine. They are meant to tell a different program a date or hour value. For example, hour 6 on Dec 9, 2000 would read 00 12 09 06. They aren't really meant for any calculations, just a when.
I will try to import them as characters and see if that works.

请先登录，再进行评论。

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

回答（3 个）

Stephen23
Stephen23 2018-4-4
Have a look at the other outputs, one of them might do what you want:
[num,txt,raw] = xlsread(..)
Walter Roberson
Walter Roberson 2018-4-4
No, xlsread() will always remove any leading zeros on anything that resembles a number. You might have a faint hope if you used the ActiveX interface directly, but I would not count on it.
Leading 0 is a presentation matter, not a numeric matter. The likely solution to your problem would be to use the numeric fields as either columns of the first parameter for datestr inputs, or as individual datetime inputs, in order to get a time string or a datetime object that you could set appropriate Format for.
C G
C G 2018-4-5
I have 5 columns in excel that I would like to read in (and 48216 rows). In excel, I have formatted the cells to display a leading zero, so 09 instead of 9.
I am trying to read these into MATLAB and when I check a subset of these values, the leading zero is dropped. Is there a way to keep the leading 0?
A = xlsread(filename,sheet,xlRange1); %Range should look similar to this [12 12 09 09]
B = xlsread(filename,sheet,xlRange2); %Instead, when it printed it would look like [12 12 9 9]
C = xlsread(filename,sheet,xlRange3);
D = xlsread(filename,sheet,xlRange4);
F = {A,B,C,D};
for k = 1:numel(F)
[fid,msg] = fopen(sprintf('file%d.txt',k),'wt');
assert(fid>=3,msg)
fprintf(fid,'%02.0f %02.0f %02.0f %02.0f',F{k}(1:4));
P={'';'3';'-71.16688 111.36653 10.0';'-71.166889 111.366531 2500.0';'-71.166889 111.366531 ';'} ;
fprintf(fid,'%s\n',P{:});
fclose(fid);
end
Yes, well sort of. It works for my code because I need the zeros in the .txt file I am creating. To do it, all I did was add a 0 to each %2.0f (line 9), so that it now reads %02.0f. This now makes sure that all of the numbers have two digits.
Instead of reading [12 12 9 9] it now reads [12 12 09 09].
  1 个评论
Walter Roberson
Walter Roberson 2018-4-5
As I indicated earlier, xlsread() will always drop the leading zero of any field that appears to be a number.
When it reads in data from Excel that is formatted as char, it does a str2double() and if the result is nan then it leaves it as char and otherwise it substitutes the double.
When it reads in double from a text-based .xml file itself, it does the equivalent.
There is no realistic hope of getting xlsread() to preserve leading 0 on something that appears to be a number in form.
Your code using fprintf is working on presentation not on representation. Using a %02d or %02.0f format specifier for presentation is fine.

请先登录，再进行评论。

类别

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

标签

Community Treasure Hunt

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

Start Hunting!

Translated by