MATLAB Answers

C G
0

Keep leading zeros on import from excel?

Asked by C G
on 4 Apr 2018
Latest activity Commented on by Walter Roberson
on 5 Apr 2018
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 Comments

Show 1 older comment
KSSV
on 4 Apr 2018
Check raw, it will read the data as given in excel....with leading zero too....but how it is going to matter?
"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)?
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.

Sign in to comment.

3 Answers

Answer by Stephen Cobeldick on 4 Apr 2018

Have a look at the other outputs, one of them might do what you want:
[num,txt,raw] = xlsread(..)

  0 Comments

Sign in to comment.


Answer by Walter Roberson
on 4 Apr 2018

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 Comments

Sign in to comment.


Answer by C G
on 5 Apr 2018

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 Comment

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.

Sign in to comment.