Link to home
Start Free TrialLog in
Avatar of F_MERLIN
F_MERLIN

asked on

Convert .DBF to excel

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
Avatar of Brian Mulder
Brian Mulder
Flag of Netherlands image

Hello F_MERLIN,

you could try this solution from Tim

https://www.experts-exchange.com/questions/20276377/dbf-to-excel.html

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
wbkDBF.Close
Set wbkDBF = Nothin
appExcel.Quit
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

HAGD:O)Bruintje
Avatar of slink9
slink9

Do you have a friend with Foxpro?  It can easily be opened in VFP and exported to XLS format.
hi
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:


http://www.usc.edu/isd/doc/statistics/sas/


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.
PROC DBF and PROC DIF

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;
run;


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;
run;


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;
run;


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;
run;


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

LIBNAME XYZSAS 'C:\';

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

PROC ACCESS;
RUN;

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
Access Name:   XYZSAS . DBFTEST1 . ACCESS

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:

Path: C:\DBASE\TOCONVRT

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

___ XYZSAS DBFTEST1 ACCESS
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
Path:  C:\DBASE\TOCONVRT.DBF
       
      <variable listing, with Action fields, etc.>

ASKER CERTIFIED SOLUTION
Avatar of ranjan01
ranjan01

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial