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 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.
- 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".
- 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.
- 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!
- 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.
- 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
- 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.
- 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".
- 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!
- 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
- 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!"
- 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.