Export and import from MySQL to Excel and back

• Project ID:

  427762

• Project Type:

  Fixed

• Budget:

  $30-$250 USD
  

Project Description:

I would like a PHP script to be developed that will be able to export the data from a MySQL table (with product information from a webshop) to an Excel readable file. The data in this file must then be edited in Excel and via the same PHP script be inserted in the database again. Data that has been altered should be UPDATED (this can be done on basis of the unique ID of each entry). New data (= rows without an ID) that have been added to the Excel file should be treated as new products and therefore be INSERTED into the products table.

This script is to be used for the users of a webshop, therefore it should be really easy to use and, most importantly, be FOOL-PROOF. The users of this script are to be regarded as novices with a basic knowledge of Excel at the most. This script is aimed at facilitating these users so they don’t have to update or insert their products using the much slower backend of their webshop.

Requirements:
- Im- and exporting of the file should be fool-proof, preferably containing no extra steps besides downloading the file, opening and editing it with Excel and then importing it again. If any extra steps are needed (e.g. ‘Save as… XML data’ to save the data properly from within Excel) than this should be *clearly* mentioned, including how to do it.
- Importing a file that has the wrong file format MUST NOT in any way corrupt the table with products. If the users attempts such a thing, a clear error explaining the problem should be given to the user.
- Excel file should be able to be edited and saved in the correct format using both Excel 2003 and 2007. (And earlier Excel versions if possible, though not mandatory.)

Things to keep in mind:
- The users might work with both US/English versions of Excel as well as other localized versions, where prices are not stored using periods, but with comma’s. Prices are stored as FLOATS in the database however, so keep this in mind when importing.
- Also keep in mind that the description field (called ‘omschrijving’ in the example SQL file) can contain just about any character, including non space characters like tabs and line breaks and also characters like semi colons and HTML code in general. I’m telling you this because this is the main problem I faced myself when trying to build this script myself on the basis of CSV files.

End result should be a PHP file with an accompanying HTML file with a form for exporting (e.g. downloading) and importing the Excel compatible file.

Attached is an export of a products table as an example to work and test with.

Skills required:

PHP

Additional Files:

producten.sql