How can I import an xls file into a dbf

[DonnyKing]

I have tried a generic routine (shared app from another forum) to import an Excel spreadsheet content into a Visual dBase (7.01) table. I keep getting an error that the table is not found. However, I know it hits the xls file because it has locked the xls file on a few occasions.

I'm trying to do this in a UI format. Does anyone have a routine that works in performing this operation? I know it is challenging to perform this.

 

[dgrewe]

The command is

APPEND FROM FileName | ?[FIELDS FieldList] [FOR lExpression][[TYPE] [DELIMITED [WITH Delimiter | WITH BLANK | WITH TAB WITH CHARACTER Delimiter] | DIF | FW2 | MOD | PDOX | RPD | SDF | SYLK | WK1 | WK3 | WKS | WR1 | WRK | CSV | XLS | XL5 [SHEET cSheetName] | XL8 [SHEET cSheetName]]] [AS nCodePage]

I have found it cleaner to export the spreadsheet to a CVS or SDF file and then append from the exported file. Microsoft does not alway play by it's own rules and appending from XLS sometimes brings in a unwanted M$ junk.




David W. Grewe Dave

 

[JCreamerII]

Donny,

As David mentioned MS xls files don't always work cleanly on the export side. I still try to convert to a dbf extension and clean it up. It depends on the number of fields that I have to work with, and if the structures are identical on the receiving side. Usually I just dump to a dbf and clean up the structure & data in the output file. If you have a large number of fields to append the process of matching up the structure can be a pain. What I usually do is change the headings to match the field names and to save teh data out as a dbf file. Then clean up the data then the structure. Some times dates & numnerics don't convert properly going to a dbf format. I'll clean them up before I append into the receiving file. If you do enough of these type conversions you get a sense of the best way to go for any one convert.

Jim C.

 

[DonnyKing]

DG & JC - much appreciated for the well informed feedback. Its been several years since I've parsed comma delimited files within dbf apps so I'm brushing (or brusing) up and trying to avoid that scene here all together.

What I have are 6 similar but different xls sheets that currently come to me and they usually have header and data errors. I have to spend many hours a month manually cleaning these things up B/4 sending for auditing and processing - hence, I told myself I can automate this stuff and have my app check, edit and output exceptions & "errors corrected" reports.

The next step is having the app take the "appended into" dbf and output the data into a new, polished xls file. Having said that (and bear with me please...) I have the 6 dbfs built with the xls "correct" headers as field names and field properties set to the correct char and Numeric settings. So, with the code DG shared, I'm trying to get the correct syntax to use...

Let's say my dbf field names are Acct, Name, Addr, Ltr_Date, Int_RTE and the xls I convert to a CVS is now called New_Rate.CVS and I remove the header names, so just straight data will append. How would I modify the code you shared to use this "New_Rate.CVS" (or SDF)???

(I know to replace "filename" with it? Would either of you mind sending me the code "modified" so this example layout will work in it? Of course and unfortunately the real xls files have 26 to 30 columns but I will tackle that later...

Much appreciated! I have lots of VDB tricks and success code to share on these threads...
Donny

 

[dgrewe]

Assuming you have the Structure of the tables already defined from previous spreadsheets already then the commands are.

APPEND FROM filename TYPE CSV
or
APPEND FROM filename TYPE SDF

HOWEVER,
Since you have Excel, the safest way to do whay you want is to open the file in Excel, Save it as a DBF.
then it is

USE filename



David W. Grewe Dave

 

[DonnyKing]

Thank you Dave... I used to append comma delimited txt files into dbfs and parse them out into secondary, structured dbfs more than I care to remember... I never appended CSV or SDA files though.

I keep getting an "unauthorized phrase CSV" error. My test dbf has all the field names and structures to match the xls columns. In Excel, I save the xls as a CSV format file. Here's what I do in dBase 7.01:
clear
\\ Directory program path
set path to "c:\cch RR editor\"
use test1
zap
append from test5.csv type CSV
// this is where I get that error and can't figure it out...

The original xls file has the usual suspects; acct, name, zip code, rate1, rate2 etc... nothing special AND xls headers are removed so just raw data in it when I save as a csv format.

Or - as you suggested, if I can save the xls as a dbf - great! However, I'm using MS Office 2006. I open the xls and save as xls 97 - 2003 xls format. Then I save it as a csv. Problem is - my Excel does not have a dbf format to save the xls as that. Is there a trick in Excel to saving an xls file as a dbf?

Many thanks and sorry for the redundancy... I know VDB well, just can't get past this csv error and/or cannot figure out how to save my xls file as a dbf???

Donny King

 

[DonnyKing]

Dave - I figured out the Excel 2006 challenge. It doesn't offer the dbase save as option... so, I used my wife's MS Office 2003 Excel and there it was!!! It works in what I'm doing... Thx again for that signal to try...

Had I know this defincency in my Office 2006 - I would have been done with the UI programming by now! I also have a version of Excel 2003 at work but this project is being done afterhours at home...

Do you know of any tricks in Excel 2006 to get that save as .dbf feature as an add-on?

My next task is after my app cleans up the data and creating exception reports... is to have the app save the cleaned dbf back as an xls... any tips on that next challenge?

I remember doing it in the report function of VDB 7.01 some years ago... if I recall correctly. Any insight to that direction is greatly appreciated!

Donny King

 

[dgrewe]

Microsoft has gone to XHTML format for all their files in office for 2006.

You can export it to a HTML file and write a program that reads the file as a low level file using Fopen(), Fread() Fclose() and breaks out the rows and columns.

Something Like this

LPARAMETERS pcDbfName,pcFilename,pnFieldCnt
LOCAL laArray,lnCounter,lnHandle
STORE 0 TO lnHandle,lnCounter
DIMENSION laArray[pnFieldCnt]

SET STEP ON

IF FILE(pcFilename)
	lnHandle=FOPEN(pcFilename,10)
ENDIF

IF lnHandle > 0

	USE (pcDbfName) ALIAS INDBF EXCLUSIVE
	lnCounter=1
	DO WHILE NOT FEOF(lnHandle)
		lcString = ALLTRIM(UPPER(FGETS(lnHandle)))
		DO WHILE .T.
			lnLoops=0
			IF CHR(13) $ lcString
				lcString = ALLTRIM(STRTRAN(lcString,CHR(13),""))
				lnLoops=lnLoops+1
			ENDIF
			
			IF CHR(10) $ lcString
				lcString = ALLTRIM(STRTRAN(lcString,CHR(10),""))
				lnLoops=lnLoops+1
			ENDIF

			IF " " $ lcString THEN
				lcString=ALLTRIM(STRTRAN(lcString," ",""))
				lnLoops=lnLoops+1
			ENDIF

			IF "<BR>" $ lcString THEN
				lcString=ALLTRIM(STRTRAN(lcString,"<BR>",""))
				lnLoops=lnLoops+1
			ENDIF

			IF "<TR>" $ lcString THEN
				lcString=ALLTRIM(STRTRAN(lcString,"<TR>",""))
				APPEND BLANK
				lnCounter = 1
				lnLoops=lnLoops+1
			ENDIF
			
			IF "<TD>" $ lcString AND "</TD>" $ lcString
				laArray[lnCounter]=''
				lnStart=AT("<TD>",lcString,1)
				lnEnded=AT("</TD>",lcString,1)
				lcsubString=ALLTRIM(SUBSTR(lcString,lnStart,lnEnded-lnStart+5))
				lcString=ALLTRIM(STRTRAN(lcString,lcSubString,''))
				lcSubString=ALLTRIM(STRTRAN(lcSubString,"<TD>"))
				lcSubString=ALLTRIM(STRTRAN(lcSubString,"</TD>"))
				laArray[lnCounter]=lcSubString
				lnCounter=lnCounter+1
				LOOP
			ENDIF

			IF  "<TD>" $ lcString THEN
				laArray[lnCounter]=''
				lcString=ALLTRIM(STRTRAN(lcString,"<TD>",""))
				LOOP
			ENDIF

			IF "</TD>" $ lcString  THEN
				lcString=ALLTRIM(STRTRAN(lcString,"</TD>",""))
				laArray[lnCounter]=laArray[lnCounter]+lcString
				lcString=''
				lnCounter=lnCounter+1
				LOOP
			ENDIF

			IF "</TR>"$lcString THEN
				lcString=ALLTRIM(STRTRAN(lcString,"</TR>",""))
				GATHER FROM laArray MEMO
				lnCounter=1
				LOOP
			ENDIF

			IF lnLoops=0 AND lnCounter>1 AND !EMPTY(lcString)
				laArray[lnCounter]=laArray[lnCounter]+lcString
				lcString=''
				LOOP
			ENDIF

			IF EMPTY(lcString) THEN
				lnLoops=0
			ENDIF

			IF lnLoops=0 THEN
				EXIT
			ENDIF
		ENDDO
*
	ENDDO
	=FCLOSE(lnHandle)
	BROWSE
ENDIF

David W. Grewe Dave

 

[dgrewe]

For taking the data from a dbf to a html you can use the code

*/***************************************************************************
*# Create a HTML file from a DBF
*/Program   : dbf_Html
*/System    : Fox Library
*/Purpose   : Create a Html File from a Dbf File.
*/Syntax    : = Dbf_Html(inputfile , outputfile)
*/Returns   :
*/Parameter :
*/Defaults  :
*/Requires  :
*/Changes   :
*/Calls     :
*/Version   : 1.0
*/Dated     : 08/11/2000
*/Written By: David W. Grewe
*/***************************************************************************
*& Utility - HTML maintenance
*/***************************************************************************
*/ Record Of Change
*/
*/***************************************************************************
*/***************************************************************************
*/ Program Notes
*/ WARNING - DO NOT run this program on a table with a Logical or general field
*/***************************************************************************
PARAMETER pcInFile , pcOutFile , pcTitle

*set step on

DO CASE
CASE PARAMETERS() < 1.5
	RETURN -10

CASE !FILE(pcInFile)
	RETURN -10

CASE PARAMETERS() < 2.5 .OR. VARTYPE(pcTitle) <> "C" OR EMPTY(pcTitle)
	pcTitle = JUSTSTEM(pcInFile)
ENDCASE
*

PRIVATE lnOFile , lcFont1 , lcFont2 , lnLenDbf
lcFont1 = ' BGCOLOR="#FFFFFF" '
lcFont2 = ' BGCOLOR="#FFFFFF" '
lnSelect = SELECT()
lnLenDbf = 0
IF Dbf_Open(pcInFile , "DBF2HTML",.T.) < 10
	RETURN -5
ENDIF
DIMENSION laArray(FCOUNT())
lnFields = AFIELDS(laArray)
*
lnOFile = Asc_Make(pcOutFile)
*
=FPUTS(lnOFile , '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Final//EN">')
=FPUTS(lnOFile , '<HTML>')
=FPUTS(lnOFile , '<HEAD>')
=FPUTS(lnOFile , '<TITLE>'+pcTitle+'</TITLE>')
=FPUTS(lnOFile , '</HEAD>')
=FPUTS(lnOFile , '<BODY>')
*
=FPUTS(lnOFile , '<TABLE WIDTH="100%" BORDER="1" ><TR>')
FOR lnField = 1 TO lnFields
	=FPUTS(lnOFile , '<TH>' + laArray[lnField , 1] + '</TH>')
ENDFOR
=FPUTS(lnOFile , '</TR>')
*
GOTO TOP
SCAN ALL
	=FPUTS(lnOFile , '<TR>')
	llEven=IIF(MOD(RECNO() , 2) = 0 , .T. , .F.)
	FOR lnField = 1 TO lnFields
		DO CASE
		CASE laArray(lnField,2) = "G"
		CASE laArray(lnField,2) = "L"
			=FPUTS(lnOFile , IIF(EVALUATE(FIELD(lnField)) = .T.,'<TD>Y</TD>','<TD>N</TD>'))
		OTHERWISE
			=FPUTS(lnOFile , '<TD>'+ ALLTRIM(TRANSFORM(EVAL(FIELD(lnField))))+'</TD>')
		ENDCASE
	ENDFOR
	=FPUTS(lnOFile , '</TR>')
ENDSCAN
*
=FPUTS(lnOFile , '</TABLE>')
=FPUTS(lnOFile , '</BODY>')
=FPUTS(lnOFile , '</HTML>')
=FCLOSE(lnOFile)
*
USE
SELECT  (lnSelect)
RETURN  lnOFile

David W. Grewe Dave

 

[DonnyKing]

Excellent Dave! I have good success in the Excel 04 save to .dbf

However, I will try your Excell 2007 import and export. The more I can do within the application - the better... There will be a number of people using my UI. The less manual file conversion - the less chance for user errors.

I genuinely appreciate all of your support in this. BTW - I began and completed a lot of the UI including the drop-downs and getfile(), savefile() routines set up. Its been about 3 years since I've used my VDB 7 but its coming back.

One more question, on the UI form, I also created a basic menu (.mnu) and in the form properties linked the menu - and all is "active/true" But when I run the form, the menu does not show on it??? Do I need to compile the two first, rather than a simple save first?

I looked at a complex app I wrote some years ago and for some reason I used "&File" on the first menu section. It worked then (and still does on that old app) but when I tried it on this new app - got errors??? I'm looking for my old program text versions to see what's different...

Donny King

 

[dgrewe]

Well, here is where the truth comes out. I do not use Dbase, I use VFP. But since they are very similiar most of the code works. I do not know how Dbase does it's menus, but in VFP, you can only use a Menu in a Top Level form. VFP have 3 form levels, Top Level, In Screen and in Window. In the menu builder you have to select a check box that tells the system it will be in a top level form and the builder will add code to make it work. Then in the init section of the form you call the menu.

Maybe VDB is the same there also.



David W. Grewe Dave

 

[DonnyKing]

Thx Dave - I hear the two are similar as well. I was linking the .mnu correctly... and had MDI = false (which its supposed to be).

So, I set it to True and saved... and still no menu. Then I set MDI to false again, saved and then it came up! I think my VDB is buggy - time to uninstall/reinstall...

In any event, I was doing it correctly all along. Its a strong language and punches envelopes other "and some new languages" can't reach. That's why I still use it...

BTW - my UI is coming along really clean and user friendly...

Thx again Dave!

Donny King

 

[dgrewe]

Found this in the VFP forum, Thought you could use it

FAQ184-4704: Export a Formatted Table to Excel using HTML


David W. Grewe Dave

 

[DonnyKing]

Excellent information Dave! Thank you again for your digence and sharing. BTW - I've got a lot done on the UI... nearly done (well, they're never really, ever done LOL)!