By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,304 Members | 1,253 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,304 IT Pros & Developers. It's quick & easy.

From CSV To MySQL Table

P: n/a
Hi, I've a CSV file with 3 columns

ID - Price1 - Price2

How I would like to update the records of a MySQL tables that has the
same ID of the CSV files.

How Can I do this? Read from CSV and update MySQL table?

Thanks
May 14 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Hi Valeria,

your idea/question to read from csv and update the table is the right
way.

Open your csv file and read each line using a loop. Check inside your
loop, if an entry in the database exists. if so update it, otherwhise
continue the loop or insert an entry.

See follwing pages for detailed information:
- http://de2.php.net/manual/en/function.fgetcsv.php
- http://de2.php.net/manual/en/ref.mysql.php or http://de2.php.net/manual/en/ref.mysqli.php

An simple example could be:
[snip]
$link = mysql_connect($mysqlHost, $user, $password) or die('Could not
connect: ' . mysql_error());
$handle = fopen("myfile.csv", "r");

// loop content of csv file, using comma as delemiter
while (($data = fgetcsv($handle, 1000, ",")) !== false) {
$id = (int) $data[0];
$price = floatval($data[1]);
$price2 = floatval($data[2]);

$query = 'SELECT id FROM my_table';
if (!$result = mysql_query($query)) {
continue;
}

if ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
// entry exists update
$query = "UPDATE my_table SET price='$price', price2='price2'
WHERE id=$id";
mysql_query($query);
if (mysql_affected_rows() <= 0) {
// no rows where affected by update query
}
} else {
// entry don't exists continue or insert...
}

mysql_free_result($result);
}

fclose($handle);
mysql_close($link);
[/snap]

purcaholic

May 14 '07 #2

P: n/a
your idea/question to read from csv and update the table is the right
way.

Open your csv file and read each line using a loop. Check inside your
loop, if an entry in the database exists. if so update it, otherwhise
continue the loop or insert an entry.
As an alternative, you can define a (temporary) table based on your CSV
file in MySQL (you'll have to upload the csv file to the database
server), and use an update statement. MySQL has a CSV engine for these
purposes. If this is a regular action and not a one-time only action,
you'll be better of using PHP though.

Best regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/
May 14 '07 #3

P: n/a
In article <46***********************@news.xs4all.nl>, Willem Bogaerts
wrote:
If this is a regular action and not a one-time only action,
you'll be better of using PHP though.
Why so? I would have thought it would be the reverse.
Surely such a simple (looking) update as this would be quicker and more
efficient joining tables, even temporary ones, than in php?

Regards
Mark

May 14 '07 #4

P: n/a
Why so? I would have thought it would be the reverse.
Surely such a simple (looking) update as this would be quicker and more
efficient joining tables, even temporary ones, than in php?
If you would do this from a CRON job or a PHP page, it needs the rights
to create a table. That is something I like to avoid. A one-time action
as an administrator is less dangerous, as you don't "run unattended"
yourself.

Best regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/
May 14 '07 #5

P: n/a
In article <46*********************@news.xs4all.nl>, Willem Bogaerts wrote:
If you would do this from a CRON job or a PHP page, it needs the rights
to create a table. That is something I like to avoid. A one-time action
as an administrator is less dangerous, as you don't "run unattended"
yourself.
Ah, I see. Thank you for the explanation.

Regards
Mark

May 15 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.