Monday, February 20, 2012

Why Append/Import From an .xlsx fails?

We do know that since Office 2003, VFP pose no problem importing from an .xls or any of its other formats; then came Office 2007 introducing with it an .xlsx format and suddenly VFP can no longer "directly" append nor import from it.  A lot has been baffled by this and maybe have thought 'hey, VFP is really dying!'.    So is it really VFP's incompetence or not?  Let us find out.

One solution I proposed to tackle that problem of importing/appending from an .xlsx file is via automation as I have shown in these blogs:

a. Appending/Importing from Excel 2007's xlsx via automation:

b.  Importing from an xlsx file via automation - Part II:

The trick I used there is to save the excel file on a lower version format and I advice .xl5 format for that.

However, though I have shown the automation ways on those blogs, we haven't tackled exactly "why" appending/importing from an xlsx format is failing.  And this blog is meant to shed some light onto that.

With Office 2003 and lower versions, when we open an excel file, we are dealing with only a "single" file.  With the advent of Office 2007 however, along comes a new format called xlsx.  Why the extra x at the end?  Because that extra x at the end actually signifies the new format which is 'xml'.

With .xlsx, we are no longer dealing with a single file but "several" xml files.  An .xlsx actually is a compressed folder with several sub-folders and several xml files.  If you want to study it, here is what you should do:

a.  Create a copy of one of your xlsx files. 
b.  Rename the copy to any name you want but rename the extension as well from .xlsx to .zip
c.  Open or Extract
d.  Analyze the contents

Here is how it will look like:

VFP has no fault at all why it can no longer append nor import directly from an .xlsx file.  My automation approach works because what it does is like fusing all the pieces of a broken glass to make it as a whole again, which is what VFP is designed to recognize in the first place. 

So is VFP the one at fault on failure to append/import from an .xlsx file?  Now you know better!


No comments:

Post a Comment