Princeton University Library Data and Statistical 
Services

Search DSS





Finding Data Analyzing Data Citing data

About Us


DSS lab consultation schedule
(Monday-Friday)
Sep 1-Nov 3By appt. here
Nov 6-Dec 15Walk-in, 2-5 pm*
Dec 18-Feb 2By appt. here
Feb 5-May 4Walk-in, 1-5 pm*
May 7-May 15Walk-in, 2-5 pm*
May 16-Aug 31By appt. here
For quick questions email data@princeton.edu.
*No appts. necessary during walk-in hrs.
Note: the DSS lab is open as long as Firestone is open, no appointments necessary to use the lab computers for your own analysis.

Follow DssData on Twitter
See DSS on Facebook

Home Online Help Statistical Packages DBMS Copy Converting Excel Files

Converting Excel and other spreadsheet files

DBMS/Copy can be used to convert spreadsheets from Excel and similar programs to many other formats. Often, it is easier to save your spreadsheet as a comma separated values (csv) or tab-delimited file, as most spreadsheet, database, and statistical programs can read these types of files directly. Here are a couple of thngs to remember when converting spreadsheets:

  • DBMS/Copy can only convert one sheet at a time, so if you have data on more than one sheet, you may need to edit your data before using DBMS/Copy.
  • DBMS/Copy and other programs can use the first line of your spreadsheet to create variable names. It will be very helpful if the column headers in your spreadsheet have variable names that conform to the naming rules in the package to which you will be converting.
  1. Once you have started DBMS/Copy, you will be presented with a choice of how you want to run it. In most cases, you will want to select "Interactive".
  2. You will then get a file selection box to select the file you want to convert. Be sure to select the correct file type - there are a few for Excel, so if you are unsure of what type of Excel file you have, just try each until DBMS/Copy shows your file in the selection box.
  3. Then the "Spreadsheet Grabber" window will pop up. This is where you can specify the rows and columns you want to convert - do not include graphics! The spreadsheet grabber will show where the data are on your spreadsheet although it may not show any decimals - don't worry, the values will converted properly!
  4. Highlight the rows and columns you want to convert and be sure the "Data Area" button is selected. Don't worry if you do not want all the columns, you will be able to drop them later.
  5. Then check the "Variable Name Rows" box. The data area you just highlighted will turn red. Highlight the row which contains your variable names. Click OK
  6. You will then be presented with a list of the variables and their types. Here you can change the names, types (character, number, date) and decide whether to keep or drop each variable. Once you have made your changes, click OK.
  7. You will then be presented with the Power Panel. The Power Panel gives you the ability to select rows (Record Filter and Equations), view the data, view the variable information, sort the data and view the values of a variable. Since most of this is easier in programs such as Stata or SPSS, we recommend skipping this step and just clicking OK. If you are converting a very large file (50 meg or more) into Stata, then you may want to use the "Shrink Variables" button under "Variable Information".
  8. You will then get another file selection box, this time to specify the output file. Select the directory in which you want the file to be created - be sure you have write access to the directory and enough space to hold the file!
  9. Select the type of file to which you want your spreadsheet converted. Be sure to select one that matches the version of software you will be using. For example, if you have SAS version 6.12 for windows, be sure to select this and not SAS version 8 or you will not be able to use the file. Click "Save" when you are done.
  10. You will then be shown the batch program that will be run. You should not need to make any changes to this file. You can also save the batch program for future use; this is really only necessary if you have made many changes to variable names, selected record filtering, etc. Click on "Do It!"
  11. DBMS/Copy will show you its progress as it converts the file. When it is done, you will be given a choice of converting another file or exiting.
This page last updated on: