I've got 2 .xlsx files that were created from another system. FM12 imports one of them but the other one only loads blank records.
I'm able to open both files with Open Office or Excel.
Has anyone else come accross this?
Are there different types of .xlsx files?Is this a weird Microsoft thing where they have multiple xlsx formats or does FM only partially support .xlsx files?
Are there different types of .xlsx files?
Is this a weird Microsoft thing where they have multiple xlsx formats or does FM only partially support .xlsx files?
Encrypted and non-encrypted .xlsx files both have the same file extension (".xlsx"), but are structured differently internally; however, I think FileMaker Pro will just refuse to import an encrypted .xlsx file. Are the correct number of records/rows and fields/columns being imported, even if all the fields end up being blank? Have you tried stripping out all of the cell styling in the spreadsheet?
Are you absolutely certain that one of the databases *does* import successfully? I downloaded both of the spreadsheets attached to your post (Works.xlsx and DoesntWork.xlsx), and neither of them imported the field data successfully for me. There may be an issue in the .xlsx importer in FileMaker Pro.
If you save as .csv from Excel, then re-save as .xlsx, it imports correctly into FileMaker, so I think there's something wonky with the way the file itself is being generated.
Can you get the source system to provide data in a format with less overhead (like .csv)?
No, the source system only outpus .xlsx files.
I find it hard to believe I'm the first person to run inot this issue.
There's only one type of .xlsx file, but that doesn't mean that the source system is writing the file correctly.
What is the source system?
Typically something like this comes down to some weird data embedded in with the real data. Hidden / Control characters have been known to cause behavior like this. When it comes to Excel it is not out of the question that during data entry or creation of the Excel file that things like hard (or soft) carriage returns and other formatting characters are introduced accidently when entering in the data. This type of extra stuff doesn't bother Excel at all but when you move the data to another application, like FileMaker Pro, then weird things can happen.
For example, I exported the data to CSV and then brought in the "Doesn't Work" data into FileMaker Pro using the CSV file. It worked, but there are some weird characters in the location and description fields which might be causing the problem when brought in straight from Excel.
I just tried importing the file "Works.xlsx" again and it did NOT work for me either so I assume the whole thing comes donw to a bad XLSX format by the system that created it.
Or it's weird characters like steveroming suggested.
I still wouldn't rule out a problem with FileMaker Pro's .xlsx importer.
I'm also guessing that the originating system constructed the spreadsheets programmatically (either directly, or by controlling Excel to produce the spreadsheet), and formatted the individual cells in ways that a human might not style data in spreadsheets to be imported into FileMaker Pro.
Retrieving data ...