Keep leading zeros on import from excel?

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)

[C G]

I have included a picture of the excel file, the highlighted cell has 00. I would like to keep this as 00.

[KSSV]

Check raw, it will read the data as given in excel....with leading zero too....but how it is going to matter?

[Stephen23]

"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]

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.

Answer 1

[Stephen23]

Have a look at the other outputs, one of them might do what you want:

[num,txt,raw] = xlsread(..)

Answer 2

[Walter Roberson]

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.

Answer 3

[C G]

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

[Walter Roberson]

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.