[EpiData-list] Is there any way to import from Excel to EpiData?

epidata-list at lists.umanitoba.ca epidata-list at lists.umanitoba.ca
Tue Apr 24 19:22:49 CDT 2007


I have had the best success by exporting Excel data as tab-separated 
variables and creating a .qes file that matches exactly how I want the 
data to appear. The only problem is with dates, which export as 
mm/dd/yyyy hh.mm.ss unless you set the cell format in Excel to be 
dd/mm/yyyy (this is usually a 'special' format in Excel). By the way, 
Lotus 123 does a better job of creating such files. Avoid 
comma-separated (.csv) files, as Excel doesn't always handle strings in 
a way that is compatible or consistent.

The advantage is that you have a .qes file that documents your file 
structure nicely. By specifying the structure this way, you can be sure 
you end up with what you want. EpiData is pretty picky about this, and 
illegal data in the .csv file will stop the import. I've gone through a 
lot of trial and error sometimes getting a particularly messy file to 
import.

When exporting to .dbf format, be sure that the first row has legal 
EpiData field names and, again, that dates are formatted the way you 
want. You cannot easily control the length of fields (numeric or 
string), though this is possible through a bit of trial and error by 
changing the column widths. Be sure the first data row in your 
spreadsheet has no empty cells, as these will export to dBase as 
strings, even if the rest of the column has numbers. Specifying explicit 
formats, at least for the first data row (row 2) will help.

With files that have formulas in some columns and not a lot of columns, 
I have also created a column that is text created by concatenating the 
cells in the same row so that the resulting text string is in a fixed 
format that easily imports into EpiData.

This transformation is a critical step in data management and should not 
be left to chance. This may seem like a lot of work when you do this one 
time only; however, if you have to do the import more than once on the 
same data, it is definitely worth the effort.

Jamie Hockin
Public Health Agency of Canada

Richard Herrell wrote:

> You can save the Excel file as a .dbf file, then import the .dbf file into Epidata.  I assume you'll need to add variable names in the first row of the Excel file.  
>    
>   Richard Herrell
>   NIMH



More information about the EpiData-list mailing list