Is anybody know how to convert Excel to MySql?
I got a large data of excel which I want to insert into MySql table.
but I could not figure it out how to do that?
any suggestions
thanks
Save it as a CSV file, then use a LOAD DATA INFILE query to import it.
I’ve been puzzled by how to do this correctly.
I have 216 records in a CSV. I’ve created my fields in MySQL to match to fields created in the Excel file.
After I attempt to import it with CSV using LOAD DATA, it imports 216 records (as it should) but all fields are ‘NULL’.
Attached are two samples. One is the Excel File, and the other is the result after importing into MySQL. I’m not familiar with this method of importing, and would appreciate any assistance.
Thank you!
please do a SHOW CREATE TABLE for the table
then show the LOAD DATA command that you used
Thank you for your prompt response!
You’ll have to forgive me, but I just simply went to phpmyadmin, accessed the table I wanted to Import to (which was all ready created), then I went to ‘Import’, selected the CSV file, selected ‘CSV using LOAD DATA’, kept the defaults of:
(unchecked) Replace table data with file
(unchecked) Ignore duplicate rows
Fields terminated by ;
Fields enclosed by "
Fields escaped by \
Lines terminated by auto
(checked) use LOCAL keyword
Attached is an image of my table.
Is the semicolon really the delimiter in your file?
No, the comma.
then you should change this –
fields terminated by ;
to this –
fields terminated by [COLOR="Red"]','[/COLOR]
Ms office excel 2007 offers you the connectivity to all database . You have ODBC connections where you can connect to external source and fetch data into excel. All you need is the credential of database.
aleserlee, it seems you have overlooked the fact that the data here is being extracted from excel, not fetched into excel
That worked absolutely SPLENDID! Thank you! Now I’ll know how to do this in the future!
You guys at Sitepoint are awesome!