Introduction to BioMart

Mining data

Data Mining in Ensembl

Unfortunately UCSC table browser doesn't have Ensembl gene track for hg38. But they do have it for hg19 and the below command should work for you.

mysql \
  --user=genome \
  -N \
  --host=genome-mysql.cse.ucsc.edu \
  -A \
  -D hg19 \
  -e "select ensGene.name, name2, chrom, strand, txStart, txEnd, value from ensGene, ensemblToGeneName where ensGene.name = ensemblToGeneName.name" > \
  output.txt

You can try the same command with hg38 but you will have to choose other gene models such as refseq or ucsc.

Follow these steps: http://kandurilab.org/bioinformatics/biostars/UCSC_tableBrowser_annotation.pdf

Here's an easy way to do it from UCSC's table browser:

1. In the table browser, select Ensembl Genes as your track
2. Under output format, choose "selected fields from primary and related tables"
3. Add your output file name, if you want one (otherwise, it will print to the browser)
4. Click "get output"
5. On the next page, you will get to choose your fields.
6. Under linked tables, check ensemblToGeneName, then press "Allow selection from checked tables"
7. The page will refresh, and you should have a new table called hg19.ensemblToGeneName
8. Check of name, chrom, strand, txStart, txEnd, and name2 in hg19.ensGene (or any fields you'd like)
9. In hg19.ensemblToGeneName, check "value", which has the description "alternate gene name"
10. Press "get output"

If you did it right, you should get a table that looks a bit like this (I took this chunk from chr1:100,000,000-150,000,000):

#hg19.ensGene.name  hg19.ensGene.chrom  hg19.ensGene.strand hg19.ensGene.txStart    hg19.ensGene.txEnd  hg19.ensGene.name2  hg19.ensemblToGeneName.value
ENST00000263174 chr1    +   100111498   100160097   ENSG00000099260 PALMD
ENST00000605497 chr1    +   100111748   100155633   ENSG00000099260 PALMD
ENST00000605613 chr1    +   100133135   100135379   ENSG00000099260 PALMD
ENST00000496843 chr1    +   100148821   100160097   ENSG00000099260 PALMD
ENST00000434734 chr1    +   100163797   100164734   ENSG00000223656 HMGB3P10