Magento & Excel
A factor that has emerged recently from me as a developer regarding Magento is that is doesn’t support Excel documents as a CSV files. This means that you cannot export a CSV file from Magento, modify or even just open it in Excel and then expect to re-import it. Magento requires all the strings in the CSV to be enclosed with double quotation marks, something which Excel discreetly removes. This means that you’ll either need to modify your files in something like Notepad or TextEdit which looks something like this:
Or download a good CSV editor, which I’m currently looking into. Suffice to say, the Notepad option isn’t the most appealing.
I don’t mind the fact that this is the way it has to be, I’m just annoyed that it wasn’t made clearer by Magento. It is only mentioned passingly in the wall of text that is the Magento user guide “NOTE1 Do not modify the CSV file with Microsoft Excel” – I wouldn’t say that this is made clear enough – why not put a line of text in the admin area so that no-one misses it? Excel is probably one of the most recognised CSV editors afterall…
Although Magento Product Importing has moved since this blog post in 2009, issues still arise when importing CSVs edited in Excel. One method that we use is to copy and paste the data into SmokeyLake’s ‘Text Write Program’ which generates a Magento ‘import friendly’ text file which you can convert into a CSV file. A nice little solution.