Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to transfer Excel data into a VFP tabel

Status
Not open for further replies.

k2a

Programmer
Jun 26, 2012
133
DE
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
 
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:

Code:
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
 
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
 
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
 
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
 
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
 
Hi Klaus,
Vilhemls procedure allows you to export your xlsx file to a cursor or dbf your option
Koen
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top