The Origin Forum
File Exchange
Try Origin for Free
The Origin Forum
Home | Profile | Register | Active Topics | Members | Search | FAQ | Send File to Tech support
Username:
Password:
Save Password
Forgot your Password? | Admin Options

 All Forums
 Origin Forum
 Origin Forum
 Rearranging imported data (array) from a database
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic Lock Topic Edit Topic Delete Topic New Topic Reply to Topic

deepakdinakaran

India
14 Posts

Posted - 05/02/2018 :  06:59:49 AM  Show Profile  Edit Topic  Reply with Quote  View user's IP address  Delete Topic
Origin Ver. and Service Release (Select Help-->About Origin): Origin 2018 (64 bit) SR1
Operating System:Windows 8.1

Dear all,

I used sql query in Origin to import data from database. Sheet1 in the picture has the imported data.

I would like to rearrange the data in sheet 1 as shown in sheet 2 for n number of rows in sheet 1. I do not know how to do this programmatically (Labtalk or Origin C) since I normally use the ‘import wizard’ to rearrange the data but the options are limited when I want to import from a database. It would be helpful if someone can suggest how this can be done.

Thank you.

Hideo Fujii

USA
1582 Posts

Posted - 05/02/2018 :  2:56:33 PM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Hi deepakdinakaran,

You can try the following script. As far as I tested, it seems working with the sample;
or you can modify for your more general cases:
////////////////////////////////////////////////////////////////
range rsi=[%H]$(page.active); //Input sheet range
nc=wks.nCols;          //Number of columns
nr=wks.maxRows;        //Number of rows
window -t data;        //Output worksheet
worksheet -a (nr*2-1); //Have more Y columns to store
owb$=%H;               //Output workbook name
xy$=rsi!col(2)[1]$;    //Whole input string in a cell
str$=token(xy$,1,"}")$; nx=str.count(",")+1; //# of X elements
str$=token(xy$,2,"}")$; ny=str.count(",");   //# of Y elements
////////// Handle X Dataset /////////////////////
range tt=[owb$]1!col(1);
for(ii=1; ii<=nx; ii++) {
  if(ii==1) tt[ii]=Value(mid(token(token(xy$,1,"}")$,1,",")$,3)$); //1st cell
  else      tt[ii]=Value(token(token(xy$,1,"}")$,ii,",")$);  //Other cells
}
////////// Handle Y Datasets ////////////////////
oc=2;      //Output column#
for(ir=1; ir<=nr; ir++) {
  cat$=rsi!col(1)[ir]$;
  for(ic=2; ic<=nc; ic++) {
    xy$=rsi!wcol(ic)[ir]$;     //Whole input string in a cell
    range tt=[owb$]1!wcol(oc); //Output column range
    tt[C]$=cat$;               //Assign category string to Comment
    for(iy=1; iy<=ny; iy++) {
      if(iy==1) tt[iy]=Value(mid(token(token(xy$,2,"}")$,2,",")$,2)$); //1st cell
      else      tt[iy]=Value(token(token(xy$,2,"}")$,iy+1,",")$);  //Other cells
    }
    oc++;
  }
}
////////////////////////////////////////////////////////////////

Hope this helps.

--Hideo Fujii
OriginLab
Go to Top of Page

deepakdinakaran

India
14 Posts

Posted - 05/03/2018 :  05:53:31 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Hello Hideo,

It works well. Thank you very much.

Best regards,
Deepak
Go to Top of Page
  Previous Topic Topic Next Topic Lock Topic Edit Topic Delete Topic New Topic Reply to Topic
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
The Origin Forum © 2020 Originlab Corporation Go To Top Of Page
Snitz Forums 2000