Summary of methods for importing Excel data tables into MySQL using the command line

Importing MySQL from an Excel datasheet has been done several times, but every time you encounter a variety of problems: invalid UTF8 character string, data too long, ..., wasted a lot of time

To improve efficiency, it is time to specify the SOP for data import:

1. Prepare a. txt file

1.1 Data to be imported (without headers) copied from the worksheet, pasted into a new Excel datasheet (avoid source data)

1.2 Paste Note: Use the right-paste as value option to filter out unwanted formatting from the source data table (for example, font color, bold, and so on)

1.3 Save the newly created Excel document (with only one datasheet) as Unicode text (tried. csv format, bad)

1.4 Close the Save as Excel document, in the Explorer, open the document in Notepad, click the "Save as" button, find the encoding format "Unicode", change this to "UTF-8", overwrite the source file.

(This step is critical, and if missing, MySQL will report a confusing error when importing a. txt file: Invalid UTF8 character string ")

2. Create a table, import a. txt file

2.1 According to the structure of Excel data table, design MySQL table structure (feel the text format is better than varchar, do not care about the length of each field)

2.2 Creating tables: Create table term (type text, subtype text, en text, cn text, CHD text) character Set=utf8;

2.3 Using load Data InFile ' d:/sample.txt ' into Table terms lines terminated by ' \ r \ n '; statement import. txt, success!

Start enjoying database operations now!

Eggs:

To export the query results to a. txt file, use the statement: SELECT * into outfile ' d:\\term.txt ' from the term limit 10;

Summary of methods for importing Excel data tables into MySQL using the command line