Import/Export database + Add new field in CSV format data file

Recently been busy in the laboratory, did not properly update the blog, in the process of grasping the package, encountered a lot of problems.

Because I often use Wireshark to export the capture information to a CSV file, here's a simple mark about 2 ways to import/export a CSV file to a database:

A. Simple, but slightly slower way to use Navicat for MySQLto import the database:

The process is as follows:

The installation is complete and the connection interface is configured as follows:

New Database

Go to import Wizard

Select the type of import:

Then select the file as the data source, here is the package that I have grasped before, 300w+ strip capture record

The default settings are taken directly here

Same default

I set the following to indicate that a new table

Here is the program based on the content of the speculation, note to modify the following data type, and adjust as necessary, here is a bit not very good, at present only found through the arrow keys "down" to add, no other operation, if the added data record is not filled, then automatically deleted.

I reduced the packet information to just what I needed so that the information crawled from 1G to 200+m

Half an hour, 300w+ records.

This slow, who use who know ~ ~ ~ ~

Two. Use SQL statements to import, fast!

Using SQL statements can quickly achieve the effect of a large number of data import databases. Examples are as follows:

LoadData infile'.. \\uploads\\test.csv' into TableSampletablenamefields terminated by ','Optionally enclosed by '"'Escaped by ','Lines terminated by '\ r \ n'Ignore1Lines

Explain:

1. Load data infile + file path: Here the relative path is used, the MySQL installed on my machine is in ' C:\ProgramData\MySQL\MySQL Server 5.7 ', in general, the first line in the CSV file is typically the name of each column property, Therefore, the first row should be omitted when importing the database
2. The sampletablename in the sentence should be replaced with the name of the corresponding table
3. CSV file, the domain is generally separated by commas, field values are usually wrapped in a pair of double quotation marks, line wrapping generally with ' \ r \ n ' (Windows), so I found that the general escaped by+ comma and lines terminated by+ ' \ r \ n ' has a corresponding relationship, If the escaped by "", that is, the quotation marks are separated, then only the lines terminated by is changed to ' \ n ' to import, and at this time can not ignore 1 lines;

Common errors:

Error Code 1290:

I have a MySQL5.7 installed on my machine,

MySQL5.7 the state of the default run is:

Error code:1290. The MySQL server is running with the--SECURE-FILE-PRIV option so it cannot execute this statement

This is because the files that need to be imported are no longer in the secure folder that MySQL considers, so MySQL rejects the import operation.

Use the following statement to see the Security folder currently considered by MySQL and put the file in a folder

 like ' Secure_file_priv ';

Import/Export database + Add new field in CSV format data file