Page 1 of 3
Read data to databases from MS Excel-2010 files
Posted: Sat Dec 01, 2012 9:12 am
by Eugene Lutsenko
Whether allows eXPress++ read data to databases from MS Excel-2010 files?
Re: Read data to databases from MS Excel-2010 files
Posted: Sat Dec 01, 2012 11:02 am
by Cliff Wiernik
Did you try the DC_Excel2Workarea() functions. Since it uses the currently loaded Excel software, if it can read 2010 files, it should be able to read them. I have not specifically checked with these files though.
Re: Read data to databases from MS Excel-2010 files
Posted: Tue Dec 18, 2012 5:59 pm
by Eugene Lutsenko
Code: Select all
FUNCTION Main()
LOCAL oScrn, nError, nHandle
DC_LoadRdds()
USE sprdsht VIA 'DBFCDX'
oScrn := DC_WaitOn( 'Exporting SPRDSHT.DBF to SPRDSHT.XLS' )
DC_WorkArea2Excel( DC_CurPath() + '\SPRDSHT.XLS' )
DC_Impl(oScrn)
DC_SpawnUrl( DC_CurPath() + '\SPRDSHT.XLS' )
Sleep(200)
nError := 1
DO WHILE nError > 0
Sleep(100)
nHandle := FOpen( DC_CurPath() + '\SPRDSHT.XLS', FO_READWRITE )
nError := FError()
ENDDO
FClose( nHandle )
Sleep(100)
COPY STRUCTURE TO sprdsht2
USE sprdsht2 new exclusive
DC_Excel2WorkArea( DC_CurPath() + '\SPRDSHT.XLS' )
DCMSGBOX 'Done!'
RETURN nil
* ---------------
PROC appsys ; RETURN
In the given example:
- initially there is a dbf-table;
- then data from it are copied in created by the Excel-sheet program of necessary structure;
- then on the basis of the first dbf-table the second dbf-table with the same structure, as at the first is created;
- then data from earlier created Excel-leaf with data of the first table are loaded into the second dbf-table.
The main thing that before loading of an Excel-leaf the dbf-table has to be already created and open corresponding to it on structure.
Whether and it is possible to load data from an Ecel-leaf which structure before loading is unknown into the dbf-table?
Whether it is possible to learn Excel-leaf structure before loading by means of any function not to investigate and to enter it manually?
Re: Read data to databases from MS Excel-2010 files
Posted: Wed Dec 19, 2012 2:41 am
by reganc
You can also have a look at:
C:\Program Files\ALASKA\XPPW32\source\samples\activex\msexcel
for examples of how to access Excel using Activex directly in case that is useful.
The help on the Excel Activex methods and properties are here:
http://msdn.microsoft.com/en-us/library/bb149081.aspx
Re: Read data to databases from MS Excel-2010 files
Posted: Wed Dec 19, 2012 4:33 am
by Tom
You may also "use" an Excel-table like a normal dbf - with the ODBCDBE. You can even use the Excel-notation for ranges in that situation, like USE "A1:C100", since Excel headers are not compatible with dbf headers. ODBCDBE is a part of the professional subscription. Connections are made using the DacSession class.
Re: Read data to databases from MS Excel-2010 files
Posted: Wed Dec 19, 2012 8:13 am
by Eugene Lutsenko
reganc wrote:You can also have a look at:
C:\Program Files\ALASKA\XPPW32\source\samples\activex\msexcel
for examples of how to access Excel using Activex directly in case that is useful.
The help on the Excel Activex methods and properties are here:
http://msdn.microsoft.com/en-us/library/bb149081.aspx
Unfortunately on this way: C:\Program Files\ALASKA\XPPW32\source\samples\activex\msexcel there are only examples transformation dbf->xls, but xls->dbf is absent.
Re: Read data to databases from MS Excel-2010 files
Posted: Wed Dec 19, 2012 8:15 am
by Eugene Lutsenko
Tom wrote:You may also "use" an Excel-table like a normal dbf - with the ODBCDBE. You can even use the Excel-notation for ranges in that situation, like USE "A1:C100", since Excel headers are not compatible with dbf headers. ODBCDBE is a part of the professional subscription. Connections are made using the DacSession class.
Hardly I will be able to deal with it...
Re: Read data to databases from MS Excel-2010 files
Posted: Wed Dec 19, 2012 10:50 am
by rdonnay
If you will upload your Excel file, I will see what I can do to help.
Re: Read data to databases from MS Excel-2010 files
Posted: Wed Dec 19, 2012 11:30 am
by Eugene Lutsenko
rdonnay wrote:If you will upload your Excel file, I will see what I can do to help.
Thank you very much! Like it, but lines and columns can be much more including there is so much, how many supports Excel-2010.
http://lc.kubagro.ru/Dima/Inp_data1.xls
http://lc.kubagro.ru/Dima/Inp_data2.xlsx
http://lc.kubagro.ru/Dima/Inp_data3.xls
http://lc.kubagro.ru/Dima/Inp_data4.xls
http://lc.kubagro.ru/Dima/Inp_data5.xls
Re: Read data to databases from MS Excel-2010 files
Posted: Fri Dec 21, 2012 8:44 pm
by Eugene Lutsenko
Thanks Roger, Wolfgang and Tom!
Thanks to you I managed to understand everything (almost).
Everything was much simpler, than it seemed at the beginning when didn't understand yet.
But questions will be still (most likely as shows life)