Quickly solve the problem of garbled characters and jump lines in mysql exported scv files

Due to work reasons, it is often not possible to achieve full onlineization (that is, all data is completed online and no import or export is required), and exporting to Excel is often slower than practicing to become an immortal. Therefore, when we export database files to local use, we often use the method of exporting to CSV format.

The csv format also often has problems such as garbled Chinese characters or skipped lines when exported, which puts you in a dilemma. Your boss wants data, but you can’t export it, which is frustrating.

1. Problem: We originally wanted to export the data in the database as shown in the left picture to its original appearance, but it became a mess on the right;

2. Solution:

a: Export to csv the same way as before;

b: Select the csv file you exported, right-click and select [Notepad] to open it;

c: File-->Save As-->Encoding select UTF-8-->Save; (The file name and save type do not need to be changed. After clicking Save, it will prompt that the file already exists. Do you want to replace it? Just select [Yes])

d: Open the file you just saved, but this time choose to open it in Excel, and then the data will be clean, just like in your database (as shown below).

Supplementary knowledge: MySQL backup -- import and export -- and solve the garbled problem

1. Export the entire database

mysqldump -u username -p database name > exported file name

mysqldump -u dbuser -p dbname > dbname.sql

2. Export a table

mysqldump -u username -p database name table name > exported file name

mysqldump -u dbuser -p dbname users> dbname_users.sql

3. Export a database structure

mysqldump -u dbuser -p -d --add-drop-table dbname >d:/dbname_db.sql

-d no data --add-drop-table add a drop table before each create statement

4. Import the database

1) Restore from file to database

mysql -uroot -p dbName < fileName

For example: restore from test.sql to database db1

mysql -uroot -p db1 <test.sql

2) Use the common source command to enter the MySQL database console.

For example, mysql -u root -p mysql>use database and then use the source command

The following parameters are script files (such as .sql used here)

mysql>source d:/dbname.sql

5. Solve the problem of garbled export

For example:

mysqldump -uroot -p --default-character-set=utf8 dbname tablename > bak.sql

Then you should also use --default-character-set=utf8 when importing data:

mysql -uroot -p --default-character-set=utf8 dbname < bak.sql

After unified encoding, the garbled characters problem in MySQL data migration is solved.

The above article on how to quickly solve the problem of garbled characters and skipped lines in mysql exported scv files is all the content that the editor shares with you. I hope it can give you a reference, and I also hope that you will support 123WORDPRESS.COM.