CODEWITHSUNDEEP
csvexportmysqldump
Emilio Nicolás
Emilio Nicolás

Reputation: 2534

MysqlDump to CSV format. (Enclosed string fields, first description row, no sql files)

I would like to know mysqldump parameters in order to get in a directory, every table in CSV format (, separated + a description first row). I don' want any other files like .sql.

For example, if I have only the following table in the schema

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `lastname` varchar(45) NOT NULL,
  `email` varchar(320) NOT NULL,
) ENGINE=InnoDB

In the output I would get only a user.csv file like this

id;name;lastname;email
1;"Mark";"Lenders";"[email protected]"
...

If possible, I also want to enclose only varchar fields.

I use this mysqldump call

mysqldump -u root -p -t -T /tmp dbschema --fields-enclosed-by=\" --fields-terminated-by=;

But I still have these mistakes:

Server has a Debian6 operating system.

Thanks!!

Upvotes: 0

Views: 1764

Answers (1)

Devart
Devart

Reputation: 122022

DELIMITER $$

CREATE PROCEDURE write_tables_to_csv()
BEGIN

  DECLARE ts, tn  VARCHAR(64);
  DECLARE cur CURSOR FOR SELECT table_schema
                              , table_name
                         FROM
                           information_schema.tables
                         WHERE
                           table_schema = 'test'
                           AND table_name LIKE 'table1%';
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  OPEN cur;
  REPEAT
    FETCH cur INTO ts, tn;
    IF NOT done THEN
      SET @sql = CONCAT('SELECT * FROM ', ts, '.', tn, ' INTO OUTFILE ''', tn, '.csv'' FIELDS OPTIONALLY ENCLOSED BY ''"''');
      PREPARE stmt1 FROM @sql;
      EXECUTE stmt1;
      DEALLOCATE PREPARE stmt1;
    END IF;
  UNTIL done
  END REPEAT;

  CLOSE cur;
END
$$

DELIMITER ;

This procedure reads table names, generates SELECT statements and executes them. Change the condition for your schema and tables.

EDIT

Also, you clould use Data Export tool in dbForge Studio for MySQL. It alows to export some tables in any format at once. CSV format writes column header, and string fields are enclosed by specified symbol.

Upvotes: 1

Related Questions