3

I am looking for a program that can:

  • import a Microsoft Excel spreadsheet into a MySQL table
  • support cells containing line breaks
  • support cells containing ' (and if possible any other character)

If possible:

  • Free
  • Windows 7
  • allows to specify which columns we want to import
7
  • Do you care what language? May 27, 2014 at 21:28
  • 1
    @NickWilde I would prefer in English but I am surrounded with Europeans and Asians so I should be able to cope with most languages. Same for programming languages (preference for Python/C++/Java/MATLAB/R). May 27, 2014 at 21:56
  • 1
    Might not be exactly what you're looking for, but I've used excel macros in the past to generate INSERT statements (in a separate sheet), which then could easily be exported as *.sql files. Wouldn't that be an option? At least it would cover all requirements you've listed ;)
    – Izzy
    May 27, 2014 at 22:27
  • 1
    Excel macros are good too! :) May 27, 2014 at 22:30
  • 1
    Ah yeah I was presuming Unicode support unless specified. I was definitely thinking about the programming language. PHP I see isn't in you're preferred list, but as you say you an probably cope I'll write something up this evening for a PHP solution. May 28, 2014 at 0:03

2 Answers 2

4

You can simply export the spreadsheet in Microsoft Excel as a CSV, then use MySQL Workbench's import function:

enter image description here

This solution supports support cells containing line breaks and support cells containing '. However, it doesn't support all special characters as the Excel to CSV export will mangle the special characters that aren't ASCII characters. To perform an Excel to CSV conversion with UTF8 encoding, one solution is to first import the Microsoft Excel spreadsheet into Google drive, then export the Google Drive spreadsheet to a CSV, then use MySQL Workbench's import function.

3

I wrote an application for importing an Excel spreadsheet into MySQL. It meets all your other requirements except specifying columns to import, but it will import everything very fast and you can ignore or remove columns you don't want easily enough. In my biased opinion it is simply the most accurate, easiest and fastest way to import Excel spreadsheets into MySQL.

You can verify that it works with your data before purchase. It will load all your data except some random records will be blank until licensed. There are 2 versions of the program available. An easy to use GUI version and a command-line version which will allow you to automate and append your data to the table. It can handle all versions of Excel and MySQL Server. The process can be fully automated. Once you have the command-line version of the program called from a bat file or your favorite programming language, you can use windows task scheduler to schedule it. Excel2MySQL is capable of handling huge Excel spreadsheets.

http://excel2mysql.net

screenshot

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.