Import Excel Data into MySQL in 5 Easy Steps

139 Comments

This is probably nothing new to many, but I spent quite some time to figure it out so I thought I’d post my notes on it.

To import data from Excel (or any other program that can produce a text file) is very simple using the LOAD DATA command from the MySQL Command prompt.

  1. Save your Excel data as a csv file (In Excel 2007 using Save As)
  2. Check the saved file using a text editor such as Notepad to see what it actually looks like, i.e. what delimiter was used etc.
  3. Start the MySQL Command Prompt (I’m lazy so I usually do this from the MySQL Query Browser – Tools – MySQL Command Line Client to avoid having to enter username and password etc.)
  4. Enter this command:
    LOAD DATA LOCAL INFILE 'C:\\temp\\yourfile.csv' INTO TABLE database.table FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' (field1, field2);
  5. Done!

Very quick and simple once you know it 🙂

Some notes from my own import – may not apply to you if you run a different language version, MySQL version, Excel version etc…

  • TERMINATED BY – this is why I included step 2. I thought a csv would default to comma separated but at least in my case semicolon was the deafult
  • ENCLOSED BY – my data was not enclosed by anything so I left this as empty string ”
  • LINES TERMINATED BY – at first I tried with only ‘\n’ but had to add the ‘\r’ to get rid of a carriage return character being imported into the database
  • Also make sure that if you do not import into the primary key field/column that it has auto increment on, otherwhise only the first row will be imported

[Update: I had some trouble getting double quotation marks etc when exporting some Excel data and found a few old but useful Excel export macros in VBA]

[Update: To change the list separator in Excel:
1. First search your Excel file for the character you plan to use so it does not exist somewhere in your data.
2. Go into Windows Control Panel + Regional and Language + Formats Tab click Additional Settings + List separator (Windows 7)]

Facebook Photos to your Windows Mobile Cell Phone

No Comments

I just stumbled over this cool tool – OutSync – from Mel Sampat who’s a Windows Mobile Program Manager at Microsoft.

It allows you to sync the photos of your Facebook contacts to Outlook and then on to your Windows mobile cell phone (or other phone if you have sync software that synchronizes photos)

I always wanted to have some photos in Outlook and my phone but never really got around to trying it out. Also it would be too much work to get photos of everyone but this tool solves that quite nicely – at least if you use Facebook.

It’s easy to install and worked right away for me on my Vista and Outlook 2007 setup.

There are some things that I would like to see improved, mainly that it only matches on name so your contact has to have the exact same spelling in Outlook as in Facebook. It should be able to match on email (but I think the problem here is that the Facebook API does not allow this) or let you do a manual match. A feature that is listed as “coming soon” is birthday synchronization.

I also wish there was a similar tool for syncing Outlook and Live Messenger, but maybe there already is?