Looking for ways to export or import data from mysql?
If you are interested in importing data but you don’t want to import the structure of the data, you can export data to a csv file and then re-import the file into the database by loading the data file.
IMPORTING DATA FROM CSV FILE (MYSQL):
Fields can be terminated by tab ‘\t’ or a single quote ‘\” or a double quote ‘\”‘. Although I highly recommend that you use either commas or tabs. Using other delimiters may cause you some headaches with the right situation in the future.
If you don’t need quotes around all fields (e.g. numeric fields) then change “ENCLOSED BY” to “OPTIONALLY ENCLOSED BY” and MySQL will only put quotes around the fields that need them. Some systems require all fields in a CSV file to have quotes around them so you may need to export the data with quotes around them all depending on your requirements.
EXPORTING DATA INTO CSV FILE (MYSQL);
If you require only specific fields to export, replace “*” with the fields. For example: SELECT field1, field2, field3 INTO OUTFILE…
If you get an error like:
It’s possible that you don’t have the proper permissions to write to the directory or that you have a file with that name already there. You will not be able to overwrite the file if it does exists.
Dump data and table to a file
Example: mysqldump -u someuser -p database table > filename.sql
Dump the entire database
Dump table structure and no data
Very nice article and straight to the point. I am not sure if this is actually the best place to ask but do you people have any thoughts on where to hire some professional writers? Thx
Hi there. Just simply want to leave a short note and tell you that I’ve enjoyed leafing through your specific web-site and am endorsing it to my buddies. Keep up the good work! Thanks.