18505_en-US

liliconcom

How to use only some columns from a row as column headers?

As part of a database export I get a file with some headers that I can use straight away ( from Experiment ID to DOE_FILE_ID) but then I get some 'generic' headers of C1 to C255

 

what I need to do is to select all of the first row of data which has C1 to C255 as their headers and replace it with the information from the selected first row

 

so C1 would be changed to Sample, C2 would be OLD Sample Identifier etc. For every table I get this information will be different so I can't use a Find & Replace to do this.I cannot change the way that this data is imported using preferences as it is from a database.

 

Many regards

 

 

David

Re: How Do I Use Only Part of a Row of Data as Column Header?<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

I guess I should have mentioned I was assuming you wanted to make a button out of it. script > Button > Efficient repetition.

Re: How Do I Use Only Part of a Row of Data as Column Header?

Another option is to use the Column Names Utility Addin.

Select the columns you want to move up and then click move up.

Best,

Stan

Re: How Do I Use Only Part of a Row of Data as Column Header?

If you're okay doing it interactively, you can use copy and paste.

Select the cells in the first row that have the column names. Then select the columns in the columns panel on the left and paste them.

See this blog post for a more complete explanation, with a video.

-Jeff

Re: How Do I Use Only Part of a Row of Data as Column Header?

To start out with,

colNames = dt << get column names;For( i = 1, i <= N Items( colNames ), i++,  Column( i ) << set name( Column( i )[1] ));// delete the first rowdt << select rows( 1 );dt << delete rows;

This will load all the column names into colNames, loop over the list, and then set each name to its first element.

For your case, I would use a regex filter to loop over the column names, adding the columns beginning with "^C\d" to a separate list.

badCols = {};For( i = 1, i <= N Items( colNames ), i++,    cname = Column( colNames ) << get name;    If( !(Is Missing( Regex( cname, "^C\d" ) )),        Insert Into( badCols, colNames )    ););Show( badCols );<CODE><SPAN style="font-family: arial, helvetica, sans-serif; font-size: 10pt; line-height: 1.5em;">colNames = dt << get column names;</SPAN>

To then rename those specific columns, you can do the following:

For( i = 1, i <= N Items( badCols ), i++,    cname = Column( badCols ) << get name;    Column( cname ) << set name( Column( cname )[1] ););

I'm still working on selecting the first cells of the relevant columns and deleting that cell (the column names), and you may also then want to delete the last row if it is empty, but this is a good place to start. I'll update when I figure that last piece out, but you may beat me to it.

I hope this helps.