Affymetrix provides sequence and annotation information as raw data files for use with a variety of software tools.  This page discusses some details that my be helpful in working with these raw data files.  Mouse and Chicken GeneChips will be used in the following discussion.

Where are the raw data files containing Affy sequence and annotation information?  For some time the starting point to get GeneChip information is the Affy products page. The selections on that page recently changed.  Look for a dropdown box under "Expression Analysis Arrays" on that page and select an array of interest.

I find Affy's naming conventions somewhat confusing for their mouse chips.  The older chip was called the "Murine Genome U74Av2" (MGU74Av2).  Newer chips are called "Mouse Expression Set 430" and are sold in "A" and "B" chips, MOE430A and MOE430B.  The annotations were better for the "A" chip in general, with many ESTs present on the "B" chip..  Then these "A" and "B" chips were combined into a single chip, Mouse430_2.  For some reason there is an "A" version of this chip now, Mouse430A_2.  Having two chips with similar names, MOE430A and Mouse430A_2, is a confusing convention

After you are certain which chip you care about and have found its web page, there should be an area at the lower right labeled "ADDITIONAL SUPPORT".  One of the links under this heading is "Technical Notes."  To find data, follow the link labeled "All Support Materials...".  Here are direct links to these support materials:  Mouse430_2   Chicken

On each support materials page you'll want the links under "NetAffx Annotation Files" and "Sequence Files."  For example, with Mouse430_2 you'll see these files:

NetAffx Annotations Files

• BLASTP
• BLASTX
• CSV
• MAGE-ML XML
• Orthologs

Sequence Files

• Consensus Sequences
• Control Sequences
• Probe Sequences, FASTA or Tabular
• Target Sequences, FASTA

Affy updates these files roughly quarterly (March, June, Sept, Dec), and they provide no automatic notification when the files are updated.  As far as I know, the only way to find out if new files exist is to periodically check for updates. For now, Affy provides some release notes on this page identifying the significant changes. 

Automating the download process:  I never would have found Affy's automatic download tool without calling and asking Affy on the phone: On the main Affy page, select the Support link from the tabsheet horizontal menu bar.  Along the left-side, click on the Developers' Network link.  Next, select the DevNet Tools link.  Look for the section "NetAffx Download Utility."  Download and install this program under Windows.  This utility is setup to check for changes in the files every day (even though most files change quarterly at most). To automatically detect changes, one needs to check the annotation directory every day and check for changes.  Here's what this utility looks like:

DO NOT specify a network drive for the Annotation Directory or the service will just die silently without transferring any files. 

The NetAffyx Download Utility is not a "normal" program.  You control this program via an icon in the Windows Control Panel.

What information is in the CSV Annotation file?  The Mouse430_2 annotation file has significant information for the mouse GeneChip.  However, very little information is currently available for the Chick GeneChip.  Knowing which fields have information, and which fields should be ignored because they are constant or missing, for a particular genome is useful information.

In the past I have loaded the annotation CSV files into Access databases to "connect" Affy results with this annotation information.  I found the default Access definitions often truncated a number of data fields.  I would need to changed any "text" fields that were longer than 256 bytes to a "memo" to avoid this truncation.

To solve both problems (to find which fields have useful data, and to see how long the fields are), I wrote a simple R script to create a summary table. Copy and paste this line into R and select a file to process an Affy xxx_annot.csv file:

source("http://research.stowers-institute.org/efg/ScientificSoftware/Applications/Affy/Annotations/AffyStats.R")

Here are the results from processing the file Mouse430_2_annot.csv:

Notes about Mouse430_2 Data Summary:

• The UniqueCount for Probe.Set.ID gives the number of probes in the GeneChip, which is 45,101 in this case.
• Many of the MinLength values are 3, since Affy uses "---" for missing values.
• Fields with MaxLength > 255 cannot be loaded into Microsoft Access database table as "Text" fields.  A "Memo" field must be used to avoid field truncations.
• Fields with only a single unique value, e.g., GeneChip.Array, Annotation.Date, do not have any "per probe" information.  These fields are constant, at least for this GeneChip.
• Fields with considerable missing data are clearly identified from the high MissingCount column with UniqueValue=1, e.g., FlyBase, AGI, and WormBase fields.
• The MGI.Name field dropped from 18,249 unique identifiers on March 31, 2005 to only 13,073 on June 20, 2005.  Most of the other fields had similar statistics in the last two versions of this file.
• R added periods to field names to force compliance with its conventions. 

Here are the results from processing the file Chicken_annot.csv:

Notes about Chicken GeneChip Data Summary:

• The UniqueCount for Probe.Set.ID gives the number of probes in the GeneChip, which is 38,535 in this case.
• In the June 20, 2005 database almost all the fields were missing, however in this Sept 16, 2005 database there are a significant number of values defined for these fields:  Archival.UniGene.Cluster, UniGene.ID, Alignments, Gene.Title, Gene.Symbol, Entrez.Gene, RefSeq.Protein.ID, RefSeq.Transcript.ID.  Three months ago there was only one probe annotated with information in all these fields, so remarkable progress has been made.
• It's clear from this statistical summary that some fields are nearly entirely "missing data":  When MissingCount is 38,535, there is absolutely no data in the given field.
• Many of the MinLength values are 3, since Affy uses "---" for missing values, but seems to have not used this convention in fields 40-42..

Importing Annotation CSV Files into Access.  A few additional notes from Affymetrix would make this process much easier.  In particular, Affy's Probe Set Annotations in Tabular Format document should include some of the following notes.

Why not Excel instead of Access?  Unbelievably, Microsoft still has a limit of 65536  (i.e., 64K) rows in Excel 2002.  If you try to load more rows than that, like with the Orthologs file, Excel will show this message

You won't see records past row 65536 (The Orthologs file for the older MOE430A has over 200,000 rows). 

Access provides database "joins" and other ways to manipulate the data not done easily in Excel.  (The same functionality is available in R using the "merge" statement. I hope to replace some work I've been doing in Access and use R instead.

Loading the Annotation Table in Access.  The Microsoft Access defaults are not always helpful in importing this file.  Start with an empty Access database:

File | Get External Data | Import | files of type:  Text Files (*.csv) |
select the MOE430A_annot.csv file | Import | Delimited | Next

Check:  First Row Contains Field Names

Check the MaxLength column in the above AffyStats summary table to determine which fields must be a "memo" type to avoid truncation.

Many of these "Memo" fields are so long because they contain "denormalized data" from a database perspective.  These fields contain "repeating groups," such as multiple Gene Ontology identifiers within a single field.  

I named the new table "Annotation" to be consistent with the column name on the Affy web page.

Specify the name ("Annotation" as shown above) | Finish

If all goes well, you'll see the above message.  Select OK.

When you have problems, you're likely to see a message such as this:

Fields that are truncated should be changed from "Text" to "Memo".

The Probe Set ID can be used as a primary key since it's unique, which can be changed, if desired, in table design mode.  Here's what the table looks like in design mode:

Here are observations about the various data fields for MOE430A:

Loading the BLASTP and BLASTX tables.  These CSV files also cannot be loaded into Access databases using default parameters.  The "Hit Description" must be made into a Memo field.  The "E-value" can be stored as a "double" instead of a character string.  The Probe Set IDs are not unique, so this field cannot be made a primary key, but making it an index is probably a good idea to speed future processing.

Loading the Orthologs table.  This one is easy since the Microsoft Access defaults work fine.

The Probe Set IDs are not unique in this table, but an index can be added to this field to speed joins with other tables.  This table has 18,663 unique Probe Set IDs.

Loading Lists of Probe Set IDs for Consensus, Control and Target Sequences.  The following files contain FASTA sequence information, so they cannot be loaded into Access tables directly:

• MOE430A_consensus
• MOE430A_control
• MOE430A_target

A short UNIX script can be used to extract the probeset IDs from these files, which can then be loaded into Access:

cat create_probeset_lists
#! /bin/sh

echo "Probe Set ID" > consensus.txt
echo "Probe Set ID" > control.txt
echo "Probe Set ID" > target.txt

grep '>' MOE430A_consensus | cut -d':' -f3 | cut -d';' -f1 >> consensus.txt
grep '>' MOE430A_control   | cut -d':' -f3 | cut -d';' -f1 >> control.txt
grep '>' MOE430A_target    | cut -d':' -f3 | cut -d';' -f1 >> target.txt

The consensus.txt file can be loaded into an Access table, named Consensus, like this:

File | Get External Data | Import | consensus.txt | Import | Delimited | Next | Comma Delimiter |

First Row Contains Field Names | Next

In a New Table | Next | Next | "Probe Set ID" as primary key | Next

Import to Table:  Consensus

Finish

Repeat the above procedure for the control.txt  and target.txt files and change the table names accordingly. 

This process would also work with the MOE430A_probe_fasta file, which is in FASTA format.  However, the MOE430A_probe_tab file is already tab delimited delimited, so that file can be loaded directly into Access.  To get Access to recognize the file a little easier, let's add a ".txt" suffix to MOE430_probe_tab.  Use these similar steps to load the file:

File | Get External Data | Import | MOE430A_probe_tab.txt | Import | Delimited | Next | Tab Delimiter |

First Row Contains Field Names | Next

In a New Table | Next | Next | No Primary Key | Next

Import to Table:  Probe

Finish

In Design Mode, change the field "Probe Set Name" to "Probe Set ID" for consistency with other tables.  This field is not unique so it cannot be a primary key, but make it an indexed field with "duplicates OK".

Here's what's in the Probe table:

Each probe set has a number of rows in this table.  For example the first probe set, 1415670_at, has eleven probe pairs.  Here is the distribution of the number of probe pairs per probe set for MOE430A:

The (Probe X, Probe Y) coordinates in this table specify the location of the probe pair in the Affy .DAT file.

Load the list of "mask" maintenance gene probeset IDs.   From http://www.affymetrix.com/support/technical/mask_files.affx

Each GeneChip® expression array listed below features approximately 100 maintenance genes which serve as a tool to normalize and scale your data prior to performing data comparisons.

To facilitate the use of these genes in scaling or normalization, the mask files that identify these maintenance probe sets on each array are available below.

Download the file http://www.affymetrix.com/Auth/support/downloads/mask_files/moe430anorm.zip.  Unzip this file to create the file moe430anorm.msk.  Copy this file to moe430A_maintenance.txt.  Replace the first three lines ("MOE430A", "[Call]", "[Comp]") with "Probe Set ID". 

Load this file into an Access table and call it "Maintenance":  File | Get External Data | Import | moe4380Aa_maintenance.txt | Import | Delimited | Next | First Row Contains Data | Next | Next | Next | select Probe Set ID as primary key | Next | Maintenance | Finish

A "join" of the Annotation and Maintenance tables can be used to see additional info about these maintenance genes.

For MOE430A, all of the maintenance probe sets had exactly 11 probes.

NOTE:  The "Control" genes have a Probe Set ID starting with "AFFX" so there is no overlap between the "Control" genes and the "Maintenance" genes.

Summary.  The notes above show how to load these MOE430A sequence and annotation tables into an Access database:

Also see:  Affy GeneChip MOE430A:  Probe Sequences in a ProbeSet Example