Working in an automated way using Excel spreadsheets is a nightmare... You're right that this sounds like it should be done automatically.  This sounds about the right problem to start heading into computer scripting!

There is a relatively easy way to do this using Python and the Biopython toolkit, just in case you want to do it for yourself, otherwise, if you've got some kind of bioinformatics core facility, they should be able to this.

Of how many sequences are we talking here?

Thanks Peter, It's currently in the low hundreds of sequences but will build as the database grows. I could do it manually, in small batches - it would only take a couple of days I suppose.

I think a tool on Galaxy would provide exactly the result you want (https://usegalaxy.org).

First, upload your data set (I would guess that saving your excel data as tab-delimited text might be sufficient) under "Get Data-->Upload File from your computer".

Then retrieve the sequences with "Fetch Sequences-->Extract Genomic DNA".

They have helpful video tutorials and file format specifications at each page, so it should be no problem.

https://usegalaxy.org/

Thanks Joseph. I'll look into this; sounds promising.

Yes, use Galaxy to retrieve ("fetch") your sequences. Make sure you choose interval rather than FASTA format for the output, and you'll have a tab-delimited file you can open in Excel. You'll need to have chromosome ID, start, end, and strand specified to get the correct sequences. Excel is not great for handling text strings. Perhaps there is a better option that can be suggested if you provide some more details about what you plan to do with the sequences.

Daniel & Joseph,

A bit of background. I am compiling a table of diagnostics assays, and I am including the GenBank accession numbers of the diagnostic targets and the start and end nucleotides of the region of interest (about 200 bp). So I have the following columns in my spreadsheet:

Accession      Base Start        Base end

AB12345        200                   420

CD23456        10125               10320

EF76543         1121234          1121444

What I'd like to do is to retrieve the actual diagnostic targets (i.e. the 200 bases) and include them in the spreadsheet. They are various plant pests and pathogens (e.g. viroids, viruses, bacteria, fungi, nematodes, insects). Would Galaxy be able to handle this range of organisms, or is it primarily human? None of the data sources listed look appropriate.

Vince

You're right, Vince. Galaxy is not suited for this type of query with mixed species, and no chromosome designation. Try Batch Entrez instead, http://www.ncbi.nlm.nih.gov/sites/batchentrez

However, you need to make sure you are using the right Accession #s. I was not successful in finding the accessions you listed by searching GenBank. What is their origin?

Once you have the fasta file for your targets of interest, you can use the mid function in Excel to retrieve only the targeted portion of interest for each sequence. You'll have to convert your fasta file to interval format (the Galaxy tool for this will work just fine!).

I just made up accession numbers to show the format of the data - sorry probably should have mentioned that.

The tip about the "mid" function looks good - actually I think this solves the main issue. Thanks!

Ok. Please put your real accessions into a text file w/o the start/end positions and upload to Entrez Batch. Follow the directions at the link for downloading the data (initially it will return an HTML result, but you can output to a file). If you run into further problems/questions, please post again.

Yep, it looks like Batch Entrez would get you all the sequences, and mid would do the job of getting the correct DNA sequence interval. One caveat is that, at least in your example, the short diagnostic DNA sequence is only a small fraction of the whole sequence, so the Excel file would (at least temporarily) have a much longer sequence than needed, so might get unnecessarily large.

Also, Excel only allows 32,767 characters per cell, which would be a problem for that last entry. However, command-line scripts should be able to parse these data.

Depending on your operating system (Hopefully Mac/Linux for this purpose), a short script should be able to wrangle the Entrez data, combined with your coordinate file, into tab-delimited output file that would be useful for results of arbitrary length. If you posted an example (with real accessions/coordinates) here, or on a coding forum such as www.biostars.org or www.stackoverflow.com, I'm sure someone would be able to hack out a short human-readable program.

Myself, I prefer to read the txt files and save them in cell arrays at MATLAB. You can generate a mat file for each GenBank Sequence that you can later load in seconds. 

If Vincent has access to MATLAB, it is relatively intuitive for a programming environment (compared to R, etc), but would probably still require someone to take him through the basic usage. From Vincent's original question, the less programming (or separate programs) involved, the better, I suspect.

The excel solution seemed like a good one, except for limits on sequence length per-cell.

If Vincent has a Mac, then a 5 or 10-line bash script would do the job, and he could just process the file each time by rerunning the script; if he is running Windows, I wouldn't want to subject him to troubleshooting the Cygwin environment.

I agree with Joseph, but would add that I don't think character limits in Excel will be a problem. Per the example, the differences between col 3 and col 2 (i.e. the length of the target sequence) are small. Most transcripts are less than 10kb, so instances where the GenBank sequence exceeds the length limit should be extremely rare.

Hey Vincent,

You've gotten a lot of good answers here for obtaining the sequences but I'd want to know what you want to do next. I'm assuming you have some intention of working with these sequences (comparing them, aligning them, etc.). I'd be happy to chime in, or script up, a solution if you give me an idea of what your final goal is.  In most cases analysis tools expect some particular format, for example a tab delimited file.  If you want you can contact me off this thread.

Thanks for the offer Shaun. My only problem was retrieving the sequences en masse. Everything after that I can cope with, but the offer is appreciated.

I've now got a method which takes about ten minutes to complete (which is fast enough for me). I paste a column of accession numbers into a text file and then retrieve the sequences using batch Entrez. I paste the Fasta sequences into another text file, then reformat them using Galaxy. I then import into an Excel file and that gives me a column of sequences which I paste into my Excel spreadsheet. Next I use the "mid" function, combined with the info from the start and finish bases, and extract the required sequences into a new column.

This might sound clumsy, but it works quickly enough that I can do it while my cup of tea cools, and that's good enough for me. Thank for all the suggestions.

The good thing about MATLAB regarding data storage is that you can save everything in a *.mat file that you can load whenever needed.  You can have as many mat files as required. This way, your files will be ready and organised and accessible in a matter of seconds. 

MATLAB commands:

To save a file use:

save('filename.mat');

To load afile use:

load('filename.mat');

For example, if you have a specific genome to investigate, this you can download the text files of that genome from the online using this link for example:

ftp://ftp.ncbi.nih.gov/genomes/Bacteria/

and then read them using MATLAB:

---------------------------------------------------------------

A function to read the text file line by line:

---------------------------------------------------------------

function genome = readfile(filename)

fid = fopen(filename);

k = 1;

while 1

tline = fgetl(fid);

if ~ischar(tline), break, end

genome{k} = tline;

% disp(tline)

k = k+1;

end

fclose(fid);

---------------------------------------------------------------

A fuction to save the *.fna file:

---------------------------------------------------------------

function DNA = fna2DNA(filename);

genome = readfile(filename);

genome = {genome{(2:length(genome))}};

s = reshape(char(genome)',1,size(char(genome),1)*size(char(genome),2));

DNA = cellstr(s);

---------------------------------------------------------------

regards ...