Comparing a table against a spreadsheet ! 1

[safarijoe2000]

Hello,

I have a table (mytable.dbf) and I have an Excel spreadsheet (mysheet.xls).

What I'd to do is to be able to exclude those records from the mytable.dbf that are present on mysheet.xls

Say, mytable.dbf has field (fieldA), and the mysheet.xls has also a field (fieldA). I'd like to compare both based on fieldA, and then exclude those records from mytable.dbf.

Any help will be useful.

Thanks

Walter

 

[StewartUK]

Hi Walter,

Are you familiar with Excel Automation? I think that's what you'll need to use for this.

I'd suggest that you SCAN MyTable, putting fieldA into a variable and then use a FOR...ENDFOR loop to go down the rows in the Excel spreadsheet comparing Cell A with the value of the variable.

There is an Excel contstant for determinig the last cell, something like:-

osh = oxl.ActiveSheet
osh.Cells.Select
osel=oxl.Selection
osel.SpecialCells(11).Select && this selects the last cell
osel.Cells.Address &&...
*...this returns the cell in the format $colnum$rownum

......with which you could find out how many rows there are for the loop.

This syntax:-
osh.Cells(12,"A&quot.Text
returns the contents of the cell

Hope that helps, get back if you need more help,

Stewart

 

[jimoo]

Here is a simple automation FAQ that may help.

faq184-4428 Excel - How to do Automation from VFP



Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[BPeisch]

If it were me, I'd take a different approach. I'd IMPORT the Excel spreadsheet into a temporary VFP table, and compare that to the existing VFP table using pure native VFP code.



-BP

http://www.peisch.com

 

[jimoo]

Barbara is correct, and I thought about it. Her way is preferred if you know the format of spreadsheet, including column widths, headers, etc. is going to remain consistent. If not, the import into a table usually encounters errors or bad data. That has been my experience with it anyway.

Then again, if the format of spreadsheet changes too much, even automation gets very tricky.

The bottom line, in my opinion, is excel is difficult format to read from.

And as I say that, I have one more thought. With Excel 2000 I tried to save the spreadsheet as an XML document and read it, but that did not work well because of the XML document it created. The new Office suite has made great improvements with XML. If you have it, you may try saving the document as an XML document and see if you can import or read it using VFP.


Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[BPeisch]

Hi Jim,

I think you're confusing IMPORT with APPEND FROM. The IMPORT command creates a new DBF with a name that matches the XLS file, so the structure always matches that of the spreadsheet. Of course, if the format changes, there are other challenges, but the IMPORT shouldn't break.



-BP

http://www.peisch.com

 

[jimoo]

I'll give you a star Barbara, because you are correct. I don't think I tried import, but I can assure you that append from is a pain.

A side note - I think the XML way is the way MS is going and I would encourage people to try it with the new office suite.

Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[BPeisch]

Thanks for the star, Jim. You're right about XML. That is what MS is pushing whereever they can. I think they're even going that way with Help in Longhorn.



-BP

http://www.peisch.com