How do a read a text file as fixed width columns as in Excel?

3 views (last 30 days)
Hello,
I have a hydrologic model that outputs hourly data in a strange but fixed width format. I can use Excel to get the format that I need but if I could skip that step and do it in matlab it would be useful.
Below is an example of the data and for some reason this matlab question window screws up the spacing so I will give the spacing for the 27 desired columns. Note: The spacings need to count white space
Column widths: 4,2,3,2,5,2,5,2,5,2,5,2,5,2,5,2,5,2,5,2,5,2,5,2,5,2,5
1989 1 2318 119 120 121 122 123 124 1
1989 1 24 1 1 2 1 3 1 4 1 5 1 6 1 7 1 8 1 9 110 111 112 1
1989 1 2413 114 115 116 117 118 119 120 121 122 123 124 1
Here is how the data should be separated using vertical lineson the second example line:
1989| 1| 24| 1| 1| 2| 1| 3| 1| 4| 1| 5| 1| 6| 1| 7| 1| 8| 1| 9| 1|10| 1|11| 1|12| 1|
However, when i try fscanf using %c for the specifier I just get a mess.
fid =fopen('Webster_Soil.SRO','r');
a = fscanf(fid,'%4c %2c %3c %2c %5c %2c %5c %2c %5c %2c %5c %2c %5c %2c %5c %2c %5c %2c %5c %2c %5c %2c %5c %2c %5c %2c %5c\n');
fclose all;
Does anyone have any solutions? Thanks in advance for the help,
Brandon Sloan

Answers (2)

Leah
Leah on 19 Mar 2013
The sum of all of your spacing is 93, so you would need 93 characters per entry. The data above has 37, 57, & 57 characters in each string. I think you might be missing some white space or you spacing array is wrong. I did something like this.
S={'1989 1 2318 119 120 121 122 123 124 1';...
'1989 1 24 1 1 2 1 3 1 4 1 5 1 6 1 7 1 8 1 9 110 111 112 1';...
'1989 1 2413 114 115 116 117 118 119 120 121 122 123 124 1'};
last=1;
spacing=[4 2 3 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2];
for i=2:length(S)
Schar=char(S(i));
for j=1:length(spacing)
if j==1
Snew(i,j)=str2double(Schar(last:last+spacing(j)));
else
Snew(i,j)=str2double(Schar(last:last+spacing(j)-1));
end
last=last+spacing(j);
end
last=1;
end
  3 Comments
Brandon Sloan
Brandon Sloan on 22 Mar 2013
Nevermind I got it to work with some playing with the numbering. Thank you very much for the help! The code that works is:
fid=fopen('Webster_Soil10m.SRO');
g = textscan(fid,'%s','Delimiter','\n');
fclose all;
S = g{1,1};
Snew = zeros(length(S),27);
last=1;
spacing=[4 2 3 2 5 2 5 2 5 2 5 2 5 2 5 2 5 2 5 2 5 2 5 2 5 2 5];
for i=1:length(S);
Schar=(S{i,1});
x = (length(Schar)+1.5)/3.5;
for j=1:x
Snew(i,j)=str2double(Schar(last:last+spacing(j)-1));
last=last+spacing(j);
end
last=1;
end
Cedric I tried your code but it kept giving me an error when I used the reshape function because the total number of elements was not divisible by the number of elements in a row. Thanks anyway for the help.
Brandon
Cedric
Cedric on 22 Mar 2013
Hi Brandon, sorry, I made a mistake; please, see the EDIT in my post.

Sign in to comment.


Cedric
Cedric on 22 Mar 2013
Edited: Cedric on 22 Mar 2013
You could go for something like the following:
colw = [4,2,3,2,5,2,5,2,5,2,5,2,5,2,5,2,5,2,5,2,5,2,5,2,5,2,5] ;
buffer = fileread('Webster_Soil.SRO') ;
buffer(buffer<' ') = [] ; % Remove \n,\r, etc. [EDITED]
buffer = reshape(buffer, sum(colw), []).' ;
data = str2double(mat2cell(buffer, ones(size(buffer,1),1), colw)) ;
  2 Comments
Brandon Sloan
Brandon Sloan on 22 Mar 2013
Hey Cedric, when I do that I keep getting this error:
??? Error using ==> reshape
Product of known dimensions, 93, not divisible into total number of elements, 244.
Error in ==> fd at 4
buffer = reshape(buffer, sum(colw), []).' ;
It is the same error that I got before, and I am not sure why. My other code does works so it is not too big of a deal. Thanks though
Brandon
Cedric
Cedric on 22 Mar 2013
Edited: Cedric on 22 Mar 2013
Well, it means that not all the lines in the file are 93 characters long (which is the sum of the padding that you gave.
This codes assumes that the whole file is padded according to the widths that you defined. It reads the whole content in one shot, eliminates all line feeds, carriage returns, etc, which leaves a vector of valid characters (whose length is assumed to be 93 * the number of lines of the file has the structure defined above). The vector is then reshaped in an array of size n x 93, where n is the number of lines (operation that fails if the length of the vector is not a multiple of 93). This array is then split in strings according to your padding (which outputs a cell array) and then converted to an array of double.

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!