Import spreadsheet error

Hi, Export/Import is giving me serious problems on import.

The error is "Uploaded file is not a valid spreadsheet file or its values are not in the expected formats!"

I've added products & categories to the spreadsheet, and I've had to do so with Google Sheets, as I don't have Microsoft Office products.

The only thing I can notice is on the Products sheet, the products I've added don't have the same padding as the ones exported... other than that, I made sure the columns had the same formatting as the original products from the export...

I've recreated the spreadsheet, using only the values from the original, and the formats from the exported sheet, and am STILL getting the error. I have a deadline to meet and this is driving me insane - there is nothing i can see wrong with this sheet.

YES, I have tried exporting and importing only what I exported and that works fine, but when changes are made it fails - even with the same formatting. there are almost a hundred products and the file size is ~45KB

Hello? Anyone? Here's a link to the spreadsheet...http://thevinewinecellar.com/vinewine.xlsx

Version of OC? Which tool, and which version of it? Did you simply rename the file extension?!

The error is telling you that you have a serious mismatch between file formats, and that Google Sheets' format is not going to work in place of Microsoft Excel's format. The best you can probably do is to find a file converter that will massage and force the former into the latter, and vice-versa. As spreadsheets go, you have Googleglop -- look at free, open source, cross-platform Open Office, which originated in frontal competition with Micromob. Conversions as well as native inter-compatibilities do occur there.

Alike OC itself, mysql, spreadsheet programs, and other software all come in versions, stages of development. The same is true of most file extensions, whose file formats go through stages of development.

Not all spreadsheet formats, and not all spreadsheet programs, can handle one another's stuff. Versions of Excel cannot all handle all other Excel versions' files, even if all of the formats are nominally .xls or .xlsx, and likewise versions of Word and its funny friends cannot all handle one another's nominally .doc or .docx, either. You went beyond that to yet another kind of spreadsheet and file format, and appear from what you've said to have renamed the file extension as a bridge. Won't work. The machine gobbledygook inside the files differs, and what you see as format is not all that there is to "format" in the first place. It's the same as between a pure ascii text file that is properly editable in a pure ascii text editor and the machine gibberish that you don't see on-screen in word processor files for word processors, even if the texts might be made to seem the same on your screen. There are column/row/cell formats, there are sheet/file formats, there are file extension formats, and those govern the data flow from the file through the processor to wherever the data are going or coming from.

If you merely renamed the file extension to .xlsx, that won't work. If you hand built outside spreadsheet programs what you thought would pass as a .xlsx, that won't work. The error is telling you that your own Pegasus can't fly there even if you rename it Birdy.

OC 1.5.6

Export/Import tool, the free one

Google Sheets can import and export a number of formats, xlsx being one of them

I took the xlsx sheet I got from Export/Import, imported it to Google Sheets, edited it and ADDED new products, and exported it as xlsx - no file renaming has taken place. I'm well aware that if a programs calls for a file in a certain format that it has to be in that format - that just changing the extension doesn't change the format. As for your claim where you said that I renamed the file extension - NO, I never said that.

The file can be opened and edited in my friend's copy of Excel, as well as Open Office as an xlsx file - but on import in OC, the Export/Import extension craps out with the given error message. Would be nice if it provided more info - as to where the problem was being caused.

andysdesigns wrote:Hello? Anyone? Here's a link to the spreadsheet...http://thevinewinecellar.com/vinewine.xlsx

I looked at your spreadsheet, and got this in the system/logs/error.txt (you should check yours) during an Import to a 1.5.6 Opencart server:

Export/Import: Invalid header in the Attributes worksheet

I then further debugged it, turns out that the in-built PHPExcel always counted 6 instead of the expected 5 columns for the Attributes worksheet header row. So I opened it up in OpenOffice, re-saved it as ODS, and it then imported fine from the ODS.

This makes me think that something was wrong with your spreadsheet software used. I don't have MS Excel installed, so I can't test it with that one.

So I can import an .ods from OpenOffice calc - straight into OC?

andysdesigns wrote:So I can import an .ods from OpenOffice calc - straight into OC?

Yes.

It still beats me though as to why your XLSX file was seen as having 6 columns instead of the visible 5 columns for the Attributes worksheet.

P.S.: I now had a chance to open up your spreadsheet file on another computer which had an MS Office 2010 with the ribbon interface on it, and when I went to the Attributes sheet and did a Ctrl-End, it jumped to sixth column F, so MS Excel does indeed see it as six columns, too. I then tried to delete column F with the same MS Excel, but it still ended up with six columns. So, at least for MS Execl 2010, this is a bug in this Microsoft software!

The downloadable file provided, vinewine.xlsx, opens without incident, and showing only five columns in the Attributes tab, in this version of Excel on a friend's machine:
"Microsoft (R) Office Excel 2003 (11.8326.8324) SP3
Part of Microsoft Office Professional Edition 2003
Copyright (c) 1985-2003 Microsoft Corporation."

Your Google file exporter, converter, or other routine is selecting that vintage of Excel format. That is nothing new, for years leading programs have offered export conversions into various prior as well as current Microsoft formats but Microsoft never deigned to offer the reverse. Now yet another giant is playing the same game. That may give an interesting handle upon recent need to use .ods as a bridge.

The problem resides in the vintage of the file format and in and of itself is not a bug in prior and subsequent versions of Excel the program or .docx the format. The file is yet another example of the incapacities of versions of Word and Excel to open one another's files of the nominally same extension, in this case ".xlsx maybe or not". Among the worst, "Works" does not and never did. Across all programs by all publishers an extreme example of variation in file format arises among the forty-odd "flavors" of the nominally standardized graphical .tif format.

An apology goes to you, andysdesigns, for ruffling your feathers. None goes to Google or Microsoft.

The ods version is complaining about a header in the Category sheet now, and this is just from opening the sheet exported from OC, adding in the DATA VALUES ONLY and saving as ods and trying to import... and the clencher is that I'm NOT editing the Category sheet NOR the headers at ALL.

There are serious problems with this software, when I can export a sheet, reimport it immediately, and it works - then open the exported sheet, dump in the data, save as ods - WITHOUT TOUCHING the headers and it NOT WORK.

http://thevinewinecellar.com/export-vinewine.ods

(1) If you're not using Excel itself to open, manipulate, and save .xlsx, then whatever happens to it is not the tool's fault, blame instead the software used to open, manipulate, and save it. Sheets and Calc are not Excel itself. The tool expects native .xlsx, manipulated and saved in Excel itself, and accommodates deviations but not at the tool's own risk. If you don't want to use or can't use Excel, then you'll risk deviations (old copies can be found at modest expense). File conversions (of all sorts) are a classic and unavoidable problem when the result is, "Close but no cigar."

(2) Formats:
a/ http://office.microsoft.com/en-us/excel ... 40050.aspx
Microsoft's own but cursory rundown.
b/ http://spreadsheetpage.com/index.php/re ... n_history/
Other rundown.
c/ http://stackoverflow.com/questions/1666 ... excel-2007
"OpenOffice Calc only opens, but doesn't save xlsx. LibreOffice Calc also saves as xlsx, but the result will probably look weird with Excel. If you want to get the best possible compatibility, [. . .] SoftMaker's Lite-Version FreeOffice which costs nothing, plus, btw., is much faster and smaller than OpenOffice and LibreOffice."
d/ http://office.microsoft.com/en-us/excel ... 55787.aspx
"The table below shows which Excel features are fully, partially, or not supported in the OpenDocument Spreadsheet (.ods) format."

(3) Conversions (among many search dumps):
a/ https://www.google.com/search?q=convert ... =firefox-a
b/ https://www.google.com/search?q=convert ... S:official

(4) Typical conversions via Save, SaveAs, Export, and various on-line converters are:
xlsx to csv (Comma Separated Values)
xlsx to html (Hypertext Markup Language)
xlsx to mdb (Microsoft Access Database)
xlsx to ods (OpenDocument spreadsheet)
xlsx to pdf (Portable Document Format)
xlsx to rtf (Rich Text Format)
xlsx to xls (Microsoft Excel Spreadsheet)
xlsx to xml (Extensible Markup Language)
and vice-versa

andysdesigns wrote:The ods version is complaining about a header in the Category sheet now, and this is just from opening the sheet exported from OC, adding in the DATA VALUES ONLY and saving as ods and trying to import... and the clencher is that I'm NOT editing the Category sheet NOR the headers at ALL.

There are serious problems with this software, when I can export a sheet, reimport it immediately, and it works - then open the exported sheet, dump in the data, save as ods - WITHOUT TOUCHING the headers and it NOT WORK.

http://thevinewinecellar.com/export-vinewine.ods

Please give us some more details abouit what exactly you did. When I open your http://thevinewinecellar.com/export-vinewine.ods in OpenOffice I can clearly see that in your 'Categories' worksheet your Q1 cell (status enabled) is corrupted. Also, in your 'Products' worksheet, your cells Z1 and AA1 are corrupted.

Have you tried OpenOffice or LibreOffice? The issue is with your spreadsheet software!

In addition to file format, in spreadsheets (as in word processor files and even in pure ascii text files) there are document formats, here specifically cell, row, and column formats (plus those for tabs and other aspects), which hold potential to cause problems according to how you (re)set any or all of the program preferences, the worksheet (or similar) preferences, and the formats of particular cells, rows, and columns. For example, in any cell the usually default, general-purpose, usually named "General" format autosenses whether an entered number is decimal, money, date, time, telephone, text, forumla, value, etc., and it may sometimes booboo or not in autosensing the input but the "General" cell format is NOT numerically pure enough for database "ID" numbers. If you force hard formats for those (decimal, money, date, time, telephone, text, forumla, value, etc.), they will hold. They should but may not SaveAs or Export that way into non-native formats from any given spreadsheet program. You still need to keep an eye on whether adding rows or columns reverts to general-purpose defaults. You can usually spot that by eye, and resolve it by copying and pasting onto it ONLY the known good cell, row, or column format of an adjoining or nearby cell, row, or column. You seem to be running amok of cell, row, and column formats, notwithstanding file formats.

Export straight from OC, open in OpenOffice and the statusenabled header is automatically corrupted - trying to fix it didn't help

So I just got a copy of Excel 2010 and that did the trick...

Good. We've all been there; native usually works, sometimes even native doesn't work. When you're satisfied, go ahead and edit your opening title Solved.

Meanwhile, we have some other new wrinkles to ponder for the sake of dealing with whatever is next new and wonderful that wasn't a problem beforehand.

Thank-you for this wonderful tool however I am beside myself at this point. I have been trying to locate information about my problem and have tried everything imaginable but having no luck. I keep getting this message in my error log
'"Export/Import: Invalid header in the Products worksheet". I can export fine, have even imported products back. At first when I tried to import it imported everything but the images. I exported another sheet after adding products by hand from the same image folder as the other products I was trying to import. I made sure that the image names matched. Now I keep getting the message about the header. I am using opencart version 1.5.6.1 and have the appropriate export/import tool. I did not change the hea dings, just added new products under the ones exported. I checked to make sure the formatting was the same. Please help me!!!