How can I import an xls file into a dbf
How can I import an xls file into a dbf
(OP)
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.
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.
RE: How can I import an xls file into a dbf
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
RE: How can I import an xls file into a dbf
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.
RE: How can I import an xls file into a dbf
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
RE: How can I import an xls file into a dbf
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
RE: How can I import an xls file into a dbf
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
RE: How can I import an xls file into a dbf
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
RE: How can I import an xls file into a dbf
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
CODE
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
RE: How can I import an xls file into a dbf
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
RE: How can I import an xls file into a 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
RE: How can I import an xls file into a dbf
Maybe VDB is the same there also.
David W. Grewe Dave
RE: How can I import an xls file into a dbf
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
RE: How can I import an xls file into a dbf
FAQ184-4704: Export a Formatted Table to Excel using HTML: Export a Formatted Table to Excel using HTML
David W. Grewe Dave
RE: How can I import an xls file into a dbf