EXCEL/CSV to DBF, a few questions

Post Reply
George
Posts: 726
Joined: Tue Oct 18, 2005 6:49 pm

EXCEL/CSV to DBF, a few questions

Post by George »

Hi Forum,
I am kindly requesting your help for those who have experience importing Excel file and/or CSV to DBF (or ADS) tables:
    1. What is the fastest method to import Excel or CSV data to a DBF file?
    2. What is the class suggested (or ActiveX) to import an Excel file to DBF without having MS-Excel installed in user's computer?
    3. It's possible to import a CSV file to DBF starting from an specified line number in the CSV source file?

Thanks in advance for your help and suggestions.

Regards,


George
User avatar
Roger Seiler
Posts: 223
Joined: Thu Dec 01, 2005 3:34 pm
Location: Nyack, New York, USA
Contact:

Re: EXCEL/CSV to DBF, a few questions

Post by Roger Seiler »

If you mean from within an FWH program, I haven't done that. But I have often used the Excel program to manually export Excel data to a dbf, using "Save As" and then selecting dBase III as the output format. When doing this, I usually have to first modify column headers to valid characters, eliminating spaces, and limiting heading to 10 chars. Also often have to modify column widths. Then I just access the exported dbf with my FWH program.
User avatar
anserkk
Posts: 1333
Joined: Fri Jun 13, 2008 11:04 am
Location: Kochi, India
Has thanked: 2 times

Re: EXCEL/CSV to DBF, a few questions

Post by anserkk »

George wrote:Hi Forum,
2. What is the class suggested (or ActiveX) to import an Excel file to DBF without having MS-Excel installed in user's computer?
George

The Class FileXLS does not require MS-Excel to be installed in user's computer. If I am not wrong, the downside is that it will create Excel file in Excel 97 format only and not the latest excel file formats. It looks like that the class is not updated for a very long time.

Another option to create excel file without requiring MS-Excel installed on the PC is LibXL. Its NOT a freeware. :( You just need their LibXL.dll along with your application exe folder(similar to FastReport). It creates Excel file in latest formats.

Hope the information is useful for you. :D

Regards

Anser
kok joek hoa
Posts: 117
Joined: Tue Jan 03, 2006 6:18 pm

Re: EXCEL/CSV to DBF, a few questions

Post by kok joek hoa »

Hi George

Here a sample using ADS import CSV to DBF and very fast.

every first column of CSV must "DATA"

Code: Select all | Expand

static function import_data_cutomer_dari_excel(oBrw)    local cfilename :=  cGetFile('*.csv' ,'Import Data' )    local otextfile    local cline :=''    LOCAL ADATA :={}    LOCAL NCTL :=0    LOCAL NTEMP_INDEX :=0    LOCAL NTEMP_INDEX_SALES := 0        otextfile:=ttxtfile():new(cfilename)    IF OTEXTFILE:SEEK('DATA')        for nctl:=1 to  otextfile:NTLINES()            cline := otextfile:readline()            IF upper(ALLTRIM(StrToken(cline, 1 ,';' )))=='DATA'                AADD(ADATA,{    StrToken(cline, 1 ,';' )            ,;    // DATA                                StrToken(cline, 2 ,';' )            ,;    //KODE CUSTOMER                                StrToken(cline, 3 ,';' )            ,;    //STATUS PERUSAHAAN PT /CV /LTD DLL                                StrToken(cline, 4 ,';' )            ,;    // NAMA CUSTOMER                                StrToken(cline, 5 ,';' )            ,;    // ALAMAT                                StrToken(cline, 6 ,';' )            ,;    // KOTA                                StrToken(cline, 7 ,';' )            ,;    // NPWP                                StrToken(cline, 8 ,';' )            ,;    // KODE POS                                StrToken(cline, 9 ,';' )            ,;    // CONTACK PERSON                                StrToken(cline, 10 ,';' )           ,;    // JABATAN KONTAK                                StrToken(cline, 11 ,';' )           ,;    // TELP                                StrToken(cline, 12 ,';' )           ,;    // FAK                                StrToken(cline, 13 ,';' )           ,;    // EMAIL                                StrToken(cline, 14 ,';' )           ,;    // WILAYAH                                StrToken(cline, 15 ,';' )           ,;    // KETERANGAN                                StrToken(cline, 16 ,';' )           ,;    // KODE SALES                                StrToken(cline, 17 ,';' )            ;    // NAMA SALES                            };                    )                           endif               otextfile:advance()        next        OTEXTFILE:CLOSE()                dbselectarea(AALIAS[DB_SALESMAN])        NTEMP_INDEX_SALES := INDEXORD()        SET ORDER TO TAG KODE                DBSELECTAREA(AALIAS[DB_CUSTOMER])        NTEMP_INDEX := INDEXORD()        SET ORDER TO TAG KODE            TRY                 AdsBeginTransaction()                    FOR NCTL =1 TO LEN(ADATA)                DBSELECTAREA(AALIAS[DB_CUSTOMER])                   SEEK LEFT(ADATA[NCTL,2],6)                IF FOUND()                    MSGALERT('Kode : '+alltrim(ADATA[NCTL,2]) +' sudah ada')                else                                            if flock()                        append blank                        replace kode        with LEFT(ALLTRIM(ADATA[NCTL,2]),6)                        replace titel       with ALLTRIM(ADATA[NCTL,3])                        replace nama        with ALLTRIM(ADATA[NCTL,4])                        replace alamat      with ALLTRIM(ADATA[NCTL,5])                        replace kota        with ALLTRIM(ADATA[NCTL,6])                        replace npwp        with ALLTRIM(ADATA[NCTL,7])                        replace zipcode     with ALLTRIM(ADATA[NCTL,8])                                                // ALAMAT NPWP DISAMAKAN                        replace nalamat     with ALLTRIM(ADATA[NCTL,5])                        replace nkota       with ALLTRIM(ADATA[NCTL,6])                        replace nzipcode    with ALLTRIM(ADATA[NCTL,8])                        //----alamat npwp                                                                        replace kontak      with ALLTRIM(ADATA[NCTL,9])                        replace jabatan     with ALLTRIM(ADATA[NCTL,10])                        replace phone       with ALLTRIM(ADATA[NCTL,11])                        replace phone3      with ALLTRIM(ADATA[NCTL,12])                        replace email       with ALLTRIM(ADATA[NCTL,13])                        replace wilayah     with ALLTRIM(ADATA[NCTL,14])                        replace catatan     with ALLTRIM(ADATA[NCTL,15])                        replace kodesales   with LEFT( ALLTRIM(ADATA[NCTL,16]) ,10)  // PANJANG KODE SALES 10                                            ELSE                        BREAK                    ENDIF                                    dbselectarea(AALIAS[DB_SALESMAN])                    SEEK LEFT( ADATA[NCTL,16] ,10)                    IF !FOUND()                        IF FLOCK()                            append blank                            REPLACE SL_KODE   WITH LEFT( ADATA[NCTL,16] ,10)                            REPLACE SL_NAMA   WITH LEFT( ADATA[NCTL,17] ,35)                        ELSE                            BREAK                        ENDIF                    ENDIF                                   endif            NEXT            AdsCommitTransaction()            dbunlockall()        CATCH            AdsRollback()            dbunlockall()            msgstop('Proses Import Gagal!!!')            dbselectarea(AALIAS[DB_SALESMAN])            SET ORDER TO NTEMP_INDEX_SALES                        DBSELECTAREA(AALIAS[DB_CUSTOMER])            SET ORDER TO    NTEMP_INDEX            return nil        END                     ELSE        OTEXTFILE:CLOSE()        MSGALERT('Proses dibatalkan!!')        RETURN NIL    endif               dbselectarea(AALIAS[DB_SALESMAN])    SET ORDER TO NTEMP_INDEX_SALES        DBSELECTAREA(AALIAS[DB_CUSTOMER])    SET ORDER TO    NTEMP_INDEX    oBrw:REFRESH()        MSGINFO('Proses Complate')return nil 



Regards,
Kok
George
Posts: 726
Joined: Tue Oct 18, 2005 6:49 pm

Re: EXCEL/CSV to DBF, a few questions

Post by George »

Thanks for sharing your experience regarding the subject.
After some testing I think the best is to use CSV instead of Excel file, as MS-Excel since 2007 version is not supporting the DBF file anymore.
Using the below code, (together with a dialog with a METER control) in FWH 64 bits, my program is loading approx. 2200 records per second into an ADS table with 34 fields.

Code: Select all | Expand

 FUNCTION AddingCSV2ADS(nPercent1, oMeter, oDlg1, nTotalRecords, cFile, cTitle, nDivisor)    APPEND FROM &cFile WHILE (nTotalRecords++,;                    nPercent1++,;                    oMeter:Set(nPercent1),;                    IIF(nPercent1/nDivisor = int(nPercent1/nDivisor),;                        (oDlg1:cTitle := cTitle + "; Records Loaded: " +;                         alltrim(str(nTotalRecords)), oDlg1:Update(),;                         SysRefresh()),  ), TRUE ) ;                         DELIMITED WITH ({ '"', "," })RETURN NIL


Regards,

George
User avatar
Roger Seiler
Posts: 223
Joined: Thu Dec 01, 2005 3:34 pm
Location: Nyack, New York, USA
Contact:

Re: EXCEL/CSV to DBF, a few questions

Post by Roger Seiler »

Though the current version of MS-Excel won't "Save As" to dBase anymore, the current version of OpenOffice.org will - and it is free. OO.o also can export a Word file to PDF, which is handy. Though I have MS-Office, I also use OpenOffice.org and recommend it. You can even distribute it to your customers for free as an add-on, and they may appreciate the big savings over MS-Office.
- Roger
George
Posts: 726
Joined: Tue Oct 18, 2005 6:49 pm

Re: EXCEL/CSV to DBF, a few questions

Post by George »

I agree Roger.
OpenOffice is a great product and manage DBF file flawless.

Regards,


George
User avatar
ukoenig
Posts: 4043
Joined: Wed Dec 19, 2007 6:40 pm
Location: Germany
Contact:

Re: EXCEL/CSV to DBF, a few questions

Post by ukoenig »

George,

yes OPENOFFICE works fine to help, to finish my CSV-tool.
There was a problem with Exel ( older version ) to display MULTILINE - memos.
With OPENOFFICE it works fine.

Image

using a external texteditor, to view the exported DBF to CSV -result.

Image

I noticed a header with included fieldsizes.

Image

I tested to create a DBF from a created CSV.
It seems, there is a problem, because of a missing fpt-file ?.

best regards
Uwe :lol: :?:
Since 1995 ( the first release of FW 1.9 )
i work with FW.
If you have any questions about special functions, maybe i can help.
Post Reply