sending data from ms excel 2003 to vfp 5 table

[titoneon]

Hi Guys,
I have a MS excel 2003 file with two columns and about 125 rows, i want this be sent to a vfp 5.0 table
so i have done these:

use tablename
append from newc.xls type xl5 && NEWC.XLS && go error

then i typed

append from newc.xls type xls && geting the same error

the error is "OLE error code 0x80030002 Unknown OLE status code

Any help is very appreciated, i need the data in excel to be in a vfp table, otherwise, i will have to copy and paste not a
funny thing i guess
Thanks a lot in advance

 

[DSummZZZ]

Do you have the file open in Excel when you try to import it?


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[titoneon]

Hi Dave,
No i did not have it open, that is weird
Thanks

 

[titoneon]

Dave,
I just did something different and got the result i wanted, but anyway doing it as i mentioned in my first post is strange, it should work
Regards

 

[JRB-Bldr]

I have a MS excel 2003 file with two columns and about 125 rows, i want this be sent to a vfp 5.0 table
i need the data in excel to be in a vfp table

Is this a one-time need or do you need to do this operation on a periodic basis?

If merely a one-time operation, manually Export the data from Excel 2003 into a DBF4 format file with SaveAs...
Just:
* go into Excel 2003
* open the desired Excel file
* highlight Only those rows/columns that contain your desired data
* do a SaveAs... into DBF4 format
* close Excel file
* Open VFP and then USE the new exported data table <do whatever with it>

If this operation is to be done programatically and periodically...
Is the file itself an Excel file with a .XLS extension or is it a .CSV file which might open in Excel?

Good Luck,
JRB-Bldr

 

[titoneon]

Hey JRB,
Long time no talking to you, that is exactly what i did, went to excel and save as dbiv, but will be interesting in case that i have to do it periodic basis how to accomplish it ?
No the file was saved as xls no csv but i noticed one thing, there is more than one sheet, even that the other two sheets in the same file are empty, can be this an issue ?
Regards
Thanks in advance

 

[Mike Lewis]

No, the fact that the workbook has multiple sheets wouldn't cause the error you are seeing. If the sheet containing your data is not the first sheet, use the SHEET clause in your APPEND command. For example:

APPEND FROM newc.xls TYPE xl5 SHEET 2

You might also try using TYPE XL8 instead of XL5. But, again, that wouldn't explain the error you are seeing.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[titoneon]

Hey Mike,
Nice to hear fro you, thanks for the info and i will try that, even that i already accomplished it but i will try it, by the way
I want to wish to all of you Happy Holidays and a Merry Christmas
Regards
Ernesto

 

[Olaf Doschke]

Excel 2003 is Excel main Version 11.
Why do you expect you can append type xls or xl5 or xl8?

You have to save as excel95/98 and then can append.

Since about 10 years it's better to only use vfps APPEND from excel files you created from VFP, not from Excel. Or use an Excel oledb provider to access excel data as a table. That is even more true for xlsx files, but incompatibility to Excel began much earlier than with the XML formats since Office 2007.

See?

Bye, Olaf.

 

[Mike Lewis]

I'm surprised you say that, Olaf. I've been using APPEND FROM to import native Excel 2003 files since ... well, since 2003 I suppose.

Then again, maybe it's working in my case but won't work for others, for some reason. I don't know.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[TamarGranor]

TYPE XL8 should work with Excel 2003 files. The format didn't change until Excel 2007.

Tamar

 

[Olaf Doschke]

I never used APPEND ... TYPE XLS or XL8, because even if the format is right, Append can only work for cases the excel columns match the table/cursor field types at least halfways.

Like XMLTOCURSOR is no universal conversion of any XML to a cursor, APPEND is no one size fits all XLS files.

That said, you really mean to say there where no changes at all in excel files since between Excel 98 and Excel 2007?

It may be true for excel sheets not making use of Excel 2000 or later features, with simple sheets. But I can't imagine you can append any Excel 2003 file by APPEND.
What I also know is, that the 2007 Office version introduced really different excel file types. We all know you can change the file extension of an xlsx to zip, then unzip it and see the inner workings with xml files etc. Of course you can't append from that, but also not all xls files are "appendable".

Bye, Olaf.

 

[TamarGranor]

Yes, when you start digging into documentation, you see "Excel 97-2003" as one of the file types.

Tamar

 

[Mike Lewis]

My guess is that, even if there were differences between 97 and 2003, these don't affect the functioning of the APPEND FROM command. VFP is still able to read the information that it needs to do the import.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

I don't know why you still continue here. I never said you can't append any Office2003 file, I just gave the facts: Version 11 is not Version 8 or 5.

Even if it works in normal cases, that still does not make all XLS files appendable. You can continue to do that. I'm not saying you can't. But the sheet you append has to have the structure of a database table, eg same type of values in each column, aside of a header row with captions/field names. Even Excel 5 or 8 files are not always appendable, if you eg put a text, a date, a currency and a float into the same column, you can't append that. Pethaps you get some result, if you append all these different data types into a VFP char column. If you have a picture or graph embedded, etc etc, things might not work at all and even give OLE errors. Excel is no database. It's really simple to render an XLS sheet useless for foxpro append or import, if you just enter something in a far off cell, for example. The problem may not have been the file format version, but the data in the sheets.

And Tamar, yes, there is such an option of 97-2003. But I also remember a case such files were not really 1:1 exactly the same as what Excel2003, XP or 97 or any of those Office versions save, it's just a lowest common denominator format you can export from Office 2007 or later and read in in the older Excel versions. It's only tested for compatibility with those older Excel versions. And it's fine you can APPEND these files, too.

Titeon already said some days ago: "I just did something different and got the result i wanted."

Maybe he did something to the file, eg deleting row 1 or adding it, maybe used IMPORT FROM instead of APPEND.
Anyway, problem solved, isn't it?

Bye, Olaf.

 

[TamarGranor]

Still talking about it because people find these answers when they search, so best to have them accurate.

You make an important point. When you Save As "Excel 97-2003" from Excel 2007 or 2010, the file that's created isn't actually an Excel 97-2003 file and doesn't work with APPEND FROM. Excel can read it, but there's something different in there.

Tamar

 

[Olaf Doschke]

> When you Save As "Excel 97-2003" from Excel 2007 or 2010, the file that's created isn't actually an Excel 97-2003 file and doesn't work with APPEND FROM. Excel can read it, but there's something different in there.
As far as I have read somewhere the older Office Version had a little update to be able to read this "common denominator" file format. Thus it's not compatible to VFP APPEND.

Of course that doesn't mean you can't append Exel2003 files created in that version itself.

An extensive discussion and code about XLS2DBF or DBF2XLS can also be found at Foxite, eg see

http://www.foxite.com/archives/0000153776.htm

There also is a solutions with Excel2007 using the Microsoft.ACE.OLEDB.12.0 OLEDB Provider. Automation isn't dead, but APPEND is something I don't use for XLS anymore.

Bye, Olaf.