Load CSV data in Mysql via phpmyadmin

Question

I have a excel file like this:

      a
1 word1 : mean1
2 word2 : mean2
3 word3 : mean3
.
.

and I have saved this a cvs file. Can I import this cvs file to mysql with this table?

id   word   mean
1    word1  mean1
2    word2  mean2

I have tried CSV using LOAD DATA with Fields terminated by : and only ids imported. Thanks in advance

Geoffrey · Accepted Answer · 2010-12-16 18:03:13Z

2

Make a copy of your Excel file. In the copy:

delete the row with the a in it.
delete the column with the colons.
insert a headings row, so that the spreadsheet looks like the table you want in MySQL.

Export the copy to CSV.

Create a database in phpMyAdmin.

Use these settings on the Import page of phpMyAdmin.

In the Format of imported file section, select CSV.

In the Options section, set the following:

Fields terminated by , (after all, it is a CSV file with comma separated values)
Fields enclosed by "
Column names in first row checked

In the File to import section, browse for the file on your computer, wherever you saved it.

Click the Go button.

edited Dec 16, 2010 at 18:03

answered Dec 16, 2010 at 17:51

Geoffrey

5,42910 gold badges49 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

thewebguy · Accepted Answer · 2010-12-15 22:27:48Z

2

Your best bet is a short PHP script, something like this:

<?php

$fp = fopen('path-to-csv.csv','r');

while ($row = fgetcsv($fp)) {

$sql = "INSERT INTO `table` (`col1`,`col2`) VALUES ('{$row[0]}','{$row[1]')";
mysql_query($sql);

}

?>

answered Dec 15, 2010 at 22:27

thewebguy

1,51910 silver badges15 bronze badges

1 Comment

TheNone Over a year ago

I want to inset data thet separeted with ',' in two colomn.

Vinothkumar Arputharaj · Accepted Answer · 2012-12-21 11:48:07Z

0

use phpmyadmin

choose export as Excel 2007 XLSX Workbook

check the checkbox saying

Put fields names in the first row

Save as file. Thats all.

answered Dec 21, 2012 at 11:48

Vinothkumar Arputharaj

4,5694 gold badges31 silver badges38 bronze badges

Collectives™ on Stack Overflow

Load CSV data in Mysql via phpmyadmin

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related