How to import data from an Excel table to MySQL

 

How to import data from an Excel table to MySQL

 

Prerequisites

1.

The Excel Data is correct and the table in the database is created successfully. For example:

The Excel table contains the field ID, findtime, name, summary, type, category, detail, affected_system, attack_scenarios, ease_of_attack, SMS, MMS, SP, xdown_software, sig_sid, correct_action, 17 CIDR blocks. Create the following table in the database:

Drop table if exists 'virus _ signature ';

Create Table 'virus _ signature '(

'Id' int (11) not null auto_increment,

'Findtime' timestamp not null default current_timestamp on update current_timestamp,

'Name' varchar (255) not null,

'Summary 'varchar (100) not null,

'Category 'int (11) unsigned not null default '0 ',

'Detail' text not null,

'Affected _ system' varchar (200) not null,

'Attack _ scenarios 'text not null,

'Prop _ of_attack' varchar (30) not null,

'Ms' int (10) unsigned not null,

'Mms 'int (10) unsigned not null,

'SP _ number' int (10) unsigned not null,

'Xdown _ soft' int (10) unsigned not null,

'Sig _ SID 'int (10) unsigned not null,

'Correct _ action' varchar (200) not null,

Primary Key ('id ')

) Engine = MyISAM auto_increment = 711 default charset = utf8;

 

 

2. Assume that the virus_signature.xls path is: C:/virus_signature.xls;

Assume that the database information is as follows:

HOST: 192.168.4.82,

Usename: Root,

Passwords: 123456,

Database: Perseus,

Table: virus_signature.

 

Import Method

1. Install navicat for MySQL on the FTP.

 

2. Use navicat for MySQL to connect to the Perseus database.

 

3. in "navicat for MySQL", select "table" in the Perseus database --> right-click and choose "Import wizard" --> select "Excel file" and click "Next" --> import from c: /virus_signature.xls --> select the name of the virus_signature table and click Next --> enter the items on the tab as needed, enter "2" in the "first column" and click "Next" --> directly next --> check whether the "target column" corresponds to "source column, click Next next. select items on the tab as needed and click Next.
Start --> check whether the running result is correct

Export Method

Run the following command on 192.168.4.28:

Mysqldump-uroot-p123456 Perseus virus_signature> virus_signature. SQL