I have included a picture of the excel file, the highlighted cell has 00. I would like to keep this as 00.
Keep leading zeros on import from excel?
조회 수: 22 (최근 30일)
이전 댓글 표시
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
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)?
답변 (3개)
Stephen23
2018년 4월 4일
Have a look at the other outputs, one of them might do what you want:
[num,txt,raw] = xlsread(..)
댓글 수: 0
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.
댓글 수: 0
C G
2018년 4월 5일
댓글 수: 1
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!