Convert from Excel 2007 to dbase (DBF)

damianodec · Post by **damianodec** » Tue Apr 08, 2008 10:45 am

Hallo, I have a file excel (from excel 2007) and I want to conver to .DBF !
By Excel 2003 there is the option "Save in Dbase", but in Excel 2007 there is'nt that options!
Can anyone help me please?

xProgrammer · Post by **xProgrammer** » Tue Apr 08, 2008 12:04 pm

I don't have Excel 2007 to test but it can probably convert to a format that xBase can convert from such as comma delimited or sdf. (Check the APPEND FROM command)

xProgrammer

Rick Lipkin · Post by **Rick Lipkin** » Tue Apr 08, 2008 12:05 pm

Here is a code snipit I use.. I do not know if it will work on XLSx

Rick Lipkin
SC Dept of Health, USA

//------------------------------

cPATH := "C:\TEMP\MYFILE.XLS"

DO WHILE .T.
SITEDBF := "TEMP"+(SUBSTR(TIME(),7,2)+SUBSTR(TIME(),4,2))+".DBF"
IF .not. FILE( xVOL+"\DBTMP\"+SITEDBF )
EXIT
ENDIF
ENDDO

DBF_STRU := { }
AADD( DBF_STRU, { "SSN", "C", 11, 0 } )
AADD( DBF_STRU, { "NAME", "C", 30, 0 } )
AADD( DBF_STRU, { "ACT_TYPE", "C", 1, 0 } )
AADD( DBF_STRU, { "EMP_STAT", "C", 1, 0 } )
AADD( DBF_STRU, { "TOFROM", "C", 4, 0 } )
AADD( DBF_STRU, { "COSTCTR", "C", 6, 0 } )
AADD( DBF_STRU, { "FUND", "C", 6, 0 } )
AADD( DBF_STRU, { "RATE", "N", 10, 2 } )
AADD( DBF_STRU, { "SEMI", "N", 10, 2 } )
AADD( DBF_STRU, { "FY", "C", 4, 2 } )
AADD( DBF_STRU, { "COMMENTS", "C", 50, 0 } )
AADD( DBF_STRU, { "APPROVAL", "C", 1, 0 } )
AADD( DBF_STRU, { "SALARY", "N", 7, 0 } )
AADD( DBF_STRU, { "P0701", "C", 1, 0 } )
AADD( DBF_STRU, { "P0716", "C", 1, 0 } )
AADD( DBF_STRU, { "P0801", "C", 1, 0 } )
AADD( DBF_STRU, { "P0816", "C", 1, 0 } )
AADD( DBF_STRU, { "P0901", "C", 1, 0 } )
AADD( DBF_STRU, { "P0916", "C", 1, 0 } )
AADD( DBF_STRU, { "P1001", "C", 1, 0 } )
AADD( DBF_STRU, { "P1016", "C", 1, 0 } )
AADD( DBF_STRU, { "P1101", "C", 1, 0 } )
AADD( DBF_STRU, { "P1116", "C", 1, 0 } )
AADD( DBF_STRU, { "P1201", "C", 1, 0 } )
AADD( DBF_STRU, { "P1216", "C", 1, 0 } )
AADD( DBF_STRU, { "P0101", "C", 1, 0 } )
AADD( DBF_STRU, { "P0116", "C", 1, 0 } )
AADD( DBF_STRU, { "P0201", "C", 1, 0 } )
AADD( DBF_STRU, { "P0216", "C", 1, 0 } )
AADD( DBF_STRU, { "P0301", "C", 1, 0 } )
AADD( DBF_STRU, { "P0316", "C", 1, 0 } )
AADD( DBF_STRU, { "P0401", "C", 1, 0 } )
AADD( DBF_STRU, { "P0416", "C", 1, 0 } )
AADD( DBF_STRU, { "P0501", "C", 1, 0 } )
AADD( DBF_STRU, { "P0516", "C", 1, 0 } )
AADD( DBF_STRU, { "P0601", "C", 1, 0 } )
AADD( DBF_STRU, { "P0616", "C", 1, 0 } )

DBCREATE( xVOL+"\DBTMP\"+SITEDBF, DBF_STRU )

SELECT 9
IF NETUSE( xVOL+"\DBTMP\"+SITEDBF, .T.,5)
ELSE
SELECT( GOBACK )
_Cleanup()
RETURN(.F.)
ENDIF

nREC := 0

cSAY := "Opening Excel File "+STR(nREC)
oSAY:ReFresh()
SysReFresh()

oEXCEL := TOleAuto():New( "Excel.Application" )
oExcel:Workbooks:Open( cPATH )

oBook := oExcel:Get( "ActiveSheet" )
nROWS := oBook:UsedRange:Rows:Count()
nCOLS := oBook:UsedRange:Columns:Count()

SELECT 9
FOR nROW := 3 to nROWS
APPEND BLANK
FOR nCOL := 1 to nCOLS
xVALUE := oBook:Cells( nROW, nCOL):Value
FieldPut( nCol,xValue )

nREC++
cSAY := "Opening Excel File "+STR(nREC)
oSAY:ReFresh()
SysReFresh()

NEXT
NEXT

SELECT 9
dbCommit()

Gale FORd · Post by **Gale FORd** » Tue Apr 08, 2008 1:29 pm

The problem might be a Microsoft Security Update. We use a number of formats in Excel and all at once people could not open or save in .dbf, .xml and others. There are registry settings that can be added to allow more file extensions.

I have a .reg file for 2003 but you might be able to use it as an example.

Otto · Post by **Otto** » Tue Apr 08, 2008 6:11 pm

In the online help of Office 2007 dBase is not mentioned anymore.
Maybe it is not supported.
Regards,
Otto

xProgrammer · Post by **xProgrammer** » Tue Apr 08, 2008 9:06 pm

Hi all

> Maybe it is not supported.

In which case the question becomes what external formats does it support, and which of them is easiest to transform into xBase, sdf and comma delimited being the first two you would look for. xml format would be OK but would require programming.

The alternative is Rick's approach and access the data directly from Excel.

All this presupposes that Excel 2007 can't save in Excel 2003 format and then use Excel 2003 to do the conversion. Has anyone looked into that?

Regards
xProgrammer

Otto · Post by **Otto** » Tue Apr 08, 2008 9:31 pm

That’s the way I do it. But you need both versions of Office and it is time consuming.
I have to go to another PC.
In the doc’s - help file -I read that if you don’t find your file format you should consulate 3rd party companies.

Regards,
Otto

Gale FORd · Post by **Gale FORd** » Tue Apr 08, 2008 9:34 pm

Check out this link

http://office.microsoft.com/en-us/excel ... aseFormats

****** Other formats you can open but not save *******
Format Extension Description
DBF 3, DBF 4 .dbf dBase III and IV. You can open these files formats in Excel, but you cannot save an Excel file to dBase format.
Quattro Pro 5.0 (Win) .wb1 Quattro Pro version 5.0 for Windows. You can open Quattro Pro files in Excel by using a converter. You cannot save an Excel file to Quattro Pro format.
Quattro Pro 7.0 (Win) .wb3 Quattro Pro version 7.0 for Windows. You can open Quattro Pro files in Excel by using a converter. You cannot save an Excel file to Quattro Pro format.

*** File formats that are not supported in Excel

The following file formats are no longer supported in Office Excel 2007. You cannot open or save files in these file formats.

Format Extension Clipboard type identifiers
Excel Chart .xlc Excel 2.0, 3.0, and 2.x file formats
WK1, FMT, WK2, WK3, FM3, WK4 .wk1, .wk2, .wk3, .wk4, .wks Lotus 1-2-3 file formats (all versions)
Microsoft Works .wks Microsoft Works file format (all versions)
WQ1 .wq1 Quatro Pro for MS-DOS file format
DBF 2 .dbf DBASE II file format

Tips for file formats that are not supported by Excel
If a file format that you want to use is not supported in Excel, you can try the following:

Search the World Wide Web (WWW) for a company that makes file format converters for file formats that are not supported in Excel.
Save to a file format that another program supports. For example, you may want to import your spreadsheet into another program that does not support the Excel file format. But the other program may be able to import another supported file format, such as an XML spreadsheet or a text file format. In this case, you can save your workbook to the XML spreadsheet format, and then from the other program, import the XML file.

xProgrammer · Post by **xProgrammer** » Tue Apr 08, 2008 9:50 pm

Hi all

> *** File formats that are not supported in Excel

They don't even support their own old file formats!!! (And people keep on buying software from these sharks! Why?)

There's a lesson there, I believe, but people seem reluctant to learn from it. Storing data in proprietary binary formats is not good practice.

I just looked at OpenOffice (free, runs on Windows and a real OS - Linux) and it will save in dBase (xBase) format. And it will open Excel spreadsheets. There's a ready made solution.

Regards
xProgrammer

Roger Seiler · Post by **Roger Seiler** » Wed Apr 09, 2008 12:56 pm

Another thought about OpenOffice.org...

* It is a freely distributable open-source alternative to MS Office that is fully compatible with Office files.

* We can distribute it gratis to all of our customers. We are even allowed to charge for providing our own support/addons.

Take a look at: http:\\www.openoffice.org

So if you want to make your own Declaration of Independence, here is your chance.