Convert .DBF to excel

Posted on 2003-02-21
Medium Priority
Last Modified: 2010-05-19
Someone has given me a .dbf file that they want converted to excel. I've tried the file conversion wizard in excel but it was not the expected result. How do i go about converting to Excel. I have office 97 without Access but office 2000 with access
Question by:F_MERLIN
  • 2
LVL 44

Expert Comment

ID: 7992738

you could try this solution from Tim


he did it from vb we do it from excel :)

-first open the vb editor with alt+f11 in excel
-insert a new module
-paste the code

Sub ConvertDBF2Excel()

Dim appExcel As Excel.Application
Dim wbkDBF As Excel.Workbook
On Error Resume Next
Set appExcel = GetObject(,"Excel.Application")
If appExcel Is Nothing Then
 Set appExcel = CreateObject("Excel.Application")
End If
On Error Goto 0
Set wbkDBF = appExcel.Workbooks.Open("MyDBFFile.DBF")
wbkDBF.SaveAs "MyDBFFile.xls",xlWorkbookNormal
Set wbkDBF = Nothin
Set appExcel = Nothing

end Sub

-then change the path to your file
-now you can save
-and with F8 you can step through the code or run with F5

LVL 23

Expert Comment

ID: 7992913
Do you have a friend with Foxpro?  It can easily be opened in VFP and exported to XLS format.

Expert Comment

ID: 7994450
Reading Access or Excel (.XLS), dBase (.DBF) and other Spreadsheet-Type Files
SAS for Windows and SAS for the Macintosh can read spreadsheet-type file formats, including .XLS, .DBF and some .DIF file formats. .XLS is a format used in Excel and Access; .DBF is used in dBase and other software, and .DIF is a format used in Lotus and elsewhere. There are basically three ways to convert these types of files into SAS Data Sets: two easy ways (where the whole .DBF or .DIF file is converted as-is) and the relatively hard way (where you select portions of the files to be brought over into SAS). These methods are outlined below. Note that SAS/ACCESS to PC File Formats must be installed for these methods to work.
The "Import Wizard" and "Import Wizard" methods are probably the simplest method of doing these types of conversions. In Version 7 and later, PROC IMPORT and PROC EXPORT have been added to emulate the Import and Export Wizards.

For further documentation on PROC DBF and PROC DIF, as well as PROC IMPORT and PROC EXPORT, please see the SAS. OnlineDoc(tm) at:


then choose the "SAS. OnlineDoc(tm)" link.

For further documentation on the more customized approach (referred to as 'the hard way' above), see SAS/ACCESS Interface to PC File Formats, listed at the end of this document (and available in some User Areas), as well as the above-mentioned OnlineDoc(tm).

The "Import Wizard"

SAS for Windows and SAS for the Macintosh as of Release 6.12 have an "Import Wizard" that allows you to point-and-click your way through a simple process that imports .XLS, .DBF, .WK1, .WK3, and .WK4 files, as well as files with data delimited by commas and other characters. Click on File>Import and follow the instructions, if you have one of these types of files.

If you prefer to use a program to convert a dBase file to a SAS Data Set, run PROC DBF as in this example. (Running PROC DBF or PROC DIF under SAS for Windows requires that SAS/ACCESS software be installed.) We assume here that we have a dBase V file called "tommy.dbf" in the current directory, and the SAS data set called "tomsas" will be temporary.

proc dbf db5=tommy out=tomsas;

To convert a SAS Data Set into a dBase file, run PROC DBF as in the following example. We assume here that we have a permanent SAS Data Set called "medical" in a directory called "C:\mysas", and that we want to create a dBase IV file called "medicine.dbf" to be stored on the diskette in the A: drive.

libname oldsas 'c:\mysas';
filename newdbf 'a:\medicine.dbf';
proc dbf db4=newdbf data=oldsas.medical;

Traditionally SAS has been able to convert Lotus-created .DIF files into SAS data sets, but not all .DIF files are the same. More recent formats called .DIF (Data Interchange Format) and created by Excel or Microsoft Access may not work with PROC DIF. In these cases, the recommended workaround is to create a .DBF file or, if the correct Release of SAS is being used, create a .XLS file and use the "Import Wizard" (see above). When outputting a .DIF file from a SAS data set for use in spreadsheet (or other) software, it seems there are no problems like those found when trying to input .DIF to SAS. .DIF files created by SAS normally can be read in old Lotus or in new Microsoft Access and Excel versions. To convert a .DIF file to a SAS Data Set, run PROC DIF as in this example. (Running PROC DBF or PROC DIF under SAS for Windows requires that SAS/ACCESS software be installed.) We assume here that we have a .DIF file called "thomas.dif" in the current directory, and the SAS data set called "thomasas" will be temporary.

filename thom 'thomas.dif';
proc dif dif=thom out=thomasas;

To convert a SAS Data Set into a .DIF file, run PROC DIF as in the following example. We assume here that we have a permanent SAS Data Set called "medinfo" in a directory called "C:\mysas", and that we want to create a .DIF file called "medinfo.dif" to be stored on the diskette in the A: drive.

libname oldsas 'c:\mysas';
filename newdif 'a:\medinfo.dif';
proc dif dif=newdif data=oldsas.medinfo;

As you can see, PROC DIF works in the same way as the examples shown above for PROC DBF, although there are more choices you may need to make, including whether you want to specify a variable name prefix (the default is COLn, where "n" is the ordinal number in which the variable appears: COL1, COL2, etc.), whether you want to skip a row or rows at the beginning of the .DIF file, etc. The documentation noted above gives complete details.

Note that these examples use DOS file specifications. The same SAS statements will work in SAS for the Macintosh with Mac file specifications in place of the DOS examples.

SAS/ACCESS Interface to PC File Formats

If you prefer to customize your SAS Data Set and read in only certain variables from the .DBF or .DIF file, or change variable names or formats, for example, you can use SAS/ACCESS software. This is done by invoking ACCESS and by making choices and filling in fields in a series of SAS/ACCESS windows. Following is an outline (with notations) of a typical SAS/ACCESS session in which a .DBF file is converted into a SAS Data Set. The procedure for converting .DIF files is similar. In any case, this is just an overview, and only the very brave should attempt these operations without a manual.
To understand the process of converting a .DBF or .DIF file to a SAS Data Set (or a SAS View, if you wish), you should notice first that there are three major steps:

point SAS to the relevant input and output files
create an Access Descriptor to 'map' the .DBF or .DIF file
create a View Descriptor to describe the new SAS Data Set (or SAS View)

Here is a typical scenario for creating a customized SAS Data Set from an existing .DBF file:

If you wish to store the new SAS Data Set permanently, issue a LIBNAME statement to tell SAS where you want it stored


Start SAS/ACCESS either by clicking on Globals>Access>Access Database Files, or by running the following program:


The Access Window will appear, showing all SAS Data Sets available to your system at that moment.

Click File>New to create a new Access Descriptor
you will see the "New" window in which you enter the LIBREF for the directory where you want the Access Descriptor to be stored and the NAME you want it to have. An Access Descriptor is a SAS File that can be re-used in the future. Example:

               LIBREF     NAME      TYPE

Click OK.

Next you go to the Select Data Window, where you highlight ".DBF Files" and click OK.

Next is the ACCESS: Create Descriptor Window, where you must fill in the path to where the .DBF file is stored. This specification is really the path and the first part of the .dbf file name, not including the .dbf extension. Let's say your file is "C:\DBASE\TOCONVRT.DBF". In this window, you should see:


Next the list of variables found in the .DBF file is displayed. Here you can choose which variables you want to delete from the .DBF file (type 'D' in the 'Func' filed on the line where each variable is listed). You can also type new variable names under "SAS Names", if you want to change the original .DBF names. When you are finished modifying this window, click Locals>End.

Next, you are returned to the ACCESS: Create Descriptor Window, where you should click File>End to ask SAS to save the Descriptor and continue.

Now, in the main ACCESS Window, find the Access Descriptor you have just created and saved. It will be listed as

Type CV (Create View) on the line to the left of the name. This will bring up the View Descriptor Display Window, in which you specify both the name for the View Descriptor, and the name for the output SAS Data Set. Your window might look like this:

Descriptor: Library:  OLD       Member: DBFVIEW1   Type:  VIEW
Output SAS Data Set: Library:  OLD      Member:  DBF2SAS
      <variable listing, with Action fields, etc.>


Accepted Solution

ranjan01 earned 400 total points
ID: 7994471
please open open .dbf file in excel and save as .xls just follow this step

Open Excel.
Go to File on the menu bar and select Open.
Go to the "Files of type" drop down box, click the down arrow and select All Files (*.*) or dBase Files (*.dbf).
Go to the "Look In" drop down box, click the down arrow and select the directory in which you saved the file, then double click on the file name.
The DBF file will now open in Excel.
Instructions on how to create an Access database and import a DBF file
Click on the Start button, point the mouse at Programs, then go to Microsoft Access and click on it to open it.
Once in Access, click on Blank Database and select OK.
Go to the "Save as Type" drop down box and click the down arrow, then click on Microsoft Access Databases (*.mdb).
Click Create. This will open a Blank Document.
Right click on the "Tables" tab and select Import.
Go to the  "Files of Type" drop down box, click on the down arrow and select dBaseIII (*.dbf)
Go to the "Look In" drop down box, click the down arrow and select the directory that contains the DBF file you want to open..
Double click on the file name you want to open.

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question