CODEWITHSUNDEEP
mysqlphpmyadmin
TheNone
TheNone

Reputation: 5802

Load CSV data in Mysql via phpmyadmin

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

Upvotes: 0

Views: 1421

Answers (3)

Vinothkumar Arputharaj
Vinothkumar Arputharaj

Reputation: 4569

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.

Upvotes: 0

Geoffrey
Geoffrey

Reputation: 5432

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.

Upvotes: 2

thewebguy
thewebguy

Reputation: 1520

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);

}

?>

Upvotes: 2

Related Questions