×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

How to transfer Excel data into a VFP tabel

How to transfer Excel data into a VFP tabel

How to transfer Excel data into a VFP tabel

(OP)
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

RE: How to transfer Excel data into a VFP tabel

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: import XLSX
thread184-1463010: Importing from Excel 2007
thread184-1338029: Error appending from new MS Excel 2007

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: How to transfer Excel data into a VFP tabel

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

RE: How to transfer Excel data into a VFP tabel

(OP)
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

RE: How to transfer Excel data into a VFP tabel

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

RE: How to transfer Excel data into a VFP tabel

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

RE: How to transfer Excel data into a VFP tabel

(OP)

RE: How to transfer Excel data into a VFP tabel

(OP)
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

RE: How to transfer Excel data into a VFP tabel

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

RE: How to transfer Excel data into a VFP tabel

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close