×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How can I import an xls file into a dbf

thread290-1467055

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.

RE: How can I import an xls file into a dbf

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
 

RE: How can I import an xls file into a dbf

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.
 

RE: How can I import an xls file into a dbf

(OP)
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
 

RE: How can I import an xls file into a dbf

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
 

RE: How can I import an xls file into a dbf

(OP)
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

RE: How can I import an xls file into a dbf

(OP)
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

RE: How can I import an xls file into a dbf

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

CODE

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
 

RE: How can I import an xls file into a dbf

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

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

(OP)
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

RE: How can I import an xls file into a dbf

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
 

RE: How can I import an xls file into a dbf

(OP)
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

RE: How can I import an xls file into a dbf

(OP)
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)!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close