MySQL excel导入

 说明：

1 因在测试发现如果用SQLyog导入数据需要下载excel驱动，因而选择Navicat

2 之前选择excel文件为xlsx 发现Navicat识别不了，因而转存为xls文件，测试OK

1   先将准备好的excel文件确认以下信息

建议列名采用英文定义

2  打开Navicat 

右击---导入向导

选择excel

 选择要导入的数据文件  注意此处要选择导入的那个子表  就是你存放数据的sheet  此处为sheet3

 

 

点击下一步

 

 点击下一步

点击下一步：

 点击开始

 导入成功后的显示

 数据库查看：

 如果修改表名的话可以运行：

mysql> alter table Sheet3 RENAME V_Machine_Info;

==============================================================================

导出后可以在数据库中将表备份导出

[root@db01-sa ~]# mysqldump -uroot -p123 vmware V_Machine_Info >/server/scripts/V_Machine_Info.sql;

-- MySQL dump 10.13  Distrib 5.6.38, for Linux (x86_64)
--
-- Host: localhost    Database: vmware
-- ------------------------------------------------------
-- Server version    5.6.38

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `V_Machine_Info`
--

DROP TABLE IF EXISTS `V_Machine_Info`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `V_Machine_Info` (
  `NAME` varchar(255) DEFAULT NULL,
  `STATUS` varchar(255) DEFAULT NULL,
  `IP1` varchar(255) DEFAULT NULL,
  `IP2` varchar(255) DEFAULT NULL,
  `PREPARED_SPACE` varchar(255) DEFAULT NULL,
  `USE_SPACE` varchar(255) DEFAULT NULL,
  `STORE1` varchar(255) DEFAULT NULL,
  `STORE2` varchar(255) DEFAULT NULL,
  `TYPE_MODEL` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `V_Machine_Info`
--

LOCK TABLES `V_Machine_Info` WRITE;
/*!40000 ALTER TABLE `V_Machine_Info` DISABLE KEYS */;
INSERT INTO `V_Machine_Info` VALUES ('金马PACS','已关闭电源','','','282.50 GB','246.93 GB','hus110-4','','虚拟机'),('棠埭机器人','已打开电源','172.30.100.20','172.20.91.72','508.13 GB','108.13 GB','v7000-1','','虚拟机'),('MikroTik RouterOS-v6.18','已关闭电源','','','1.25 GB','573.16 MB','hus110-1','','虚拟机'),('iRedoS','已打开电源','172.20.6.73','','121.59 GB','17.97 GB','vm-4','','虚拟机'),('win2008@oracle','已关闭电源','','','608.34 GB','109.69 GB','vm-1','','模板'),('czey-webserver','已打开电源','172.30.0.13','','208.13 GB','136.26 GB','datastore1(11)','','虚拟机'),('vcenter_new','已关闭电源','','','104.32 GB','44.23 GB','datastore1(10)','','虚拟机'),('hds_vm_manager','已打开电源','172.20.6.31','','62.09 GB','20.11 GB','datastore1(10)','','虚拟机'),('CTMS_Server','已打开电源','172.30.100.80','172.20.91.80','416.13 GB','274.81 GB','v7000-1','','虚拟机'),('2008维护','已打开电源','172.20.91.84','172.30.100.253','108.13 GB','108.13 GB','g200-20-1','','虚拟机'),('检验科细菌','已打开电源','172.20.91.130','','416.13 GB','128.91 GB','g200-20-2','','虚拟机'),('cluster','已打开电源','172.20.91.69','','1.10 TB','1.01 TB','hus110-1','hus110-2','虚拟机'),('预约系统','已打开电源','172.20.91.62','','304.15 GB','16.43 GB','hus110-3','','虚拟机'),('ESB服务接口1','已打开电源','172.20.91.63','','316.15 GB','265.76 GB','hus110-1','hus110-2','虚拟机'),('杀毒软件','已打开电源','172.30.100.109','172.20.91.109','104.13 GB','104.13 GB','v7000-1','','虚拟机'),('金马测试','已打开电源','172.30.100.125','172.20.91.125','116.13 GB','106.89 GB','vm-1','v7000-1','虚拟机'),('手术麻醉系统','已打开电源','172.20.91.66','','608.17 GB','243.29 GB','hus110-4','','虚拟机'),('短信平台','已打开电源','172.20.91.57','172.30.100.11','68.13 GB','64.09 GB','hus110-4','','虚拟机'),('金马PacsNew','已打开电源','172.20.91.70','','314.16 GB','314.15 GB','hus110-4','','虚拟机'),('ECNM3344','已关闭电源','','','148.26 GB','100.09 GB','hus110-4','','虚拟机'),('云影像','已打开电源','172.30.100.135','172.20.100.135','208.13 GB','111.27 GB','v7000-1','','虚拟机'),('HRP','已打开电源','172.20.91.50','','616.17 GB','616.16 GB','hus110-2','','虚拟机'),('掌上e+','已打开电源','172.30.100.10','172.20.91.51','416.15 GB','316.13 GB','v7000-1','','虚拟机'),('企业公众号','已打开电源','172.30.100.19','172.20.91.71','208.16 GB','128.62 GB','v7000-1','','虚拟机'),('Oracle11gToYj','已打开电源','172.20.91.101','','608.14 GB','608.14 GB','g200-20-1','','虚拟机'),('360杀毒（新）','已打开电源','172.30.100.120','172.20.91.120','104.13 GB','104.12 GB','hus110-3','vm-1','虚拟机'),('Ngnix','已关闭电源','','','104.28 GB','12.11 GB','hus110-1','','虚拟机'),('caigouzhongx-ftp','已打开电源','172.30.100.16','','1.02 TB','44.55 GB','hus110-4','vm-1','虚拟机'),('掌上e+_app_ios','已打开电源','172.30.100.14','172.20.91.60','158.17 GB','134.65 GB','hus110-4','','虚拟机'),('统一查询系统','已打开电源','172.20.91.52','','1.11 TB','873.86 GB','hus110-2','vm-1','虚拟机'),('脑卒中中心','已打开电源','172.30.100.21','172.20.91.77','508.13 GB','130.05 GB','v7000-1','','虚拟机'),('ESB-2管理中心','已打开电源','172.20.91.55','','156.16 GB','95.18 GB','hus110-4','','虚拟机'),('Test1.6','已打开电源','172.30.100.83','172.20.91.105','416.13 GB','121.37 GB','v7000-1','','虚拟机'),('ESB-省平台','已打开电源','172.20.91.61','','316.15 GB','146.87 GB','hus110-1','hus110-3','虚拟机'),('预约检查','已打开电源','172.20.91.67','','556.17 GB','160.30 GB','hus110-4','','虚拟机'),('微信数据库','已打开电源','172.20.91.73','','308.13 GB','23.33 GB','hus110-1','vm-1','虚拟机'),('财务科key','已打开电源','172.30.100.88','172.20.91.108','104.13 GB','104.13 GB','v7000-1','','虚拟机'),('易联众-支付宝前置','已打开电源','172.30.100.17','172.20.91.65','44.14 GB','40.14 GB','hus110-3','vm-1','虚拟机'),('ntp-server','已打开电源','172.20.91.54','172.30.100.12','51.13 GB','51.13 GB','hus110-4','','虚拟机'),('pacsnas','已打开电源','172.20.91.68','','68.48 TB','68.40 TB','hus110-4','hus110-3','虚拟机'),('金马扬名阳湖','已打开电源','172.20.91.100','','264.16 GB','164.29 GB','hus110-2','','虚拟机'),('ESB省平台复制','已打开电源','172.20.91.102','','316.13 GB','129.73 GB','hus110-1','v7000-1','虚拟机'),('掌上e+ _app_安卓','已打开电源','172.30.100.13','172.20.91.59','158.17 GB','145.73 GB','hus110-4','','虚拟机'),('ESB服务接口2','已打开电源','172.20.91.64','','316.15 GB','300.15 GB','hus110-1','g200-20-2','虚拟机'),('LIS','已打开电源','192.168.1.37','192.168.1.83','664.14 GB','462.35 GB','g200-20-1','','虚拟机'),('省平台直报数据对照','已打开电源','172.20.91.140','','532.13 GB','220.16 GB','g200-20-1','','虚拟机'),('data-etl','已打开电源','172.20.100.85','172.20.91.85','608.13 GB','600.13 GB','g200-20-2','','虚拟机'),('电子病历测试','已打开电源','172.30.100.155','172.20.91.155','108.13 GB','100.13 GB','g200-20-2','','虚拟机'),('云影像2','已打开电源','172.20.91.150','172.30.100.150','208.12 GB','200.12 GB','g200-20-2','','虚拟机'),('电子病历','已打开电源','172.30.100.160','172.20.91.160','108.12 GB','100.12 GB','v7000-1','','虚拟机'),('省平台直报前置机','已打开电源','172.20.91.145','','532.13 GB','500.13 GB','vm-1','g200-20-2','虚拟机'),('环境监控','已打开电源','172.20.91.183','','108.13 GB','108.13 GB','hus110-3','','虚拟机'),('云影像3','已打开电源','172.30.100.151','172.20.91.151','208.13 GB','200.13 GB','g200-20-2','','虚拟机'),('成本核算中间件1','已打开电源','','','308.09 GB','30.82 GB','hus110-2','','虚拟机'),('BI','已打开电源','172.20.6.61','','358.18 GB','280.18 GB','hus110-3','','虚拟机'),('HQMS','已打开电源','172.20.6.50','','1.11 TB','19.22 GB','hus110-1','','虚拟机'),('成本核算中间件2','已打开电源','','','308.09 GB','25.85 GB','hus110-3','','虚拟机'),('old_his','已打开电源','192.168.2.100','','611.44 GB','573.46 GB','hus110-3','','虚拟机'),('ywzx','已打开电源','192.168.1.173','','608.11 GB','608.00 GB','hus110-1','','虚拟机'),('传染病','已打开电源','172.20.6.114','','59.92 GB','59.92 GB','hus110-3','','虚拟机'),('成本核算','已打开电源','172.20.6.40','','1.50 TB','1.16 TB','hus110-1','','虚拟机'),('web-2','已打开电源','','','308.09 GB','24.27 GB','hus110-3','','虚拟机'),('web-3','已打开电源','172.20.6.82','','366.11 GB','181.02 GB','hus110-3','','虚拟机'),('web-1','已打开电源','172.20.6.80','','116.11 GB','104.67 GB','hus110-3','','虚拟机'),('Michael-EVE','已打开电源','172.20.6.199','','146.11 GB','27.94 GB','datastore1(9)','','虚拟机'),('武进科技局','已打开电源','172.20.6.99','','168.17 GB','119.24 GB','vm-1','','虚拟机'),('win2003x64_moban','已关闭电源','','','54.25 GB','10.25 KB','vm-1','','虚拟机'),('移动输液系统','已打开电源','172.20.6.76','','208.17 GB','107.36 GB','vm-4','','虚拟机'),('centos6.4 x64 mb','已关闭电源','','','104.26 GB','10.25 KB','vm-1','','模板'),('360KV','已关闭电源','','','108.26 GB','100.00 GB','vm-5','','虚拟机'),('绩效考核','已打开电源','172.20.6.74','','608.34 GB','147.20 GB','vm-2','','虚拟机'),('自助机1-易联众','已打开电源','172.20.6.120','','770.81 GB','181.89 GB','vm-4','','虚拟机'),('VMware vCenter Server Appliance','已打开电源','172.20.6.16','','133.14 GB','79.46 GB','datastore1','','虚拟机'),('静配系统','已打开电源','172.20.6.126','','616.18 GB','187.95 GB','vm-2','','虚拟机'),('win2008&sql2008','已关闭电源','','','104.34 GB','100.00 GB','vm-5','','模板'),('rl6.7-x64','已关闭电源','','','316.34 GB','10.40 GB','vm-1','','模板'),('centos6.4 x32 mb','已关闭电源','','','104.25 GB','10.24 KB','vm-1','','模板'),('vmware2008-client','已关闭电源','','','108.26 GB','51.47 GB','vm-5','','模板'),('仕轩pacs数据库','已打开电源','172.20.6.138','','316.13 GB','90.94 GB','vm-1','vm-5','虚拟机'),('ESB-1手麻','已打开电源','172.20.6.97','','108.13 GB','108.13 GB','husvm-1','','虚拟机'),('自助打印服务器','已打开电源','172.20.6.112','','555.17 GB','136.91 GB','vm-1','vm-4','虚拟机'),('预交金测试1','已打开电源','172.20.6.135','172.31.255.5','316.14 GB','242.94 GB','vm-1','vm-5','虚拟机'),('金马扬名PACS','已关闭电源','','','647.88 GB','100.10 GB','vm-5','','虚拟机'),('武进智慧医保','已打开电源','172.20.6.134','','508.13 GB','108.88 GB','vm-4','','虚拟机'),('绩效系统','已打开电源','172.20.6.74','','308.17 GB','308.17 GB','vm-2','','虚拟机'),('nova血糖分析','已打开电源','172.20.6.85','','416.18 GB','299.29 GB','vm-5','','虚拟机'),('360scan','已关闭电源','','','54.26 GB','50.00 GB','vm-1','vm-2','虚拟机'),('预交金平台数据库-易联众','已打开电源','172.20.6.122','','850.76 GB','349.83 GB','vm-4','','虚拟机'),('血透','已打开电源','172.20.6.127','','208.13 GB','140.60 GB','vm-2','','虚拟机'),('老BI数据库','已打开电源','172.20.6.60','','608.17 GB','251.73 GB','vm-1','','虚拟机'),('新病理系统','已打开电源','172.20.6.86','','618.59 GB','357.42 GB','vm-3','','虚拟机'),('rhel6.7_x64_oracle10g','已关闭电源','','','316.34 GB','23.27 GB','vm-1','vm-3','模板'),('vmware客户端','已打开电源','172.20.6.62','','108.15 GB','80.44 GB','datastore1(3)','','虚拟机'),('平安智慧医疗','已打开电源','172.20.6.113','','154.15 GB','50.15 GB','vm-5','','虚拟机'),('华益血糖分析','已打开电源','172.20.6.92','','408.17 GB','135.18 GB','vm-4','','虚拟机'),('质控系统','已打开电源','172.20.6.125','','254.11 GB','56.38 GB','vm-2','','虚拟机'),('科教科考试系统','已打开电源','172.20.6.94','','508.17 GB','114.36 GB','vm-2','','虚拟机'),('预交金平台应用服务-易联众','已打开电源','172.20.6.123','','612.17 GB','198.08 GB','husvm-1','','虚拟机'),('win2008_sp1_moban','已关闭电源','','','108.26 GB','100.00 GB','vm-1','','模板'),('win2008&sql2005','已关闭电源','','','108.34 GB','100.00 GB','vm-1','','模板'),('体检系统','已打开电源','172.20.6.78','','408.17 GB','408.17 GB','vm-3','','虚拟机'),('医保控费','已打开电源','172.20.6.75','','559.44 GB','183.90 GB','husvm-2','','虚拟机'),('2008moban','已关闭电源','','','44.26 GB','40.00 GB','vm-1','','模板'),('输血系统','已打开电源','172.20.6.77','','608.17 GB','519.71 GB','vm-3','','虚拟机'),('区域信息平台','已打开电源','172.20.6.98','','1021.76 GB','734.37 GB','vm-1','','虚拟机'),('叫号显示','已打开电源','172.20.6.124','','158.13 GB','158.13 GB','vm-5','','虚拟机'),('预交金测试3','已打开电源','172.20.6.137','','316.14 GB','29.95 GB','vm-1','vm-4','虚拟机'),('ETL-1','已打开电源','172.20.6.93','','108.13 GB','108.13 GB','vm-4','','虚拟机'),('预交金测试2','已打开电源','172.20.6.136','','316.14 GB','18.48 GB','vm-1','vm-2','虚拟机'),('移动医疗','已打开电源','172.20.6.79','','216.16 GB','214.79 GB','vm-2','','虚拟机'),('自助机2-易联众','已打开电源','172.20.6.121','','826.54 GB','298.37 GB','vm-2','','虚拟机'),('win2003sp2clean','已关闭电源','','','51.21 GB','50.00 GB','vm-2','','模板');
/*!40000 ALTER TABLE `V_Machine_Info` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2018-07-22 11:17:36