|
Here's a good one. Sometimes in the course of performing administrative duties on a MySQL database, using phpMyAdmin, you are required to import and export data. And sometimes, when importing using simple text files (.txt / .sql), the import doesn't go so well. Or, perhaps you're looking to simply export data from a MySQL database to another database after modifying the data using Microsoft Excel or Open Office Calc and converting it to Comma Separated Value (CSV) and importing it using the .CSV file. Well, there's hope.
First, began by exporting the data from your MySQL database phpMyAdmin tool. Using phpMyAdmin, export your required data, making the selection to export as a CSV file. Next, open the database CSV file. Using Open Office Calc seems to be a be easier software application to use here, since when opening the file, the program automatically requires you to filter the delimiters, but that's another article for another day. Now, remove the headers from the generated CSV file. If there is any empty data, delete it.
Being that we have that out of the way, copy and paste the following query into the SQL query window.
LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [your-table-name]
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);
The file you are importing is "your-file.csv" and the table you are importing into is "your-table-name." The enclosed by and lines terminated by are optional but can assist you should you have columns enclosed with double quotes, which is standard when exporting from Excel, which is amid a few reasons why you may want to use Open Office.
MEDIA TAGS: mysql phpmyadmin php sql dba rename table name tables query queries CSV excel open office |
