How to transfer Excel data into a VFP tabel

[k2a]

Hi,
I access an Excel table like this:
goExcel = CREATEOBJECT("excel.application")
loBook = goExcel.Workbooks.Open(lcSourceFile)
goExcel.Visible = .T.
lnRows = loBook.ActiveSheet.UsedRange.Rows.Count
lnCol = loBook.ActiveSheet.UsedRange.Columns.Count

But I do not know how to transfer the content of the opened Excel sheet to a VFP table.
Has anyone an idea? Any help would be appriciated.
Best regards
Klaus

 

[Mike Lewis]

If you want to import the entire sheet (as opposed to a range of cells within the sheet), the easiest way is to use APPEND FROM:

SELECT MyTable
APPEND FROM MyExcelFile TYPE XL8 SHEET 1

If the DBF doesn't yet exist, you can use IMPORT instead. It has similar syntax, and will create the DBF for you.

The above assumes that the Excel file is an XLS rather than an XLSX. If the latter, you can save it as an XLS within Excel.

There are are also several threads here specifically about importing XLSX, for example:
thread184-1789199
thread184-1463010
thread184-1338029

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Mike has given good advice,
if the Excel range varies in size (especially columns) and type of data you can't have an automatic creation of the necessary DBF, though.
APPEND FROM XLS also needs data to be in the same number and type of columns the table already has, additional columns are not appended or imported and wrong types lead to 0/NULL/EMPTY values in the DBF.

A very general approach would be using an Excel OLEDB PRovider and Querying it vie Cursoradapter into a cursor. That's in principle working like SELECT * FROM TABLE just with any other database, too and can generate the necessary cursor structure as given by the sheet. The sheet can have a header row or not, that's one of the connection options for the OleDB provider connection string, but you can't query a sheet with say a summary row. You can only query Excel sheets, which are conforming to database table structures, which is, as always, same data types per column, not any sheet or any range.

Besides that, there is no pasting into table cells as you could into a new empty sheet or other range with just a starting cell, neither grids nor the browse window supports such pasting of data into a cursor or DBF.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[k2a]

Hi Mike,
thank you for your quick response. The Excel file is actually an XLSX.
I just run through several threads you mentioned. My problem would be that some of the Excel data must be manipulate.


Klaus

 

[TamarGranor]

You can use Excel's SaveAs method to create a CSV file, which VFP can open directly. You can also write VFP code to loop through the Excel data and copy it into a cursor, but that will be a lot slower.

Tamar

 

[Koen Piller]

Hi Mister K2a

To import an XLSX file into a DBF file you can use Vilhelm's procedure, to be found at

http://praisachion.blogspot.com/2017/08/importfromxlsx-40.html

where you can also find a procedure to save a DBF file into an XLSX file, or appand from.
Please study his procedures.
Regards,
Koen

 

[k2a]

Hello Tamar,
I would rather like to loop through the Excel data and copy it into a cursor, because not all the content has to be taken over. In addition, some changes are required during the copy process. Time is not crucial.

If you have already writen such a VFP code, could you give me some advice on how to do that?
Thanks
Klaus

 

[Koen Piller]

Hi Klaus,
Vilhemls procedure allows you to export your xlsx file to a cursor or dbf your option
Koen

 

[k2a]

Hi Koen,
Thank you for your advice, but Vilheml's procedure is less suitable for my purpose. In the meantime, I've already found a way that will keep me going.
Klaus