From DictyWiki

Contents 1 Updating 'Downloads: Gene Information' 1.1 Query 1.2 Save for Downloading and archiving 1.3 Convert to HTML 1.4 Insert header 1.5 Insert footer

Updating 'Downloads: Gene Information'

Query

SELECT ACCESSION as Gene_ID,
       gene_name as Gene_name,
       stragg(  distinct alias_name  ) as Synonyms,
       stragg(  distinct gene_product  ) as Gene_products 
  FROM cgm_chado.v_gene_dictybaseid d left outer 
  JOIN locus_alias la on d.gene_FEATURE_ID = la.locus_no left outer 
  JOIN alias a on la.alias_no = a.alias_no left outer 
  JOIN locus_gp gp on d.gene_FEATURE_ID= gp.locus_no left outer 
  JOIN gene_product g on g.gene_product_no=gp.gene_product_no left outer 
  JOIN cgm_chado.feature f on f.feature_id = d.gene_FEATURE_ID left outer 
  JOIN cgm_chado.dbxref xr on f.DBXREF_ID = xr.DBXREF_ID 
 WHERE d.PRIMARY_FEATURE_DICTYBASEID is not null
 GROUP BY ACCESSION,
       gene_name,
       primary_feature_id


* In SQL submit, select the 'send to Excel' option
* Save as gene_information.xls in a temporary folder

Save for Downloading and archiving

1. Open in excel

2. Sort by gene name

3. To add spaces between commas: replace "," by ", " 

4. Save as gene_information.xls and gene_information.txt in cgm1\data\general 

5. Open the text file and replace " by [nothing] (excel adds extra quote marks)

6. Save as gene_information.xls, .txt in dicty\data\general (replace file from previous month)

7. Commit files in cvs

Convert to HTML

* To insert hyperlinks: In excel, insert a column to the right of the dictybase ID column and type: 

=CONCATENATE("<a target =#new# href=#/db/cgi-bin/gene_page.pl?primary_id=", A2, "#>", A2, "</a>") 
- Copy and paste special: values  
- Delete the contents of A column (dictybase IDs without hyperlinks) 


* To insert anchors for letters: 
- In the empty A column:

=IF(LEFT(C2,1)=LEFT(C1,1),"",CONCATENATE("<a name=#",UPPER(LEFT(C2,1)),"#></a>"))
- copy and paste special: values
- delete A column (dictybase IDs without hyperlinks)


* Save as gene_information.txt 

* Open in word
* Replace " by [nothing]
* Replace # by "
* Replace ^p by </td></tr>^p<tr class="row2"><td>
* Replace ^t by </td><td>

* To fix anchors:
- Replace ></a></td><td><a target ="new" by ></a><a target ="new"
- Replace  <tr class="row2"><td></td><td> by <tr class="row2"><td>

Insert header

* Open with a text editor


* Check first and last column
* '''Insert header & table format'''

<!--#set var="title" value="Gene Information"-->
<!--#include virtual="/inc/header_noSide.html"-->


<style type="text/css">

table.download {

   table-layout:fixed;
   width: 100%;
   spacing: 2px;
   border: none;


}

table.download td {padding: 4px}

tr.row1 {
  background-color:#ACBDCC;
	font-weight: bold;
	font-size: 18px;
}

tr.row2 {
  background-color:#dddddd;
}


</style>
<a name="top"></a>

<h1>Gene Information</h1>
<p align="right"><a target="new" href="index.html">BACK TO DOWNLOADS INDEX</a></p>
<hr>
<p>To view a Gene Page, click on the corresponding dictyBase ID. </p>

<p>Go to:   <a href="#A">A</a>   <a href="#B">B</a>   <a href="#C">C</a>   <a href="#D">D</a>   <a href="#E">E</a>   <a href="#F">F</a>   <a href="#G">G</a>   <a href="#H">H</a>   <a href="#I">I</a>   <a href="#J">J</a>   <a href="#K">K</a>   <a href="#L">L</a>   <a href="#M">M</a>   <a href="#N">N</a>   <a href="#O">O</a>   <a href="#P">P</a>   <a href="#Q">Q</a>   <a href="#R">R</a>   <a href="#S">S</a></a>   <a href="#T">T</a>   <a href="#U">U</a>   <a href="#V">V</a>   <a href="#W">W</a>   <a href="#X">X</a>   <a href="#Y">Y</a>   <a href="#Z">Z</a>
</p>

<table class="download">
   <col width="15%"/><col width="25%"/><col width="25%"/><col width="35%"/></td>
<tr class="row1"><td>GENE ID</td><td>GENENAME</td><td>SYNONYMS</td><td>GENE PRODUCTS</td></tr>

Insert footer

</table><br>
<i>Last updated October 1, 2007</i>

<p align="right">
<a target="new" href="#top">BACK TO TOP</a> - <a target="new" href="index.html">BACK TO DOWNLOADS INDEX</a>
</p><br>

Return to Update_Downloads_Page