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;
 1 -- MySQL dump 10.13  Distrib 5.6.38, for Linux (x86_64)
 2 --
 3 -- Host: localhost    Database: vmware
 4 -- ------------------------------------------------------
 5 -- Server version    5.6.38
 6 
 7 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
 8 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
 9 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
10 /*!40101 SET NAMES utf8 */;
11 /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
12 /*!40103 SET TIME_ZONE='+00:00' */;
13 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
14 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
15 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
16 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
17 
18 --
19 -- Table structure for table `V_Machine_Info`
20 --
21 
22 DROP TABLE IF EXISTS `V_Machine_Info`;
23 /*!40101 SET @saved_cs_client     = @@character_set_client */;
24 /*!40101 SET character_set_client = utf8 */;
25 CREATE TABLE `V_Machine_Info` (
26   `NAME` varchar(255) DEFAULT NULL,
27   `STATUS` varchar(255) DEFAULT NULL,
28   `IP1` varchar(255) DEFAULT NULL,
29   `IP2` varchar(255) DEFAULT NULL,
30   `PREPARED_SPACE` varchar(255) DEFAULT NULL,
31   `USE_SPACE` varchar(255) DEFAULT NULL,
32   `STORE1` varchar(255) DEFAULT NULL,
33   `STORE2` varchar(255) DEFAULT NULL,
34   `TYPE_MODEL` varchar(255) DEFAULT NULL
35 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
36 /*!40101 SET character_set_client = @saved_cs_client */;
37 
38 --
39 -- Dumping data for table `V_Machine_Info`
40 --
41 
42 LOCK TABLES `V_Machine_Info` WRITE;
43 /*!40000 ALTER TABLE `V_Machine_Info` DISABLE KEYS */;
44 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','','模板');
45 /*!40000 ALTER TABLE `V_Machine_Info` ENABLE KEYS */;
46 UNLOCK TABLES;
47 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
48 
49 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
50 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
51 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
52 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
53 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
54 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
55 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
56 
57 -- Dump completed on 2018-07-22 11:17:36
View Code
posted on 2018-07-22 11:35 90500042陈阅读(2006) 评论(0) 编辑收藏举报