Read data to databases from MS Excel-2010 files

[Eugene Lutsenko]

Whether allows eXPress++ read data to databases from MS Excel-2010 files?

[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.

[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?

[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

[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.

[Eugene Lutsenko]

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.

[Eugene Lutsenko]

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...

[rdonnay]

If you will upload your Excel file, I will see what I can do to help.

[Eugene Lutsenko]

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

[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)