The following image explains it more clearly: After filling the detail, click on the Save button. Here, we need to provide a filename and its format. Finally, a new dialog box will be displayed.The recordset is used for the result set. Then, in the result panel, click "export recordset to an external file" option.Run the statement/query and get its result set.To do this, we need to follow the below steps: It allows us to export the result set of a statement to a CSV format in our local system. Workbench is a GUI tool to work with MySQL database without using a command-line tool. If we do not want to access the database server for exporting the CSV file, MySQL provides another way, i.e., using MySQL Workbench. SELECT Name, Email, Phone, IFNULL(Phone, 'N/A') FROM employee_detailĮxport Table into CSV Format Using MySQL Workbench We can change this style by adding OPTIONALLY clause before ENCLOSED BY: In the image, we can see that the numeric fields are in quotation marks. When we open this file, it will look like below image: We can verify the CSV file created in the specified location or not by navigating to a given path as follows: We can resolve this either delete the existing file on the specified location or rename the file name to create it in the same place. Thus, if we export the new CSV file with the same name and location, it cannot be created. The error message tells us that the specified file name already exists in the specified location. If we execute the same statement again, MySQL produces an error message that can be seen in the below output: It is because the specified table contains six rows only. We will get the following output, where we can see that six rows are affected. INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' SELECT Id, Name, Email, Phone, City FROM employee_detail To export the table data into a CSV file, we need to execute the query as follows: If we execute the SELECT statement, we will see the following output: Export MySQL data in CSV format using the SELECT INTO. INSERT INTO employee_detail ( Id, Name, Email, Phone, City) It is to ensure that we have a file privilege to use this syntax. This statement is a compliment of the LOAD DATA command, which is used to write data from a table and then export it into a specified file format on the server host. To export the table into a CSV file, we will use the SELECT INTO.OUTFILE statement. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |