Update A Column Using Csv

[g6h7b5]

There's any way of updating just one column using csv source? And leave the rest untouched using SQLyog? Please say YES and How 😉

“Navicat 8.1 for MySQL” and “DBACentral Builder for MySQL 2.0 beta” does this job very well with many option of importation.

[peterlaursen]

No – I cannot think of any way of doing this.

You will have to write some kind of script/application that reads the file and generates UPDATE statements and sends to server immediately or saves to a file.

Another way could (possibly) be to import to a temporary table and next write a Stored Procedure that generates similar UPDATES.

[peterlaursen]

Another use asked the same here:

http://www.webyog.com/forums//index.php?showtopic=3842

For his case we found a solution!

[g6h7b5]

If Navicat can, why SQLyog developer doesn't?

Could developers give me an answer? There's a little possibility to develop this? I love SQLyog, but that option is important to me! In Navicat they call this function “Append or update”…

[peterlaursen]

But you can import complete rows without overwriting existing data using the 'Migration Tool' (ensure that table has a Primary key).

In 8.1 (RC) we added an option to 'import from a file' directly without preconfiguring a DSN in the connection manager. But it is still an ENTERPRISE feature.

Anyway: read through the old post I linked to and try to describe the problem equally as simple (what does the table look like and what does the file look like), and there will undoubtedly be a solution too!

[g6h7b5]

Still updating all rows!

[peterlaursen]

I did not understand your last comment.

To do this (if I understand) individual UPDATE statements must be executed against the original table. The content of the file will need to be parsed by a client to decide what and where to update. Most users would write a simple script doing this. You find an example in this discussion: http://www.webyog.com/forums//index.php?showtopic=4603. But also a Stored Procedure will do after import to a temporary table.

My point is that there will be so many different situations that a general tool will not solve them all. Quite the opposite I think we will get complaints that “it does not do as I want it”. People will have other expectations that what such tool actually deos and may even damage their data!

I asked you to describe with a simple example to describe what you want. Something like this:

If table contains data

(1),('a'),('x'),('g6h7b5')

(2),('b'),('y'),('peter')

(3),('c'),('z'),('webyog')

and If PK is defined on 1st and 3rd column (?), what would then a single line in the CSV-file look like and what would the table look like after you did what you want?

[g6h7b5]

Ok, with the uploaded files I think you will have my vision.

I want to update with the data of the CSV without NULL my others fields.

insc is the PK, just want to update the field nome, right?

http://rapidshare.com/files/239240271/Desktop.rar

[peterlaursen]

1) The link to the Stored Procedure in the other Forums post needed not much modification (only changing column naems and correcting a typo). This will work (on 4.1 + servers) with your example:

USE `test`;

— we prepare a temporary table where we import the CSV

DROP TABLE IF EXISTS `bd_delete`;

CREATE TABLE `bd_delete` (

`insc` INT(8) NOT NULL DEFAULT '0',

`nome` TEXT,

PRIMARY KEY (`insc`)

) ENGINE=MYISAM DEFAULT CHARSET=latin1;

LOAD DATA LOCAL INFILE 'D:\csvcase\ll.csv' INTO TABLE `test`.`bd_delete` FIELDS ESCAPED BY '\' TERMINATED BY ',' ENCLOSED BY '”' LINES TERMINATED BY 'rn' (`insc`, `nome`);

— create a Stored Procedure that reads from the temporary table and updates the permanent table

DROP PROCEDURE IF EXISTS `test`.`csvfix`;

DELIMITER $$

CREATE PROCEDURE `test`.`csvfix`()

BEGIN

DECLARE my_id INTEGER;

WHILE ((SELECT COUNT(*) FROM `test`.`bd_delete`) > 0) DO

SET my_id = (SELECT `insc` FROM `bd_delete` ORDER BY `insc` LIMIT 1);

UPDATE `bd` SET `nome`= (SELECT `nome` FROM `bd_delete` ORDER BY `insc` LIMIT 1)

WHERE `insc` = (SELECT `insc` FROM `bd_delete` ORDER BY `insc` LIMIT 1);

DELETE FROM `bd_delete` WHERE `insc` = my_id;

END WHILE;

END$$

DELIMITER ;

— let us see what data are there: the permanent table

SELECT insc, nome, pontos FROM bd WHERE insc IN (67126137, 67130667, 67107434);

/*

insc nome pontos

---

---

---

67107434 Paulo Igor Bosco Silva 74

67126137 Roberio Rodrigues Araujo (candidato Sub Judice) 74

67130667 Welton Da Costa Marcal 74

*/

— the temporary table

SELECT insc, nome FROM bd_delete WHERE insc IN (67126137, 67130667, 67107434);

/*

insc nome

---

---

67107434 Peter Lausen

67130667 Joaquim

67126137 Paulino

*/

— execute the stored procedure

CALL `test`.`csvfix`();

— verification that UPDATES were the right one:

SELECT insc, nome, pontos FROM bd WHERE insc IN (67126137, 67130667, 67107434);

/*

insc nome pontos

---

---

---

67107434 Peter Lausen 74

67126137 Paulino 74

67130667 Joaquim 74

*/

— cleanup

DROP PROCEDURE IF EXISTS `test`.`csvfix`;

DROP TABLE IF EXISTS `bd_delete`;

— note: this particular example handles UPDATES only .. if you sometimes have new columns too you need use INSERT .. ON DUPLICATE KEY UPDATE …

— but I really do not understand why you copy back and forth to Excel so that all this is necessary. Wasn't it easier to develop a small database application that could do to the database what you now do in Excel?

2) A few general comments: your schema has some common beginner's mitakes, I think

first: You are using TEXT types for short strings types where you should use char(n). We see that often when users use an old, crap (I mean more crappy than what it needs to be!) and 'cracked' (?) Navicat version. TEXT types in MySQL are intended for very long strings (it is similar to an Access 'note' and not an Access 'text' .. why may be one reason why it is confused)

second: You use integer types like an INT(8). This will not be shorter than a plain INT in the database .. only the display will be truncated when the server returns it to a client. If that is what you want it is OK, but in 99,9% of cases we see this users just do not understand the length specification of an INTEGER type!

BTW: how can it be that you are still using “SQLyog Enterprise Trial – MySQL GUI v7.5 Beta2″ ?

3) With this particular example some 'replace into table from CSV' would not be hard to code as a simple GUI implementation. User would need to map each column in the file to a column in the table. The PK-columns will be used for the WHERE clause and the rest for UPDATE .. SET. But that was also basically what I did with the SP example. Actually a small script/application reading the file would be even simpler (but I am not sure if you have a scripting enviroment from where you can connect to MySQL). I did not promise that we will do it, but let us discuss! Personally I think users would be better off learning to write such very simple logic in some sort of code – but I realize that some people don't want to!

[peterlaursen]

correction: you will of course need MySQL 5.0 or higher to use a Stored Procedure. But no problem as you use 5.1.30.

[verra99]

Thanks for your post. It’s really useful :roll::D

simulationcredit

[navyashree.r]

Hi,

Issue “Update A Column Using CSV” added in our tracker,

http://code.google.com/p/sqlyog/issues/detail?id=1105

Thank You for your interest.

Regards,

Navya

[Author]

Posts