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