?
Solved

Convert .DBF to excel

Posted on 2003-02-21
4
Medium Priority
?
13,549 Views
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
0
Comment
Question by:F_MERLIN
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 7992738
Hello F_MERLIN,

you could try this solution from Tim

http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20276377.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
0
 
LVL 23

Expert Comment

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

Expert Comment

by:ranjan01
ID: 7994450
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.>

0
 
LVL 3

Accepted Solution

by:
ranjan01 earned 400 total points
ID: 7994471
hello
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.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

765 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