Converting .txt file to .xls format with Matlab as the interface.

previously, I converted a text file containing data as follows;

"06-11-2013","13:01:00","00","00","00","1","1","0","5","0","0","0","0","6"

this has delimiters = ' "," '

i used import data with delimiter ',' (comma) and used cell2csv() function then wrote it back to .xls and it worked perfectly. But now, I have a different issue. I have a .txt file with data as follows;

15:28:43#00348#048.1#053.3#00325#046.2#051.3#00000#000.0#000.0 which has only '#' as the delimiter. I tried using the same method. But i get an error.

??? Cell contents reference from a non-cell array object.

Error in ==> cell2csv at 43 var = eval(['cellArray{z,s}']);

Error in ==> newall_Cell at 20 cell2csv(fileName, cellArray, separator, excelYear, decimal)

------------------------------------------------ the code i used is as follows ------------------------------------------------- filename1 = 'ABC.txt';

delimiterIn1 = '#';

A = importdata(filename1,delimiterIn1);

fileName = 'XYZ.csv';

cellArray = A;

separator = ',';

excelYear = '2003';

decimal = '.';

cell2csv(fileName, cellArray, separator, excelYear, decimal)

              % the cell2csv function is long so i am not giving it here.
              % by Sylvain Fiedler, KA, 2004
              % updated by Sylvain Fiedler, Metz, 06
              % fixed the logical-bug, Kaiserslautern, 06/2008, S.Fiedler
              % added the choice of decimal separator, 11/2010, S.Fiedler

C = xlsread('XYZ.csv');

xlswrite('ABC.xls',C);

------------------------------------------------------

Need help on this please. Thanks

[Cedric]

Why using CSV as an intermediary step towards XLS(X), when you could export directly as XLS(X)? Also, why not importing the text file directly with Excel, setting the appropriate delimiter in the import wizard?

[Kamalanathan Ganesan]

I used the .csv for my previous file conversion which had (",")as a delimiter. I tried to use importdata() the .txt file with delimiter ',' and xlswrite it to .xls file. But this didn't work . it gave me the entire row in a single cell without separating the delimiter. That is why i used .csv as an intermediary step. it converted the .txt file to a single delimiter (comma) file and then to .xls

I am using matlab because I have to convert more than 200 files everyday and cant use import wizard from excel for that. would be very tedious. So i wanted to feed the .txt file into matlab and convert them into .xls.

But i knew there was a simpler way to convert the second file with '#' as the delimiter and i was just complicating stuff. Your code works exactly as i want it to. Thanks Wannaz

------------------------------------------------------------------- Also I would like to automate this for the other .txt files as well so that i need not go to the matlab code and give in names for each of the text files every time. Is there a way to do that? my file names are like follows,

20130601.txt

20130602.txt .. and so on for every month. All the files are in a single folder.

Thanks in advance.

[Cedric]

You're welcome. Please see the edit below.

[Ara]

Hi Cedric,

Can you please answer my question, too?

Answer 1

[Cedric]

I am illustrating my comment above with the following example:

Assume the text file contains

15:28:43#00348#048.1#053.3#00325#046.2#051.3#00000#000.0#000.0
16:28:43#00348#048.1#053.3#00325#047.2#051.4#00000#001.0#000.1
16:38:43#00348#048.1#053.3#00325#048.2#051.5#00000#002.0#000.2
17:28:43#00348#048.1#053.3#00325#049.2#051.6#00000#003.0#000.3
18:21:43#00348#048.1#053.3#00325#050.2#051.7#00000#004.0#000.4
19:27:43#00348#048.1#053.3#00325#051.2#051.8#00000#005.0#000.5
20:28:43#00348#048.1#053.3#00325#052.2#051.9#00000#006.0#000.6
21:28:43#00348#048.1#053.3#00325#053.2#052.0#00000#007.0#000.7

Reading it and exporting it as an Excel file can be done as follows:

 buffer = fileread('ABC.txt') ;
 data   = textscan(buffer, '%s %f %f %f %f %f %f %f %f %f', 'delimiter', '#') ;
 data   = [data{1}, num2cell([data{2:end}])] ;
 xlswrite('ABC.xlsx', data) ;

EDIT: to answer your second question:

 path    = 'C:/Users/ganesan/Documents/Data' ;       % To tailor to your system.
 dir_txt = dir(fullfile(path, '*.txt')) ;
 nFiles  = length(dir_txt) ;

 for dId = 1 : nFiles

    % - Define source and destination file names and verbose.
    srcFile = dir_txt(dId).name ;
    [~,baseName] = fileparts(srcFile) ;
    destFile = strcat(baseName, '.xlsx') ;
    fprintf('Processing file %d/%d : %s -> %s\n', dId, nFiles, srcFile, ...
            destFile) ;

    % - Read/process source file.
    data = fileread(fullfile(path, srcFile)) ;
    data = textscan(data, '%s %f %f %f %f %f %f %f %f %f', 'delimiter', '#') ;
    data = [data{1}, num2cell([data{2:end}])] ;

    % - Export destination file.
    xlswrite(fullfile(path, destFile), data) ;
 end

[Kamalanathan Ganesan]

I used it to store in the same excel file, but in different sheet. Worked perfectly. Thanks.