xlsx processing

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

xlsx processing

Octave - General mailing list
Hi,

I am trying to process various data and would like to automate the
processing. I have tried several things for days without finding success. I
appreciate it if someone can help me.

I have to read a .xlsx file with the following structure

A1 A2 A3 A4 .... AN
B1 B2 B3 B4 ... BN
C1 C2 C3C4 ... CN
X11 X12 X13 X14..X1N
...
...
XM1 XM2 XM3 XM4 ... XMN


the idea of the script would be to read the table and generate a file for
each column to process it later. Since the first three rows (Ai, Bi, Ci)
have information on how to process the data, what I need is for each file to
be saved with the following code Ai_Bi_Ci.txt
Each file would contain the data corresponding to a column (for example X11
... XM1).

How can it be implemented?

Thanks



--
Sent from: https://octave.1599824.n4.nabble.com/Octave-General-f1599825.html


Reply | Threaded
Open this post in threaded view
|

Re: xlsx processing

Andreas Weber-6
Am 16.03.20 um 01:58 schrieb mrodrig via Help-octave:
> I am trying to process various data and would like to automate the
> processing. I have tried several things for days without finding success. I
> appreciate it if someone can help me.

What have you done so far? Can you show your current code?
-- Andy


Reply | Threaded
Open this post in threaded view
|

Re: xlsx processing

Octave - General mailing list
I have worked a little more and I found this way to do it but I do not know
if it is the best
data.xlsx <https://octave.1599824.n4.nabble.com/file/t372706/data.xlsx>  
clc
clear all
pkg load io

[data_v, title_v]=xlsread("data.xlsx");
[rows, columns] = size(data_v)
rows
columns
for i=1:columns
    v=data_v(5:326,i);
    FileName=sprintf("%s.txt",[title_v{1,i}, "_", title_v{2,i},
"_",title_v{3,i}])
    save (sprintf (FileName, i), "v")
    i=i+1;
end




--
Sent from: https://octave.1599824.n4.nabble.com/Octave-General-f1599825.html


Reply | Threaded
Open this post in threaded view
|

Re: xlsx processing

Ian McCallion
On Mon, 16 Mar 2020 at 13:20, mrodrig via Help-octave
<[hidden email]> wrote:

>
> I have worked a little more and I found this way to do it but I do not know
> if it is the best
> data.xlsx <https://octave.1599824.n4.nabble.com/file/t372706/data.xlsx>
> clc
> clear all
> pkg load io
>
> [data_v, title_v]=xlsread("data.xlsx");
> [rows, columns] = size(data_v)
> rows
> columns
> for i=1:columns
>     v=data_v(5:326,i);
>     FileName=sprintf("%s.txt",[title_v{1,i}, "_", title_v{2,i},
> "_",title_v{3,i}])
>     save (sprintf (FileName, i), "v")
>     i=i+1;
> end

That looks pretty good to me! Although given that you said there were
three information rows at the head of each column, I'm not sure why
you coded

   v = data_v(5:326,i)

Should it not have been 4:326?

Also you can very conveniently use the "end" keyword inside array indices, so

  v = data_v(5:end,i)

Cheers... Ian


Reply | Threaded
Open this post in threaded view
|

Re: xlsx processing

steph
In reply to this post by Octave - General mailing list

On 2020/03/16 14:10, mrodrig via Help-octave wrote:

> I have worked a little more and I found this way to do it but I do not know
> if it is the best
> data.xlsx <https://octave.1599824.n4.nabble.com/file/t372706/data.xlsx>
> clc
> clear all
> pkg load io
>
> [data_v, title_v]=xlsread("data.xlsx");
> [rows, columns] = size(data_v)
> rows
> columns
> for i=1:columns
>      v=data_v(5:326,i);
>      FileName=sprintf("%s.txt",[title_v{1,i}, "_", title_v{2,i},
> "_",title_v{3,i}])
>      save (sprintf (FileName, i), "v")
>      i=i+1;
> end
>
>
>
>
> --
> Sent from: https://octave.1599824.n4.nabble.com/Octave-General-f1599825.html
>
>
I am not an Octave expert at all but I do read a lot data from xlsx
files, I have included one of my implementations and as you can see I
have the same dataset in a xlsx spreadsheet with different sheets. After
reading I cast the data into the respective place holders. So, what I
see with your data is that the alternating data rows can be problematic,
can't you store A, B, C in different sheets and then read separately?

And I do everything in functions then local variables being duplicated
is not a problem.

And, lastly, I work in Linux, you can do

nano .octaverc

and add the following to the file and save it in you home directory. A
similar procedure is available for Windows.

pkg load io
pkg load statistics
pkg load optim

You can add the packages that you use regularly.

I hope that my contribution will also solicit comments from experts so
that I can improve my own procedures!



function [ T, delta, Freq, G, G1, G2, Defl, Torque ]...
     = fn_read_MasterCurve_Unaged_data( NoFreq, NoTemps, Fname, SheetName )

%Read the creep and recovery experimental data
%
% Data in order:
%    1.  No
%    2.  Temp [�C]
%    3.  Phase angle [�]
%    3.  Freq [Hz]
%    6.  Complex modulus [kPa]
%    4.  Storage modulus [kPa]
%    5.  Loss modulus [kPa]
%    6.  Deflection angle [deg]
%    7.  Torque [uNm]


     datMC = xlsread (Fname,SheetName, [], 'OCT');

     T(1:NoTemps,1:NoFreq) = 0;
     delta(1:NoTemps,1:NoFreq) = 0;
     Freq(1:NoTemps,1:NoFreq) = 0;
     G(1:6,1:NoFreq) = 0;
     G1(1:NoTemps,1:NoFreq) = 0;
     G2(1:NoTemps,1:NoFreq) = 0;
     Defl(1:NoTemps,1:NoFreq) = 0;
     Torque(1:NoTemps,1:NoFreq) = 0;

     L1 = 1;
     L2 = L1+NoFreq-1;
     for iT = 1:NoTemps
         T(iT,1:NoFreq) = datMC(L1:L2,2);
         delta(iT,1:NoFreq) = datMC(L1:L2,3);
         Freq(iT,1:NoFreq) = datMC(L1:L2,4);
         G(iT,1:NoFreq) = datMC(L1:L2,5);
         G1(iT,1:NoFreq) = datMC(L1:L2,6);
         G2(iT,1:NoFreq) = datMC(L1:L2,7);
         Defl(iT,1:NoFreq) = datMC(L1:L2,8);
         Torque(iT,1:NoFreq) = datMC(L1:L2,9);
         %disp(sprintf('iT = %d  L1 = %d  L2 = %d',iT,L1,L2));
         L1 = L2+1;
         L2 = L2+NoFreq;
     end

end




Reply | Threaded
Open this post in threaded view
|

Re: xlsx processing

dev1990
In reply to this post by Octave - General mailing list
Hi guys ,
 i am getting error while reading a xlsx file in Windows10,Octave version
5.2.0


  *io  |  2.4.13 | ...\mingw64\share\octave\packages\io-2.4.13*
     linear-algebra  |   2.2.3 | ...\octave\packages\linear-algebra-2.2.3
               lssa  |   0.1.3 |
...\mingw64\share\octave\packages\lssa-0.1.3
              ltfat  |   2.3.1 | ...\share\octave\packages\ltfat-2.3.1
            mapping  |   1.2.1 | ...\share\octave\packages\mapping-1.2.1
      miscellaneous  |   1.3.0 | ...\octave\packages\miscellaneous-1.3.0
                nan  |   3.4.5 | ...\mingw64\share\octave\packages\nan-3.4.5
             netcdf  |  1.0.12 | ...\share\octave\packages\netcdf-1.0.12
              nurbs  |  1.3.13 | ...\share\octave\packages\nurbs-1.3.13
                ocs  |   0.1.5 | ...\mingw64\share\octave\packages\ocs-0.1.5
             odepkg  |   0.8.5 | ...\share\octave\packages\odepkg-0.8.5
              optim  |   1.6.0 | ...\share\octave\packages\optim-1.6.0
        optiminterp  |   0.3.5 | ...\share\octave\packages\optiminterp-0.3.5
         quaternion  |   2.4.0 | ...\share\octave\packages\quaternion-2.4.0
           queueing  |   1.2.6 | ...\share\octave\packages\queueing-1.2.6
             signal  |   1.4.1 | ...\share\octave\packages\signal-1.4.1
            sockets  |   1.2.0 | ...\share\octave\packages\sockets-1.2.0
          sparsersb  |   1.0.6 | ...\share\octave\packages\sparsersb-1.0.6
            specfun  |   1.1.0 | ...\share\octave\packages\specfun-1.1.0
            splines  |   1.3.3 | ...\share\octave\packages\splines-1.3.3
         statistics  |   1.4.1 | ...\share\octave\packages\statistics-1.4.1
                stk  |   2.6.1 | ...\mingw64\share\octave\packages\stk-2.6.1
            strings  |   1.2.0 | ...\share\octave\packages\strings-1.2.0
             struct  |  1.0.16 | ...\share\octave\packages\struct-1.0.16
             tisean  |   0.2.3 | ...\share\octave\packages\tisean-0.2.3
                tsa  |   4.6.2 | ...\mingw64\share\octave\packages\tsa-4.6.2
              video  |   1.2.4 | ...\share\octave\packages\video-1.2.4
            windows  |   1.4.0 | ...\share\octave\packages\windows-1.4.0
             zeromq  |   1.5.0 | ...\share\octave\packages\zeromq-1.5.0
>> pkg load io
>> A=readtable('Book1.xlsx');
warning: the 'readtable' function is not yet implemented in Octave

Please read <https://www.octave.org/missing.html> to learn how you can
contribute missing functionality.
error: 'readtable' undefined near line 1 column 3
>> filename='Book1.xlsx';
>> A=xlsxread(filename);
*error: 'xlsxread' undefined near line 1 column 3*
>>



--
Sent from: https://octave.1599824.n4.nabble.com/Octave-General-f1599825.html


Reply | Threaded
Open this post in threaded view
|

Re: xlsx processing

nrjank
>> A=readtable('Book1.xlsx');
warning: the 'readtable' function is not yet implemented in Octave


As it says, the error means that the readtable function has not yet been implemented in Octave.

the function xlsread has been implemented. That should be able to read in your data after which you can manually process it.


Reply | Threaded
Open this post in threaded view
|

Re: xlsx processing

dev1990
Got this. It seems xlsread('sample.xlsx') is correct, and we need JDK installed in system. Thanks for prompt response.

Best regards
Debajyoti

On Wed, Mar 18, 2020, 12:36 AM Nicholas Jankowski <[hidden email]> wrote:
>> A=readtable('Book1.xlsx');
warning: the 'readtable' function is not yet implemented in Octave


As it says, the error means that the readtable function has not yet been implemented in Octave.

the function xlsread has been implemented. That should be able to read in your data after which you can manually process it.


Reply | Threaded
Open this post in threaded view
|

Re: xlsx processing

PhilipNienhuis
Please answer below the mail, no top posting.
Read on:


dev1990 wrote
> On Wed, Mar 18, 2020, 12:36 AM Nicholas Jankowski &lt;

> jankowskin@

> &gt;
> wrote:
>
>> >> A=readtable('Book1.xlsx');
>>> warning: the 'readtable' function is not yet implemented in Octave
>>>
>>>
>>> As it says, the error means that the readtable function has not yet been
>> implemented in Octave.
>>
>> the function xlsread has been implemented. That should be able to read in
>> your data after which you can manually process it.
>>
> Got this. It seems xlsread('sample.xlsx') is correct, and we need JDK
> installed in system. Thanks for prompt response.
>
> Best regards
> Debajyoti

No need to install a JDK, not even a JRE is required. Out of the box the io
package should be able to read xlsx and xlsm files w/o any further
dependency software.

Philip



--
Sent from: https://octave.1599824.n4.nabble.com/Octave-General-f1599825.html


Reply | Threaded
Open this post in threaded view
|

Re: xlsx processing

Octave - General mailing list
Thanks very much for yours responses




--
Sent from: https://octave.1599824.n4.nabble.com/Octave-General-f1599825.html


Reply | Threaded
Open this post in threaded view
|

Octave ;the scanfiles function below still finds deleted files and subdir's; what's wrong? Thanks

Octave - General mailing list
In reply to this post by nrjank
function [dwnPath]=scanFiles(initialPath, extensions, fileHandler)

  persistent total = 0;
  persistent depth = 0; depth++;
  initialDir = dir(initialPath);
  persistent fff=cell(100,1);
  printf('dir SCAN: %s ...\n', initialPath);

  for idx = 1 : length(initialDir)
    curDir = initialDir(idx);
    curPath = strcat(curDir.folder, '\', curDir.name);

    if regexp(curDir.name, "(?!(\\.\\.?)).*") * curDir.isdir
    
      
      scanFiles(curPath, extensions, fileHandler);
    elseif regexp(curDir.name, cstrcat("\\.(?i:)(?:", extensions, ")$"))
     
      
      total++;
      file = struct("name",curDir.name,
                     "path",curPath,
                     "parent",regexp(curDir.folder,'[^\\\/]*$','match'),
                     "bytes",curDir.bytes);
      if (!isempty(file.path))
      
                fff(total)=file.path  ;
      endif  
      fileHandler(file);% hier beperkt tot displayen
    endif
  end

  if!(--depth)
    printf('Total number of files:%d\n', total);
    dwnPath=fff;
    total=0;
  else
    dwnPath=fff;
  endif
endfunction


Reply | Threaded
Open this post in threaded view
|

Re: Octave ;the scanfiles function below still finds deleted files and subdir's; what's wrong? Thanks

steph
On 2020/03/18 14:01, Patrick Pintelon via Help-octave wrote:
function [dwnPath]=scanFiles(initialPath, extensions, fileHandler)

  persistent total = 0;
  persistent depth = 0; depth++;
  initialDir = dir(initialPath);
  persistent fff=cell(100,1);
  printf('dir SCAN: %s ...\n', initialPath);

  for idx = 1 : length(initialDir)
    curDir = initialDir(idx);
    curPath = strcat(curDir.folder, '\', curDir.name);

    if regexp(curDir.name, "(?!(\\.\\.?)).*") * curDir.isdir
    
      
      scanFiles(curPath, extensions, fileHandler);
    elseif regexp(curDir.name, cstrcat("\\.(?i:)(?:", extensions, ")$"))
     
      
      total++;
      file = struct("name",curDir.name,
                     "path",curPath,
                     "parent",regexp(curDir.folder,'[^\\\/]*$','match'),
                     "bytes",curDir.bytes);
      if (!isempty(file.path))
      
                fff(total)=file.path  ;
      endif  
      fileHandler(file);% hier beperkt tot displayen
    endif
  end

  if!(--depth)
    printf('Total number of files:%d\n', total);
    dwnPath=fff;
    total=0;
  else
    dwnPath=fff;
  endif
endfunction


    

I would like to test your function, please give an exampple on how you initialize the arguments initialPath, extensions, fileHandler

thanks

--
Steph