Export from Excel/CSV to DBF

[mol]

Try to read excel CELL as text, then convert it using any function. I have np Excel installed on my home computer and I can't test it.
But such a code:

Code: Select all

oExcel := CreateObject( "Excel.Application")
oExcel:Workbooks:Open(cExcelFile, 2, .t., 1, "", "", .f.)
xlCalcStatus := oExcel:Calculation
oExcel:Calculation := xlManual
oExcel:Sheets(cSourceSheet):Select()
oActiveSheet := oExcel:ActiveSheet
cCellValue := oActiveSheet :Cells( nRow, nCol ):Text
msgdebug("Here is text value of cell", cCellValue)

should give you date cell converted to text.
You can use StoD function to convert it to date.

Hope it helps you

[RPC]

Try to read excel CELL as text, then convert it using any function. I have np Excel installed on my home computer and I can't test it.
But such a code:

Code: Select all

oExcel := CreateObject( "Excel.Application")
oExcel:Workbooks:Open(cExcelFile, 2, .t., 1, "", "", .f.)
xlCalcStatus := oExcel:Calculation
oExcel:Calculation := xlManual
oExcel:Sheets(cSourceSheet):Select()
oActiveSheet := oExcel:ActiveSheet
cCellValue := oActiveSheet :Cells( nRow, nCol ):Text
msgdebug("Here is text value of cell", cCellValue)

should give you date cell converted to text.
You can use StoD function to convert it to date.

Hope it helps you

Hi mol
Thanks for the code. I will try and come back.

[mol]

You can try VALUE method too
oActiveSheet :Cells( nRow, nCol ):Value

[RPC]

You can try VALUE method too
oActiveSheet :Cells( nRow, nCol ):Value

Hi mol
That worked !. With oActiveSheet : Cells(nRow, nCol):Text, Date was not getting translated properly. Value works perfectly.
This however takes slightly more time than exporting excel file to CSV and then processing it as suggested by Rathinagiri. However I would prefer your method since one step of exporting file to csv is saved and i can work directly on excel file.
Can you pls tell from where I can get more info on properties, methods of oActiveSheet ?
Thanks

[mol]

I was searching over internet few years ago about working with excel tables from hmg app.

[RPC]

Hi mol
Ok. I have another question - How to close the excel file after converting it to DBF file ?
I find "Excel.exe" process running in Process tab of Window Task Manager. How to kill it through program ?
Thanks

[mol]

Code: Select all

oExcel:DisplayAlerts := .f.
oExcel:ActiveWorkBook:Close()
oExcel:Quit()

works OK in my system

[mol]

if you want to save excel file before quitting, you shoud use
oExcel:Save()

[Rathinagiri]

Is that an .xlsx file or .xls file?

If that is an .xlsx file, then we can use xml library to directly extract data from the file. We don't even require Excel and the process will be so fast.

[KDJ]

Can you pls tell from where I can get more info on properties, methods of oActiveSheet ?

You can read Excel documentation on msdn:
- old versions of Office/Excel
https://msdn.microsoft.com/en-us/library/dn640619.aspx
- new versions of Office/Excel
https://msdn.microsoft.com/en-us/librar ... 67914.aspx