How to Convert VCF to TSV: A Practical Guide for Spreadsheet Analysis

By Dr. Zubair Khalid, DVM, MS, PhD · 2026-11-10

By the end of this guide you will be able to take a variant call format (VCF) file and turn it into a tab-separated values (TSV) table that opens cleanly in Excel, Google Sheets, or a data frame, with one row per variant and one column per field you care about. You will also be able to build a genotype matrix with one column per sample, explode annotation fields into their own columns, and prove that the conversion kept every record.

You need four things on hand. A VCF or compressed VCF (.vcf.gz), bcftools (version 1.15 or newer is a safe target), tabix, and either awk or Python 3. If you plan to use the pysam route, add that package with pip. No reference genome is required for a plain conversion, though you will need one if you decide to normalize multi-allelic records first.

The commands below are written for a Unix shell. On Windows, run them in WSL2 rather than trying to force them through PowerShell.

What a VCF File Actually Contains

VCF is a text format for storing gene sequence variants. It is maintained as part of the hts-specs family of formats under the Global Alliance for Genomics and Health (GA4GH), the same standards body that oversees SAM, BAM, CRAM, and BCF. Because it is a specification and not a single vendor's file type, every variant caller that follows it produces output you can parse the same way.

The Header Block

A VCF begins with lines that start with ##. These are metadata declarations. They define the file version, the reference build, the contigs, and the type and number of every INFO and FORMAT key that appears later. A single #CHROM line follows and names the columns, ending with the sample identifiers.

Losing this header is the most common reason a converted table becomes unreadable six months later. The header tells you that AF is a float with one value per alternate allele and that AD is an integer with one value per allele including the reference. Without it, a column of numbers has no defined meaning.

The Eight Fixed Columns

Every VCF data line starts with the same eight fields.

1. CHROM, the chromosome name exactly as the reference names it.
2. POS, a 1-based integer position. This is not the same coordinate system as BED, which is 0-based and half-open.
3. ID, a semicolon-free identifier such as an rs number from dbSNP, or . when absent.
4. REF, the reference allele.
5. ALT, one or more alternate alleles separated by commas.
6. QUAL, a Phred-scaled quality score.
7. FILTER, PASS or a named filter that rejected the site.
8. INFO, a collection of key-value annotations.

INFO, FORMAT, and Samples

The ninth column is FORMAT, which lists the per-sample keys in order, for example GT:DP:AD. Every column after that is one sample, and its value string follows the same order. A genotype of 0/1 means heterozygous with unphased alleles, while 0|1 means the phase is known. A missing genotype is ./. and a missing scalar value is ..

The INFO column holds annotations such as total depth or allele frequency. Multiple entries are packed into one field with a separator character, which is exactly why naive comma splitting breaks.

Why Spreadsheets Struggle With VCF

Three properties of VCF fight against spreadsheet software.

First, a VCF line is not a flat record when samples are present. A single row can hold hundreds of sample genotypes, each of which is itself a small structured string.

Second, INFO and FORMAT values are delimited internally. Allelic depths like 8,4 and multi-allelic frequencies like 0.25,0.10 contain commas, so any tool that splits on commas will shift every downstream column.

Third, spreadsheet programs apply automatic type detection. A column of gene symbols can be silently rewritten as dates, and long numeric identifiers can be converted to scientific notation. The fix is to import as text and set column types explicitly, which is easier when the file is already a clean TSV.

Where VCF Sits in the File Format Chain

Variant calling is the step that produces a VCF, and it sits near the end of a long pipeline. Raw sequencing reads arrive as FASTQ, align to a reference as SAM or its binary forms BAM and CRAM, and then variant callers emit VCF. The Iliad workflow suite is built around exactly this progression, moving from raw FASTQ, CRAM, or IDAT inputs through to a quality-controlled VCF for downstream work [1].

FASTQ and FASTA are often confused, and the difference matters when you trace where your VCF came from. A FASTA file stores a sequence record on two logical lines, a header beginning with > and the sequence itself. A FASTQ file stores four lines per read, a header beginning with @, the sequence, a + separator line, and a quality string with one character per base. That quality string encodes the Phred score, where Q equals negative ten times the base-10 logarithm of the estimated error probability. FASTA carries no per-base quality. FASTQ is the output of a sequencer, FASTA is typically a reference genome or an assembled contig set.

VCF is deliberately positioned as an interchange format. sTELLeR, a transposable element caller for long-read genomes, writes its results as VCF specifically so the calls stay compatible with other variant callers and with downstream analysis [2]. That design choice is why learning one conversion pattern pays off across many tools.

Three TSV Shapes You Can Build

Before writing a command, decide which shape your analysis needs.

Sites table. One row per variant, with the eight fixed columns plus selected INFO keys. No sample columns. Best for annotation review, filtering, and joining to BED files.

Genotype matrix. One row per variant and one column per sample, each cell holding a genotype such as 0/1. Best for cohort-level filtering, calculating call rates, and scanning for samples with unusual patterns.

Tidy genotype table. One row per variant per sample, with columns for sample name, genotype, depth, and allelic depth. Best for pivot tables, per-sample summaries, and plotting in a spreadsheet.

Each shape answers a different question. Converting once into the sites table and once into the matrix is usually cheaper than trying to reshape a single wide file later.

What You Need Installed

Check your tools before starting.

bcftools --version
tabix --version
python3 --version
awk --version

If bcftools is missing, install it through conda with conda install -c bioconda bcftools, through Homebrew with brew install bcftools, or from your distribution's package manager. The same package usually brings tabix.

Create a small test VCF so you can see the output before running anything on real data.

##fileformat=VCFv4.2
##contig=<ID=chr1,length=248956422>
##INFO=<ID=DP,Number=1,Type=Integer,Description="Total depth">
##FORMAT=<ID=GT,Number=1,Type=String,Description="Genotype">
##FORMAT=<ID=DP,Number=1,Type=Integer,Description="Read depth">
##FORMAT=<ID=AD,Number=R,Type=Integer,Description="Allelic depths">
#CHROM	POS	ID	REF	ALT	QUAL	FILTER	INFO	FORMAT	S1	S2
chr1	1000	rs123	A	G	50	PASS	DP=30	GT:DP:AD	0/1:12:8,4	0/0:18:18,0
chr1	2000	.	C	T	42	PASS	DP=22	GT:DP:AD	0/0:10:10,0	1/1:12:0,12

Save it as trio.vcf. Every command below works on this file, so you can compare your output to the expected results shown.

Step-by-Step: Convert VCF to TSV With bcftools query

bcftools query is the correct tool for this job because it streams the file and formats each record without loading everything into memory. The general pattern is a format string with tab escapes and a newline at the end.

Step 1: Read the Header Before You Convert

bcftools view -h trio.vcf

Look for the ##INFO and ##FORMAT lines. They tell you which keys exist and how many values each one carries. If you request %INFO/DP on a file that never declared DP, bcftools stops with an error like no such tag defined in the VCF header: INFO/DP. The fix is to request a key that is actually declared, which you confirm from this header.

Step 2: Count Records and Samples

bcftools view -H trio.vcf | wc -l
bcftools query -l trio.vcf

The first command returns 2 for the test file. The second returns S1 and S2, one per line. Note both numbers now, because you will compare against them after conversion.

Step 3: Write the Sites Table

bcftools query \
  -f '%CHROM\t%POS\t%ID\t%REF\t%ALT\t%QUAL\t%FILTER\t%INFO/DP\n' \
  trio.vcf > trio.sites.tsv

The \t sequences become real tab characters. The trailing \n ends each record, which is required. Without it, every record runs together on one line.

Expected output:

chr1	1000	rs123	A	G	50	PASS	30
chr1	2000	.	C	T	42	PASS	22

This file opens in a spreadsheet with eight columns and two data rows.

Step 4: Add a Header Line Yourself

bcftools query does not emit a header. Write one and append the body.

printf 'CHROM\tPOS\tID\tREF\tALT\tQUAL\tFILTER\tDP\n' > trio.header.tsv
cat trio.sites.tsv >> trio.header.tsv
mv trio.header.tsv trio.sites.tsv

If you plan to index the TSV with tabix, start the header with # instead. tabix skips lines that begin with # and will treat a bare header as data.

Step 5: Check the Output

wc -l trio.sites.tsv
awk -F'\t' '{print NF}' trio.sites.tsv | sort -u

Line count should be three, the header plus two records. The column count output should be a single value, 8. If you see more than one number, at least one row has a different field count, and the usual cause is a comma inside ALT or INFO that you did not route into its own column.

The Format String Placeholders That Matter

Square brackets repeat their contents once per sample. That is the mechanism behind the genotype matrix later in this guide.

Step-by-Step: A Pure awk Conversion for Fixed Columns

Sometimes bcftools is not installed and you only need the fixed columns. Tab-delimited VCF data lines split cleanly on tabs, so awk handles the fixed fields directly.

awk 'BEGIN {FS="\t"; OFS="\t"} !/^#/ {print $1, $2, $4, $5, $6}' trio.vcf > trio.awk.tsv

This prints chromosome, position, reference, alternate, and quality. It skips header lines with the !/^#/ condition. It will not parse INFO, and it will not split multi-allelic ALT values. Use it for a quick look, not for a validated table.

A safer version that keeps INFO as one intact column, commas and all:

awk 'BEGIN {FS="\t"; OFS="\t"} !/^#/ {print $1, $2, $3, $4, $5, $6, $7, $8}' trio.vcf > trio.raw_info.tsv

Because the delimiter is a tab, the commas inside $8 stay inside a single cell. This is one of the strongest practical arguments for TSV over CSV in genomics.

Step-by-Step: Exploding the INFO Field Into Columns

INFO is a nested key-value store. There are three reliable ways to flatten it.

Option A: Name Each Key in the Format String

This is the simplest and safest approach when the keys are declared in the header.

bcftools query \
  -f '%CHROM\t%POS\t%REF\t%ALT\t%INFO/DP\t%INFO/AF\n' \
  cohort.vcf > cohort.info.tsv

If a requested key is missing from a particular record, bcftools writes . in that position. That behavior is useful because it keeps the column count stable across the whole file.

Option B: Parse INFO in Python

Use this when you need dozens of keys, when keys vary between records, or when you want to keep the raw string for auditing.

import csv
import sys

FIXED = ["CHROM", "POS", "ID", "REF", "ALT", "QUAL", "FILTER"]
KEYS = ["DP", "AF", "AC", "AN"]

writer = csv.writer(sys.stdout, delimiter="\t", lineterminator="\n")
writer.writerow(FIXED + KEYS + ["INFO_raw"])

with open("cohort.vcf") as handle:
    for line in handle:
        if line.startswith("#"):
            continue
        fields = line.rstrip("\n").split("\t")
        info = dict(
            pair.split("=", 1)
            for pair in fields.split(";")
            if "=" in pair
        )
        row = fields[:7] + [info.get(key, ".") for key in KEYS] + [fields]
        writer.writerow(row)

Run it with python3 parse_info.py > cohort.info.tsv. The split("=", 1) limit matters because some values themselves contain an equals sign. The if "=" in pair guard handles flag-style INFO entries that carry no value.

A known failure mode with this script is a field containing an embedded tab, which does not occur in valid VCF but does occur in malformed output from custom scripts. Check the column count after the run.

Option C: Annotation Subfields With bcftools split-vep

Annotation tools such as VEP and SnpEff pack their predictions into one INFO key, usually CSQ or ANN, with subfields separated by a pipe character. The +split-vep plugin promotes those subfields to first-class placeholders.

bcftools +split-vep -a CSQ \
  -f '%CHROM\t%POS\t%SYMBOL\t%Consequence\t%IMPACT\n' \
  annotated.vcf.gz > annotated.tsv

This requires that the VCF header declares the CSQ format string, which VEP adds automatically. If you see Could not parse the CSQ string, the header description and the actual field order disagree, and the fix is to re-run the annotator rather than to guess.

Step-by-Step: Build a Genotype Matrix

The genotype matrix is the shape most people actually want for spreadsheet review. One row per variant, one column per sample, genotype in each cell.

Build the header first.

(
  printf 'CHROM\tPOS\tREF\tALT'
  bcftools query -l cohort.vcf | awk '{printf "\t%s", $0}'
  printf '\n'
) > matrix.tsv

The subshell groups the three print operations into one output stream. The awk program prints a tab before each sample name so the header aligns with the body.

Append the genotype rows.

bcftools query \
  -f '%CHROM\t%POS\t%REF\t%ALT[\t%GT]\n' \
  cohort.vcf >> matrix.tsv

For the two-sample test file the result is:

CHROM	POS	REF	ALT	S1	S2
chr1	1000	A	G	0/1	0/0
chr1	2000	C	T	0/0	1/1

To carry depth alongside genotype, extend the bracketed section with a compound expression.

bcftools query \
  -f '%CHROM\t%POS\t%REF\t%ALT[\t%GT:%DP]\n' \
  cohort.vcf > matrix_with_depth.tsv

Each cell then reads something like 0/1:12. Some analysts prefer one column per measurement. In that case run the query twice with different bracketed expressions and join the results on the first four columns, or use the tidy shape below and pivot inside the spreadsheet.

Step-by-Step: Tidy Genotypes for Pivot Tables

The tidy shape puts one genotype observation per line.

bcftools query \
  -f '%CHROM\t%POS\t%REF\t%ALT\t%SAMPLE\t%GT\t%DP\t%AD\n' \
  cohort.vcf > tidy.tsv

Expected output for the test file:

chr1	1000	A	G	S1	0/1	12	8,4
chr1	1000	A	G	S2	0/0	18	18,0
chr1	2000	C	T	S1	0/0	10	10,0
chr1	2000	C	T	S2	1/1	12	0,12

Four variants times two samples becomes eight rows. This is the format to load into a pivot table when you want mean depth by sample, or a count of heterozygous calls per chromosome. The AD column stays intact because the delimiter is a tab.

Step-by-Step: Convert With Python and pysam

If you prefer Python and already have pysam installed, the library handles BGZF decompression, indexing, and header parsing for you.

import pysam

vcf = pysam.VariantFile("cohort.vcf.gz")

with open("pysam_sites.tsv", "w") as out:
    out.write("CHROM\tPOS\tID\tREF\tALT\tQUAL\tFILTER\n")
    for record in vcf:
        alts = ",".join(record.alts) if record.alts else "."
        row = [
            record.chrom,
            str(record.pos),
            record.id or ".",
            record.ref,
            alts,
            "." if record.qual is None else f"{record.qual:g}",
            ",".join(record.filter.keys()) or ".",
        ]
        out.write("\t".join(row) + "\n")

Two details matter here. record.alts is None for records with no alternate allele, so guard it. And record.qual is None when the QUAL field is ., so format it conditionally rather than letting Python raise a type error.

pysam opens .vcf and .vcf.gz files. For random access by region it needs a tabix index, which you create with tabix -p vcf cohort.vcf.gz.

Working With Files Too Large for a Spreadsheet

A VCF with tens of thousands of samples will not fit in a spreadsheet no matter how you convert it. Excel's grid is just over one million rows, and a cohort file can exceed that in variant count alone before sample columns are added.

Stream instead of loading. bcftools query already streams, so memory use stays flat regardless of file size. Avoid pandas.read_csv on a multi-gigabyte TSV unless you have checked available RAM, and if you must use it, pass chunksize.

Split by chromosome when you want per-chromosome workbooks.

bcftools index -s cohort.vcf.gz | cut -f1 > chroms.txt

mkdir -p parts
while read -r chrom
do
  bcftools view -r "$chrom" -Oz -o "parts/${chrom}.vcf.gz" cohort.vcf.gz
done < chroms.txt

Sort and index before splitting if the file is unsorted. bcftools sort -Oz -o sorted.vcf.gz input.vcf produces a position-sorted file, and tabix -p vcf sorted.vcf.gz builds the index. Attempting a region query on an unindexed file produces Could not retrieve index file, which is a missing index rather than a corrupt file.

Compress the finished TSV with bgzip -c variants.tsv > variants.tsv.gz. Keep the header line starting with # if you want tabix -s 1 -b 2 -e 2 variants.tsv.gz to work, because the index needs to know which lines to skip.

Key Options and Formulas

How to Verify the Conversion

Run three checks after every conversion, not just the first one.

Record count. Count VCF data lines and compare to TSV lines minus the header.

bcftools view -H cohort.vcf | wc -l
wc -l cohort.sites.tsv

The second number should be the first plus one. If it is lower, records were dropped, usually by an unquoted shell glob or by a filter you forgot was active.

Column stability. Every row must have the same field count.

awk -F'\t' '{print NF}' cohort.sites.tsv | sort -u

A single number means the table is rectangular. Two or more numbers mean a delimiter leaked into a field.

Content fingerprint. Hash the coordinates and alleles on both sides.

bcftools query -f '%CHROM:%POS:%REF:%ALT\n' cohort.vcf | md5sum
tail -n +2 cohort.sites.tsv | cut -f1-4 | tr '\t' ':' | md5sum

Identical hashes confirm that every variant survived in the same order. A mismatch usually means the TSV was sorted differently, not that data was lost, so check sort order before assuming corruption.

A fourth check is worth the effort on clinical or publication data. Recompute one derived value from the TSV and compare to the VCF. For example, count heterozygous calls and compare to bcftools query -f '[%GT\n]' cohort.vcf | grep -c '0/1'.

Common Mistakes and Limitations

Splitting on commas. The single most common failure. ALT uses commas for multiple alleles, AD uses commas for allelic depths, and INFO uses commas for per-allele arrays. A CSV export that splits on commas silently shifts columns. TSV avoids this because commas stay inside cells.

Losing the header. A TSV without a header is a pile of numbers. Keep the original VCF alongside the TSV, and record the exact bcftools version and format string in a README or a script. Reproducing a table six months later is trivial with the command and impossible without it.

Letting Excel guess types. Gene symbols such as MARCH1 and SEPT2 have been converted to dates by spreadsheet autocorrect for years, and the error persists in published supplementary tables. Long numeric IDs get converted to scientific notation. Fix this by importing through the data import wizard, setting the delimiter to Tab, and forcing affected columns to Text before the data loads.

Confusing missing values. VCF uses . for a missing value. An empty cell in a TSV is not the same thing, and a spreadsheet formula treating blank as zero will produce wrong means. Decide early whether missing becomes ., NA, or blank, and apply that rule uniformly.

Forgetting phasing. 0/1 and 0|1 are different genotypes in the presence of phase information. A conversion that strips the pipe destroys that distinction. Keep the original string unless you deliberately want unphased calls.

Ignoring multi-allelic records. A record with ALT of G,T represents two variants. If your downstream tool expects biallelic rows, split first with bcftools norm -m -any -f reference.fa -Oz -o split.vcf.gz input.vcf. If you split after conversion, you must also split the per-allele INFO arrays yourself, which is error-prone.

Assuming chromosome order is numeric. Lexicographic sorting puts chr10 before chr2. Spreadsheet sort and Unix sort both do this by default. Use a natural sort, or add a numeric chromosome index column, when order matters for a figure.

Treating TSV as reversible. A TSV is a projection of a richer format. VCF header metadata, FORMAT definitions, and phasing notation do not survive a round trip. Keep the VCF as the source of truth and regenerate the TSV when the analysis changes.

Sharing patient-derived tables carelessly. A TSV of genotypes is as sensitive as the VCF it came from. Crypt4GH was defined as a container that allows byte-level random access to encrypted genomic data stored in standards including VCF and BCF, so analysis can proceed without creating a decrypted copy that lingers on disk [3]. If a table must leave your environment, encryption and access control belong in the workflow, not in an email attachment.

Forgetting the pipeline context. Conversions rarely happen once. They happen inside a workflow that also handles trimming, alignment, and calling. The CAMP system was built as a modular Snakemake architecture precisely because the expanding set of metagenomics file formats makes scalable workflows hard to design and reproduce [4]. Write your conversion as a small script with named inputs and outputs, so it slots into that kind of structure instead of living in your shell history.

Mismatched file standards for specialized data. Not every genomic dataset is a variant list. Paired genomic loci, such as chromatin interaction pairs, needed their own file standard because single-locus formats did not fit, and pgltools was developed around that standard for manipulation and intersection [5]. Forcing interaction data into a VCF-shaped table loses the pairing structure. Check whether a purpose-built format already exists before inventing columns.

Encoding and line endings. A UTF-8 byte order mark at the start of a file turns the first column name into an invisible-prefixed string, which breaks joins. Windows line endings leave a trailing carriage return on the last field of every row, which breaks numeric parsing. Convert with dos2unix if needed, and write files with Unix newlines.

Field size limits in Python. The csv module raises field larger than field limit when a single field exceeds its default cap. Long annotation strings cause this. Raise the limit with csv.field_size_limit(10**9) before reading.

Practical Tips for Spreadsheet Work

Keep two files. One is the machine-generated TSV produced by a script, never edited by hand. The other is your working copy for filters, color scales, and pivot tables. Regenerate the first when the VCF changes, and never copy edits backward into it.

Name columns in a way that survives a join. CHROM, POS, REF, and ALT are conventional and match what bedtools, tabix, and IGV expect. Renaming them to "Chromosome" and "Position" makes the file friendlier to read and harder to merge.

Add a variant_id column with CHROM:POS:REF:ALT when you need a stable key for VLOOKUP or a database join. VCF ID is often ., so it cannot serve that purpose.

Freeze the header row and the first four columns. A 500-sample matrix is unreadable without frozen panes.

Use filters rather than deletions. A filtered view preserves the full table for the next question, while a deleted row is gone.

Record the command. A one-line comment at the top of a script, or a README beside the TSV, is enough.

When TSV Is the Wrong Output

TSV is an analysis convenience, not an archive format. Keep the VCF or its binary equivalent BCF as the record of what the caller produced, because those formats carry the header, the sample metadata, and the exact representation of each allele. Convert on demand.

TSV is also the wrong choice for very large cohorts where you need repeated region queries. A bgzipped and tabix-indexed VCF answers a region query in milliseconds, while a plain TSV requires a full scan. Indexed TSV works for simple position lookups but does not understand variant semantics.

Finally, TSV is wrong when the data has structure that a flat table cannot express, such as phase sets, genotype likelihoods, or paired loci [5]. In those cases, keep the native format and export only the subset you need to review.

Frequently Asked Questions

What is the fastest way to convert a VCF to TSV?

bcftools query with a format string is the fastest general method. It streams the file, needs no reference genome, and writes exactly the columns you request.

Can Excel open a VCF file directly?

It can open the file, but the result is unusable for analysis because header lines, packed INFO fields, and per-sample strings all land in the wrong cells. Convert to TSV first, then import through the data import wizard.

Why do my genotypes end up in one column instead of one column per sample?

The format string was missing the square brackets. %GT without brackets returns the first sample only, while [\t%GT] repeats across every sample. Add the brackets and build a matching header from bcftools query -l.

How do I keep INFO annotations when converting to TSV?

Name each key explicitly in the format string, for example %INFO/DP and %INFO/AF. For annotation subfields packed into CSQ or ANN, use the bcftools +split-vep plugin to promote them to individual placeholders.

Does converting a VCF to TSV lose information?

Yes. Header metadata, FORMAT definitions, phasing notation, and the exact allele representation do not survive unless you deliberately carry them into columns. Treat the VCF as the source of truth and the TSV as a derived view.

How should I handle multi-allelic variants?

Split them before conversion with bcftools norm -m -any and a reference genome, so each row carries one alternate allele. That keeps per-allele INFO arrays aligned with the ALT column and avoids ambiguous downstream joins.

What is the difference between FASTQ and FASTA files?

FASTQ stores four lines per read including a per-base quality string, while FASTA stores a header and a sequence with no quality scores. FASTQ comes from sequencing instruments, FASTA usually holds a reference genome or assembled contigs.

How do I convert a VCF to TSV in Python without bcftools?

Parse the file line by line, skip lines beginning with #, split each remaining line on tabs, and write the fields you need with the csv module using a tab delimiter. The pysam library is a cleaner option when it is available because it handles compression and headers for you.

Sources

1. ILIAD: a suite of automated Snakemake workflows for processing genomic data for downstream applications.
2. Detecting transposable elements in long-read genomes using sTELLeR.
3. Crypt4GH: a file format standard enabling native access to encrypted data.
4. CAMP: a modular metagenomics analysis system for integrated multistep data exploration.
5. Pgltools: a genomic arithmetic tool suite for manipulation of Hi-C peak and other chromatin interaction data.