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.
- Save your Excel data as a csv file (In Excel 2007 using Save As)
- 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.
- 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.)
- 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);
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)]