Page 2 of 5
Re: Export from Excel/CSV to DBF
Posted: Sat Feb 18, 2017 4:17 pm
by 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
Re: Export from Excel/CSV to DBF
Posted: Sun Feb 19, 2017 9:01 pm
by RPC
mol wrote: ↑Sat Feb 18, 2017 4:17 pm
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.
Re: Export from Excel/CSV to DBF
Posted: Mon Feb 20, 2017 9:53 am
by mol
You can try VALUE method too
oActiveSheet :Cells( nRow, nCol ):Value
Re: Export from Excel/CSV to DBF
Posted: Mon Feb 20, 2017 5:08 pm
by RPC
mol wrote: ↑Mon Feb 20, 2017 9:53 am
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
Re: Export from Excel/CSV to DBF
Posted: Mon Feb 20, 2017 5:39 pm
by mol
I was searching over internet few years ago about working with excel tables from hmg app.
Re: Export from Excel/CSV to DBF
Posted: Tue Feb 21, 2017 6:04 am
by 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
Re: Export from Excel/CSV to DBF
Posted: Tue Feb 21, 2017 6:21 am
by mol
Code: Select all
oExcel:DisplayAlerts := .f.
oExcel:ActiveWorkBook:Close()
oExcel:Quit()
works OK in my system
Re: Export from Excel/CSV to DBF
Posted: Tue Feb 21, 2017 6:23 am
by mol
if you want to save excel file before quitting, you shoud use
oExcel:Save()
Re: Export from Excel/CSV to DBF
Posted: Tue Feb 21, 2017 6:59 am
by 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.
Re: Export from Excel/CSV to DBF
Posted: Tue Feb 21, 2017 8:40 am
by KDJ
RPC wrote: ↑Mon Feb 20, 2017 5:08 pm
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