Import a thousand of AscII files, combine them in one XYZ text file and export them again as one text file?

HI,

I have a thousands of ASCII files which start with the name "CDR_19830101z.asc" and end with the name "CDR_20160101z.asc". I am trying to call them all in Matlab, and export them as one combined XYZ text or excel file.

These files are averages rainfall per day, of a specific area. The X and Y value are the same of all the files. The only value which changes is the Z value which consider the the average rainfall per day. So I am trying to build a graph between the time and rainfall of each square within this study area.

The file CDR_19830101z.asc format is:

ncols     6
nrows    4
xllcorner 41.500
yllcorner 35.500
cellsize 0.25
NODATA_value -99
0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00 0.00

The last file "CDR_20160101z.asc" format:

ncols     6
nrows    4
xllcorner 41.500
yllcorner 35.500
cellsize 0.25
NODATA_value -99
1.19 2.14 0.89 2.86 3.18 5.77
1.24 4.41 1.39 2.42 2.87 4.51
1.27 1.82 1.29 1.60 1.24 2.88
1.37 2.43 3.26 2.44 2.80 2.13

I appreciate your help and time.

Thanks, Majid

...[cleaned up/formatted data--dpb. NB: Use the {}Code button to format such lines in future]...

[dpb]

Are all dates present or are there missing files? IOW, do you need to store the date info in the file or can it be inferred from the position knowing first entry date/time?

Just to be certain we know what we're talking about, I presume the 24 entries are annual totals for a given geographical subsection within the total area as given by the LL corner coordinates?

[Majid Mohamod]

There is no missing in the data. Actually I found the script which load the Raster ASCII and convert it to XYZ.csv file. But I want to modify this script to be as the following:

Apply this script on different files (12000 files) and auto save with any name (It's not important the name for now) with format csv.

I was thinking that if we can use the loop fucntion in MatLab and apply this script on each file one after one!

The script which I use is:

Answer 1

[Walter Roberson]

projectdir = uigetdir('Select a directory');
dinfo = dir( fullfile(projectdir, '*.asc') );
filenames = fullfile( projectdir, {dinfo.name} );

for K = 1 : length(filenames)
  this_file = filenames{K};

    now convert the one file named by this_file

end

[Majid Mohamod]

The code work very well, but the problem is that I have to select the file and save the output. There is a way to select and save the output automatically? I appreciate your help and time.

I merged your code with the script which I have as shown bellow:

projectdir = uigetdir('Select a directory');
dinfo = dir( fullfile(projectdir, '*.asc') );
filenames = fullfile( projectdir, {dinfo.name} );

for K = 1 : length(filenames)
  this_file = filenames{K};

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%                       RASTER TO XYZ
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%                 Produced by Joseph Wheaton 
%                   Last Updated: 18 September 2007                 
%                                                                %
%                                                                %
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
% 
%
% This script reads in an ASCII raster file and then outputs a CSV file in
% XYZ format with a point (row) for each grid cell. It is slow because
% most of the IO part of the code can not be vectorized, but it does
% the trick.

%------Read in Data -------------------------------------------------

% Prompts User to select the most recent DEM file name
[filename, pathname]=uigetfile('*.asc',strcat('Load the ASCII raster you wish to convert.'));    
filename_NewDEM=[pathname filename];

fid=fopen(filename_NewDEM,'r');      % opens file to fid in read only mode
dum1=fscanf(fid,'%s',1);            % assigns first part of header (ncols) to dummy variable dum1 (%s is string notation)
nx=fscanf(fid,'%u',1);              % stores actual number of collumns to nx (%u is decimal notation)
dum2=fscanf(fid,'%s',1);            % assigns second line header info (nrows) to dummy variable dum2
ny=fscanf(fid,'%u',1);              % stores actual number of rows to ny
dum3=fscanf(fid,'%s',1);            % assigns third line header info (xllcorner) to dummy variable dum3
xll=fscanf(fid,'%f',1);             % stores actual lower left x coordinate corner to xll (%f is fixed point notation)
dum4=fscanf(fid,'%s',1);            % assigns fourth line header info (yllcorner) to dummy variable dum4
yll=fscanf(fid,'%f',1);             % stores actual lower left y coordinate corner to yll
dum5=fscanf(fid,'%s',1);            % assigns fifth line header info (cellsize) to dummy variable dum5
lx=fscanf(fid,'%f',1);              % stores cell size in lx
dum6=fscanf(fid,'%s',1);            % assigns sixth line header info (nodata) to dummy variable dum6
nodata=fscanf(fid,'%f',1);          % stores the no data ARC tag to nodata
Z=fscanf(fid,'%f',[nx,ny]);      % stores all of the cell data in a double array dimensioned according to nx and ny
fclose(fid);                        % closes the file
fprintf('Done reading ARC data.\n');
fprintf('\n')

%------Make the X,Y,Z Arrays -------------------------------------------------

xleft = xll + (0.5*lx);
ytop =  yll + (ny*lx) - (0.5*lx);
xPos = xleft;
yPos = ytop;

fprintf('Processing points... be patient. \n');
for i=1:ny;              % Begin main gridcell loop (rows)
    for j=1:nx;          % Begin gridcell loop (collumns)
        if ((Z(j,i) == nodata) | (Z(j,i) == 0));
            X(j,i) = nodata;  
            Y(j,i) = nodata;  
        else    
            X(j,i) = xPos;  
            Y(j,i) = yPos;  
        end
        % Advance for next collmn
        xPos = xPos + lx;
    end                 % End gridcell loop (collumns)
    % At end of row reset xPos
    xPos = xleft;
    % and advance yPos to next row
    yPos = yPos - lx;
end  
%------Write the Output File ---------------------

% SORT OUT FILE NAME AND PATH:

[filename,pathname]=uiputfile('*.csv','Choose a name to save the XYZ file to');    % Select final file name
csv_file_name=[pathname filename '.csv'];   

fprintf('Creating file... be patient. \n');
%Write DoD data to a temporary file in ARC format
fid1= fopen(csv_file_name, 'w');
% Write Header
fprintf(fid1,'Easting,Northing,Elevation\n');
    i=0;
    j=0;
    for i=1:ny;              % Begin main gridcell loop (rows)
        for j=1:nx;          % Begin gridcell loop (collumns)
            if (Z(j,i) ~= nodata);
                fprintf(fid1, '%12.3f,%12.3f,%6.3f\n', X(j,i), Y(j,i), Z(j,i));  
            end
        end                 % End gridcell loop (collumns)
    end                     % End main gridcell loop (rows)
fclose(fid1);

fprintf('It worked... imagine that. Done writing %s', csv_file_name);
% DONE
end

[Walter Roberson]

change

[filename, pathname]=uigetfile('*.asc',strcat('Load the ASCII raster you wish to convert.'));    
filename_NewDEM=[pathname filename];

to

filename_NewDEM = this_file;

[Majid Mohamod]

It works very well. I have one obstacle to finish this script. I need to add auto save to this script. Please, may you help me to finalize this step? I really appreciate your help.

[Walter Roberson]

Change

[filename,pathname]=uiputfile('*.csv','Choose a name to save the XYZ file to');    % Select final file name
csv_file_name=[pathname filename '.csv'];

to

[pathname, basename, ext] = fileparts(filename_NewDEM);
csv_file_name = fullfile(pathname, [basename, '.csv']);

[Majid Mohamod]

Man.. it works wonderfully. You did a huge favor for me. Thank you so much.

Best, Majid

[Majid Mohamod]

Please, there is a small problem it's just showed up. When I try to convert the file with the following format:

ncols     11
nrows    15
xllcorner 41.000
yllcorner 33.500
cellsize 0.25
NODATA_value -99
-99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00
-99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00
-99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00
-99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00
-99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00
-99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00
-99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00
-99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00
-99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00
-99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00
-99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00
-99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00
-99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00
-99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00
-99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00 -99.00

After convert these files with -99 value, always the output is empty csv file with header only. I hope if it possible to fix this problem!

Thanks in advance Majid

[Walter Roberson]

That would be correct output for the code. The code is designed to only output the locations at which there is valid nonzero data, and since all of the data is marked as missing there is nothing to output. You would get the same result if all of the data were 0

[Majid Mohamod]

Is there any way to output them as -99 instead of leave the file empty. These data, will be used to simulate the surface runoff by using ArcSWAT. ArcSWAt won't be able define the empty files, but it's possible to read the -99 value as missing data. Please, do you have any suggestion in this regard?

Thanks, Majid

[Walter Roberson]

Change

        if ((Z(j,i) == nodata) | (Z(j,i) == 0));
            X(j,i) = nodata;  
            Y(j,i) = nodata;  
        else    
            X(j,i) = xPos;  
            Y(j,i) = yPos;  
        end

to

        if false % ((Z(j,i) == nodata) | (Z(j,i) == 0));
            X(j,i) = nodata;  
            Y(j,i) = nodata;  
        else    
            X(j,i) = xPos;  
            Y(j,i) = yPos;  
        end

change

            if (Z(j,i) ~= nodata);

to

            if true %(Z(j,i) ~= nodata);

[Majid Mohamod]

Again, I am really grateful. It works amazingly. :)

Thanks, Majid

[Majid Mohamod]

Mr. Roberson, You helped me too much. There is one thing left and then I will finish the Data processing. It's really up to you weather will help me or not. Because, I appreciate all what you've done for me so far.

I problem is: I have excel file with multiple columns. I want to export each column to separated text file. So, I've 1650 columns and the output should be 1650 text file. There is anyway to do it in Matlab or other software.

Thanks, Majid

[Walter Roberson]

data = readtable('YourFileName.xls');

varnames = data.Properties.VariableNames;

for col = 1 : size(data, 2)
   thisvar = varnames{col};
   filename = sprintf('split_%s.txt', thisvar);
   writetable( data(:,col), filename );
end

This will output as with a prefix of 'split_' then followed by the variable name (extracted from the xls file).

Note: this will not necessarily write out exactly the same header for the columns as were in the file. Also, columns that were in date/time format might have their output format changed.

[Majid Mohamod]

Matlab gives me an error, says that the file not found. I attached a screenshot of it. I posted the code inside the Matlab and then I saved it in the same folder where the excel file excises.

[Walter Roberson]

Your file name is D.xlsx but you ask to open D.xls

[Majid Mohamod]

It works great.. but the files output have random headers (As it's shown in the attached screenshot). There is anyway to output without headers?

Thanks,

[Majid Mohamod]

Hello, please, could you help me with my question?

Thanks, Majid

[Walter Roberson]

     writetable( data(:,col), filename, 'WriteVariableNames', false );

[Majid Mohamod]

I appreciate it again.. The script is done now because your help!

Best, Majid

[Hannah Bartlett]

Hi,

This works great for me as I also need to split the columns into seaparate files. However, I would like to keep the first column as well each time as I require the labels. How can I do this?

Thanks

[Walter Roberson]

 writetable( data(:,[1 col]), filename );

[Hannah Bartlett]

THANK YOU! I couldn't work out where to put the 1!