from excel to dbf

[Carlo Colella]

Good morning to all, I am writing from south italy.

Is there any way to convert an excel sheet to a dbf?

online i found this : https://anyconv.com/it/convertitore-da-xls-a-dbf/

but it is not very reliable because it converts columns containing dates to character fields.

Is there any other way?

Thanks
Carlo

[C.J. Koot]

Hello Carlo,

I created a small program for myself to convert an excel-sheet to a dbf Below is the code:

Kind regards,

Rene Koot

*this program is to export an Excel sheet into a .dbf file
*in Excel there is an export function, but this does not always use the right Char-set and table definition
*The Excel sheet should contain the following cells filled
* ROW 1: FieldName
* ROW 2: FieldType
* ROW 3: FieldWidth
* ROW 4: Decimals
* ROW 5-END: FieldValues

#INCLUDE "minigui.ch"
#DEFINE CR_LF Chr(13) + Chr(10)

FUNCTION MAIN

PUBLIC aField [0] [4]

SET DECIMALS TO 0

DEFINE WINDOW Win_Main ;
AT 0,0 ;
WIDTH 400 ;
HEIGHT 400 ;
TITLE 'Excel export to DBF' ;
FONT 'Times New Roman' SIZE 12 ;
MAIN ;

DEFINE STATUSBAR ;
FONT 'Times New Roman' SIZE 12
STATUSITEM ''
CLOCK WIDTH 90
END STATUSBAR


@10, 10 BUTTON Btn_1 ;
CAPTION 'select Excel-file' ;
WIDTH 150 ;
HEIGHT 25 ;
ONCLICK { || cFile := Getfile ( { {'Excel-bestand (*.xls;*.xlsx)','*.xls;*.xlsx'} } , 'Select file', , .F., .T. ), SETPROPERTY('Win_Main', 'Txt_1', 'VALUE', cFile)}

@ 10, 170 TEXTBOX Txt_1 ;
WIDTH 200 ;
HEIGHT 25 ;
VALUE ''

@ 50, 10 LABEL Lbl_1 ;
VALUE 'name of DBF-file:' ;
WIDTH 150 ;
HEIGHT 25 ;
RIGHTALIGN

@ 50, 170 TEXTBOX Txt_2 ;
WIDTH 200 ;
HEIGHT 25 ;
VALUE ''

@ 80, 10 GRID Grd_1 ;
WIDTH 350 ;
HEIGHT 200 ;
HEADERS { 'Fieldname', 'Type', 'Width', 'Dec' } ;
WIDTHS { 180, 50, 50, 50 };
FONT 'Times New Roman' SIZE 12 ;
ITEMS aField ;

@ 300, 20 BUTTON Btn_2 ;
CAPTION 'export Excel' ;
WIDTH 100 ;
HEIGHT 25 ;
ONCLICK RK_ReadFields()

END WINDOW

CENTER WINDOW Win_Main
ACTIVATE WINDOW Win_Main

RETURN

*************************************************

FUNCTION RK_ReadFields()

LOCAL oExcel, oSheet, oWorkBook, n, nField := 1, cField := '', cType := '', nWidth := 0, nDec := 0, nRow := 5
LOCAL cFile := GETPROPERTY('Win_Main', 'Txt_1', 'VALUE')
LOCAL cDBF := GETPROPERTY('Win_Main', 'Txt_2', 'VALUE')

IF LEN(ALLTRIM(cFile)) == 0
MsgInfo('There is no Excel-file selected')
RETURN
ENDIF
IF LEN(ALLTRIM(cDBF)) == 0
MsgInfo('There is no DBF-name given')
RETURN
ENDIF
*now we check if Excel is available and if so open Excel

Win_Main.StatusBar.Item(1) := 'open Excel!'
oExcel := TOleAuto():New( 'Excel.Application' )
IF Ole2TxtError() != 'S_OK'
MsgStop('There is no Excel!' )
RETURN
ENDIF
oExcel:Visible := .F.
oWorkBook := oExcel:WorkBooks:Open(cFile)
oSheet := oExcel:Get( 'ActiveSheet' )

DO WHILE VALTYPE(oSheet:Cells(1, nField):VALUE) != 'U'
cField := oSheet:Cells(1, nField):Value
cType  := IF(VALTYPE(oSheet:Cells(2, nField):Value) != 'U', UPPER(oSheet:Cells(2, nField):Value), '*')
nWidth := IF(VALTYPE(oSheet:Cells(3, nField):Value) != 'U', oSheet:Cells(3, nField):Value, 0)
nDec   := IF(VALTYPE(oSheet:Cells(4, nField):Value) != 'U', oSheet:Cells(4, nField):Value, 0)
AADD(aField, {cField, cType, nWidth, nDec})
Win_Main.Grd_1.AddItem ( { cField, cType, ALLTRIM(STR(nWidth)), ALLTRIM(STR(nDec)) } )
nField++
ENDDO

IF MsgYesNo('Would you like to continue converting?', 'Question')
dbCreate(cDBF, aField, , , )
USE &cDbf
DO WHILE VALTYPE(oSheet:Cells(nRow,1):VALUE) != 'U'
APPEND BLANK
FOR n = 1 TO LEN(aField)
FIELDPUT(n, IF(VALTYPE(oSheet:Cells(nRow, n):Value) != 'U', oSheet:Cells(nRow, n):Value, ))
NEXT
Win_Main.StatusBar.Item(1) := 'Records: ' + ALLTRIM(STR(nRow))
nRow++
ENDDO
USE
ENDIF

ASIZE(aField, 0)
Win_Main.Grd_1.DeleteAllItems
oExcel:Application:DisplayAlerts := .T.
oWorkBook:Close( .T. )
oExcel:QUIT()
RELEASE oExcel

RETURN

*************************************************

Op zaterdag 30 september 2023 om 08:26:38 UTC+2 schreef Carlo Colella:

[Carlo Colella]

Hi Rene,
could you please give me an example of an excel sheet you converted?

this is mine but i am afraid it is not suitable for your program

--
Visit our website on https://www.hmgextended.com/ or https://www.hmgextended.org/
---
You received this message because you are subscribed to the Google Groups "Harbour Minigui" group.
To unsubscribe from this group and stop receiving emails from it, send an email to minigui-foru...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/minigui-forum/833baddb-4f99-4b7e-9bab-c6ad5f188af0n%40googlegroups.com.

[Harbour Minigui]

No domingo, 1º de outubro de 2023 às 21:37:09 UTC+2 carl...@gmail.com escreveu:

Hi Rene,
could you please give me an example of an excel sheet you converted?

this is mine but i am afraid it is not suitable for your program

Bom dia
uma pergunta simples:
A demo presente na pasta Minigui foi testada
\Minigui\SAMPLES\Advanced\ReadXLS ?
Pode ser a solução para o problema e caso não seja
é um bom exemplo de partida, sem tirar nada do que foi proposto
dos outros membros do grupo.

Minigui Extended é a distribuição mais rica em exemplos práticos.

Saudações

Good morning
a simple question:
The demo present in the Minigui folder was tested
\Minigui\SAMPLES\Advanced\ReadXLS ?
It could be the solution to the problem and in case it isn't
it is a good starting example, without taking anything away from what was proposed
from the other members of the group.

Minigui Extended is the distribution richest in practical examples.

Greetings

Pierpaolo Martinello [ Minigui Team ]

[C.J. Koot]

Hello Carlo,

Attached is an example file I uses which works OK.

I see in your screenshot that there is a time column. As far as I know Harbour does not have an time definition in a table?

Kind regards,

René Koot

Op 02-10-2023 om 08:46 schreef Harbour Minigui:

--
Visit our website on https://www.hmgextended.com/ or https://www.hmgextended.org/
---

You received this message because you are subscribed to a topic in the Google Groups "Harbour Minigui" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/minigui-forum/54ASU-zdQ8k/unsubscribe.
To unsubscribe from this group and all its topics, send an email to minigui-foru...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/minigui-forum/a097120a-1a47-4edb-9977-0ee8ad0e3002n%40googlegroups.com.

[Carlo Colella]

Thanks

i will try deleting that column.

Kind regards,

Carlo

You received this message because you are subscribed to the Google Groups "Harbour Minigui" group.

To unsubscribe from this group and stop receiving emails from it, send an email to minigui-foru...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/minigui-forum/ac92c2bd-3795-fda3-8a32-cdceece5677a%40gmail.com.

[Carlo Colella]

thank you very much

i will try it as soon as possible

Carlo

--

Visit our website on https://www.hmgextended.com/ or https://www.hmgextended.org/
---
You received this message because you are subscribed to the Google Groups "Harbour Minigui" group.
To unsubscribe from this group and stop receiving emails from it, send an email to minigui-foru...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/minigui-forum/a097120a-1a47-4edb-9977-0ee8ad0e3002n%40googlegroups.com.

[Carlo Colella]

Hi Renè

Even with your excel sheet it gives me the same error

You received this message because you are subscribed to the Google Groups "Harbour Minigui" group.

To unsubscribe from this group and stop receiving emails from it, send an email to minigui-foru...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/minigui-forum/ac92c2bd-3795-fda3-8a32-cdceece5677a%40gmail.com.

[Spencer Redfield]

Hi Carlo,

I could not get the following oExcel := TOleAuto():New( 'Excel.Application' ) to work for me.

Instead, the following worked:     

BEGIN SEQUENCE WITH {|e| Break( e ) }

    oExcel := CreateObject( "Excel.Application" )
RECOVER
    exit
END SEQUENCE

I hope this might be helpful.

Spencer

To view this discussion on the web, visit https://groups.google.com/d/msgid/minigui-forum/CABV2MXsfApszOi7JJKdJ3pBh7GJRNnzt799j3wFfxhP3sZZ0Kg%40mail.gmail.com.

[Ricardo Sassy]

Hi Carlo.

Try using this alternate syntax:

oExcel := WIN_OLECREATEOBJECT('Excel.Application')

 

Regards.

Ricardo Sassy

 

 

De: minigu...@googlegroups.com [mailto:minigu...@googlegroups.com] En nombre de Carlo Colella
Enviado el: lunes, 2 de octubre de 2023 16:43
Para: Harbour Minigui
Asunto: Re: [harbourminigui] Re: from excel to dbf

 

Hi Renè

 

Even with your excel sheet it gives me the same error

 

 

Il giorno lun 2 ott 2023 alle ore 09:21 C.J. Koot <plke...@gmail.com> ha scritto:

Hello Carlo,

Attached is an example file I uses which works OK.

I see in your screenshot that there is a time column. As far as I know Harbour does not have an time definition in a table?

Kind regards,

René Koot

Op 02-10-2023 om 08:46 schreef Harbour Minigui:

No domingo, 1º de outubro de 2023 às 21:37:09 UTC+2 carl...@gmail.com escreveu:

Hi Rene,
could you please give me an example of an excel sheet you converted?

 

this is mine but i am afraid it is not suitable for your program

 

 

Bom dia

To view this discussion on the web, visit https://groups.google.com/d/msgid/minigui-forum/CABV2MXsfApszOi7JJKdJ3pBh7GJRNnzt799j3wFfxhP3sZZ0Kg%40mail.gmail.com.

[Auge & Ohr]

hi,

when you want to use ActiveX you NEED Excel

without Excel you can use ADO

Microsoft Access Database Engine 2016 Redistributable
https://www.microsoft.com/en-us/download/details.aspx?id=54920

Microsoft Access Database Engine 2010 Redistributable
https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=13255

use 32 Bit or 64 Bit Version like your App

Jim

[Carlo Colella]

Hi Ricardo,

It doesn't work

I get the same error

To view this discussion on the web, visit https://groups.google.com/d/msgid/minigui-forum/0ced01d9f594%24b941c340%242bc549c0%24%40gmail.com.

[Carlo Colella]

Hi,

excuse my ignorance but I didn't understand what you mean

To view this discussion on the web, visit https://groups.google.com/d/msgid/minigui-forum/74cc4851-2735-4e61-919e-83631de4457cn%40googlegroups.com.

[Carlo Colella]

This function. Great!

Thank you very much

Il giorno lun 2 ott 2023 alle ore 08:46 Harbour Minigui <minigu...@googlegroups.com> ha scritto:

--

Visit our website on https://www.hmgextended.com/ or https://www.hmgextended.org/
---
You received this message because you are subscribed to the Google Groups "Harbour Minigui" group.
To unsubscribe from this group and stop receiving emails from it, send an email to minigui-foru...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/minigui-forum/a097120a-1a47-4edb-9977-0ee8ad0e3002n%40googlegroups.com.