I Opened My Raw DNA File in Excel and Saved It, Now a DNA Site Rejects It. Can I Fix It?
Saving a 23andMe raw data file in Excel rewrites it, and other DNA sites then refuse it. Here is what changes, how to see it, and the safest fix.
Yes, it can usually be fixed. Opening a 23andMe raw data file in Excel and saving it rewrites the file, often changing the separators or, if you chose that format, turning it into an .xlsx workbook, and a DNA site that expects the original layout then calls it invalid. The safest fix is the untouched original: download your raw data again from your 23andMe account, then confirm it is readable with the free checker at healthosx.com/dna/check.
What a spreadsheet re-save does to a raw DNA file
A 23andMe raw data file is plain text. It starts with comment lines beginning with #, then one marker per line in four columns separated by tab characters: rsid, chromosome, position, genotype. There is nothing else in it. That plainness is why DNA tools can read it, and why they are strict about it.
Excel does not treat it as text. It splits each line into cells, and when you save, it writes the cells back out in a format that may not match the original. Your 23andMe file fits under Excel's 1,048,576-row limit, so nothing is cut off by size, but the layout can still change. If you press Save (Ctrl+S) on the opened .txt and keep the text format, Excel writes tab-delimited text. That is the closest to the original, but it can still alter comment lines and usually switches to Windows (CRLF) line endings. Choose CSV and the tabs become commas. An .xlsx workbook only happens if you choose it in Save As, and then the file stops being text at all, so a tool looking for lines of rsid, chromosome, position and genotype finds a zipped workbook instead. Excel's CSV UTF-8 option can also add an invisible marker at the very start of the file.
Any one of those is enough. A tool that splits each line on tabs and expects four fields gets one long field per row, or a first line it cannot parse, and gives up.
| Separator | Still plain text | Safe for a DNA site | |
|---|---|---|---|
| Save (Ctrl+S, keep text format) or Save As Text (tab delimited) | Tabs, but comment lines may gain quotes and line endings may become CRLF | Yes | Sometimes |
| Save As Excel Workbook (.xlsx) | Cells, no separators | No | No |
| Save As CSV | Commas | Yes | Sometimes |
| Original download, never opened | Tabs | Yes | Yes |
Scroll the table sideways for the rest →
General behaviour of Excel's save formats. Details can vary by Excel version.
The fix: download a fresh copy and check it before uploading
First, confirm the cause. Drop the damaged copy on the free file checker. It reports the company, the genome build, the marker count and the chip where the file states it, and whether the file can be read. If the file was re-saved in a spreadsheet, it names that as the rejection reason. The checker runs in your browser, so the file is never uploaded and there is no account.
Then go back to the source. Sign in to your 23andMe account and find the option to download your raw data. 23andMe prepares the download on request and may email you when it is ready, so a fresh copy is not always instant. It arrives as a .zip with one .txt inside. Keep it that way. Some sites take the zip as it is, and unzipping it yourself and then opening the .txt in Excel just repeats the mistake.
Check the new file with the checker before you upload it anywhere:
- Look at the marker count. It is roughly 630,000 on a recent v5 kit; older chips (v3, v4) differ, and the checker reports the count and chip where the file states it. A count far below what your chip should have suggests something is cut off.
- If the checker says the file is fine and the other site still refuses it, the problem is probably that site's own rules, such as a different build or a different company's layout.
How to tell what changed in your saved copy
Open the damaged file in a plain text editor, not Excel. Notepad, TextEdit in plain text mode, or any code editor will do. Then look at three things.
- The separators. If the lines read rs4477212,1,82154,AA instead of having wide gaps between the fields, it was saved as CSV.
- The first line. The original begins with a comment, "# This data file generated by 23andMe". If that line is wrapped in quotes, split by commas, or gone, the top of the file was rewritten.
- The file type. If the file starts with the letters PK followed by unreadable characters, you saved a workbook. Renaming .xlsx to .txt does not turn it back into text.
The genotypes themselves are rarely damaged. Letters like AG or CC survive a spreadsheet fine. What breaks is the wrapping around them, which is why the original is a clean fix and not a repair job.
| Original download | Saved as CSV in Excel | |
|---|---|---|
| Header line | # rsid[tab]chromosome[tab]position[tab]genotype | # rsid,chromosome,position,genotype |
| Comment line containing a comma (illustrative) | # This file contains raw genotype data, including… | "# This file contains raw genotype data, including…" |
| Marker line | rs4477212[tab]1[tab]82154[tab]AA | rs4477212,1,82154,AA |
Scroll the table sideways for the rest →
Illustrative example only. [tab] stands for a tab character. The comment line is a made-up example of a comment that contains a comma; quotes appear only on such a line. Your file may differ by Excel version and save option.
What if the original is gone or you cannot get it back
This is the case where the simple answer fails. If you deleted the download, lost account access, or the file came from someone else, you have a few options, in this order.
- If you only opened the file and closed it without saving, nothing changed on disk. Excel's prompt to save is what does the damage, so decline it.
- If you saved a .txt or .csv, run it through the checker as described above and see whether it can still be read.
- If you saved an .xlsx and no longer have the original, note that the checker and converter accept .txt, .csv, .zip, .vcf and .vcf.gz, not .xlsx. Open the .xlsx in Excel, use Save As and choose Text (Tab delimited), then run that file through healthosx.com/dna/check before using healthosx.com/dna/convert.
- If the checker can read the saved copy, the converter can write it out in the 23andMe layout. The result is a converted file, marked as converted on its first line, not a 23andMe original. A site that checks for the exact 23andMe header may still refuse it, so the original is still better.
One more size note: a whole-genome VCF has millions of variants, more than Excel's row limit, so do not save one over the original.
Keep an untouched copy of the original
A file that looks fine in a spreadsheet is not necessarily fine, because the neat cells only show what Excel made of the file, not what is on disk.
Make a habit of copying the original into a folder you never open, and working from a duplicate. It costs nothing and removes this whole class of problem.
Once you have a clean file, the next step is seeing what is in it. HealthOSX reads it free in your browser, with no account, and the free DNA report covers ancestry, traits and medication genes from the same file.
New to raw DNA files? Start with the full guide: 23andMe Raw Data Interpretation Free in Your Browser
Written by
HealthOSX Research