精华内容
下载资源
问答
  • Excel数据导入MySQL,已经做过好几次了,但每次都会碰到各种问题:invalid utf8 character string, data too long, ...,浪费了不少时间 为了提高效率,是时候指定一个数据导入的SOP了: 1.准备.txt文件 ...

    从Excel数据表导入MySQL,已经做过好几次了,但每次都会碰到各种问题:invalid utf8 character string, data too long, ...,浪费了不少时间

    为了提高效率,是时候指定一个数据导入的SOP了:


    1.准备.txt文件

        1.1 将要导入的数据(不含表头)从工作表复制、粘贴到一个新建的Excel数据表中(避免污染源数据)

        1.2 粘贴时注意:使用右键paste as value选项,过滤掉源数据表中的多余格式(如,字体颜色、粗体等)

        1.3 将新建的Excel文档(只含一张数据表)另存为unicode文本(试过.csv格式,不好用)

        1.4 关闭另存为后的Excel文档,在资源管理器中用记事本打开该文档,点击“另存为“按钮,发现编码格式为“Unicode”,将此项更改为“UTF-8”,覆盖源文件。

    (这一步很关键,如果缺少,则在导入.txt文件时Mysql会报出让人费解的错误:invalid utf8 character string ''


    2. 建表、导入.txt文件

        2.1 根据Excel数据表的结构,设计Mysql表结构(感觉Text格式比varchar好用,不用关心各字段的长度问题)

        2.2 创建表:Create table term (type text, subtype text, en text, cn text, chd text) character set=utf8;

        2.3 使用Load Data InFile 'D:/sample.txt' Into Table terms lines terminated by '\r\n';语句导入.txt,成功!


    开始享受数据库操作吧!


    彩蛋:

    如要将查询结果导出到.txt文件,使用语句:select  *  into outfile 'D:\\term.txt' from term limit 10;

    转载于:https://www.cnblogs.com/zh1989/p/5856171.html

    展开全文
  • 导入Excel表格中的数据到mysql数据库是一种常见需求。导入的方法有很多。今天介绍一种用python导入excel数据到mysql的方法。它的好处是执行效率高,导入数据量大。 实施步聚 1,通过python 将excel文件转换为sql插入...

    基本需求

    导入Excel表格中的数据到mysql数据库是一种常见需求。导入的方法有很多。今天介绍一种用python导入excel数据到mysql的方法。它的好处是执行效率高,导入数据量大。

    实施步聚

    1,通过python 将excel文件转换为sql插入语句组成的文本文件。为了实现这个功能用到了一个读取excel数据的python模块xlrd。
    2.命令行上执行 mysql命令,运行第一步生成的sql脚本,将数据导入mysql数据库。

    转换excel文件的python代码

    # -*- coding:utf-8 -*- 
    from xlrd import open_workbook  
    import sys
    import getopt
    import time
    reload(sys)
    sys.setdefaultencoding( "utf-8" )  
    
    #解析命令行参数,得到excel文件名和数据库表名
    try:
    	opts, args = getopt.getopt(sys.argv[1:],'f:s:d:',['src=','sheetIdx=','table='])
    except getopt.GetoptError:
    	print('error:','options invalid')
    	sys.exit()
    
    for k, v in opts:
    	if k in ("-f", "--src"):
    		srcFile = v
    	elif k in ("-s", "--sheetIdx"):
    		sheetIdx = int(v)
    	elif k in ("-d", "--table"):
    		tableName = v
    
    startTime = time.time()
    
    #创建表的sql
    outsql =open(srcFile+'.sql','w')
    outsql.write('DROP TABLE IF EXISTS '+tableName+';' + '\n')
    outsql.write('CREATE TABLE IF NOT EXISTS '+tableName+'(`_tmp_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,'+ '\n')
    
    insertHeader = "insert into "+tableName+"("
    
    #逐行逐列分析excel,生成insert语句并写入sql脚本文件
    wb = open_workbook(srcFile)
    sheetNum=0
    rowCount="0"
    for s in wb.sheets():  
        if sheetNum != sheetIdx:
        	continue
        print ('Sheet:',s.name ) 
        for row in range(s.nrows):
    		if row < 1:
    			fieldList=""
    			fieldTypeList=""
    			for col in range(s.ncols):
    				cellValue = "%s"%s.cell(row,col).value
    				fieldTypeList+= "`"+cellValue + '` varchar(255),'
    				fieldList+= "`"+cellValue + '` ,'
    			outsql.write(fieldTypeList + '\n')
    			outsql.write('PRIMARY KEY (`_tmp_id`)) AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;'+ '\n')
    			#关闭自动提交可以在执行大量语句时显著提高性能
    			outsql.write('set autocommit =0;'+ '\n')
    
    			insertHeader += fieldList[:-1]+") values("
    		if row >= 1:
    			valueList=""
    			for col in range(s.ncols):
    				if type(s.cell(row,col).value) == type(1.5):
    					cellValue = "%d"%s.cell(row,col).value
    				else:
    					cellValue = "%s"%s.cell(row,col).value
    				valueList+="'" + cellValue + "',"
    			valueList = valueList[:-1]
    			outsql.write(insertHeader + valueList + ');\n')
    			rowCount = "%s"%row
        sheetNum+=1
        #break
    outsql.write('commit;'+ '\n')
    outsql.write('set autocommit =1;'+ '\n')
    outsql.close()
    
    outsql =open(srcFile+'.log','w')
    #记录待导入的总行数
    outsql.write(rowCount)
    outsql.close()
    
    endTime = time.time()
    print('used time/seconds: ',endTime-startTime)
    
    

    执行sql脚本的命令行命令

    $cmdLine = 'mysql -h '.DATABASE_SERVER.' -u'.DATABASE_USER.' -p'.DATABASE_PWD.' '.DATABASE_NAME .' < '.$sqlFileName;
    

    以上命令行是用php拼接而成的,其实用任意的后台语言,比如java,perl,python都可以实现命令行的拼接功能。

    其中 DATABASE_SERVER 是指mysql数据库服务器的名字,一般用localhost或127.0.0.1就可以了;DATABASE_USER 指mysql数据库的用户名;DATABASE_PWD指用户密码;DATABASE_NAME指数据库名。$sqlFileName 代表第一个步骤中生成的sql脚本文件的名字。

    经过实际测试,用这种方法导入包含有几万条数据的excel表格都很顺利。但如果用一些网上常见的组件往往在导入几百,几千条数据时还行,当数据量上万时就有可能会发生崩溃。所以这个方法虽然简单,但实际效果很不错。

    展开全文
  • 2.excel导入到mysql: 2.1.将选中的数据块儿(不包含表头)拷贝一个TXT文本文件中,假如存“D:\data.txt”这个位置里。或者直接另存为CSV文件。 其实csv文档就是txt(即你可以使用自己的txt然后换个后缀名就行),...

    1.mysql导出到excel

    1.1.SELECT * INTO OUTFILE ‘/test.xls’ FROM table1;

    2.excel导入到mysql:

    2.1.将选中的数据块儿(不包含表头)拷贝到一个TXT文本文件中,假如存到“D:\data.txt”这个位置里。或者直接另存为CSV文件。

    其实csv文档就是txt(即你可以使用自己的txt然后换个后缀名就行),只不过把各个字段之间用”,”分隔开了而已。既然它是txt文档,那么就涉及到了文件编码的问题!默认保存的文件编码是ANSI,如果你的数据库(数据表)使用UTF-8编码,那么一定要将这个csv文件另存为UTF-8格式!编码方式在editplus中的document->file encoding–>convert encoding中可以任意修改

    2.2.根据要导入的数据建立MySql数据库和表,结构和excel结构保持一致.然后使用命令:load data local infile ‘D:\data.txt’ into table 表名 fields terminated by ‘\t’;
    注意:**D:\data.txt为要导入的文件的路径,Windows命令窗口下根路径需要用双斜杠,否则报错**Errcode: 2 - No such file or directory

    如果编码方式有问题会报错:ERROR 1300 (HY000): Invalid utf8 character string: ”

    2.3.如果需要可以将最后一列的回车符号去掉: UPDATE tablename SET lastcolumnname= REPLACE(lastcolumnname,’\r’,”);

    展开全文
  • SELEC * FROM tablename into outfile out_directory 如果数据是UTF-8编码,可能生成Excel会乱码,用文本编辑器打开,以ANSI另存即可。
    SELEC * FROM tablename into outfile out_directory

    如果数据是UTF-8编码,可能生成Excel会乱码,用文本编辑器打开,以ANSI另存即可。
    展开全文
  • 您可能感兴趣的文章:MySQL命令行导入SQL脚本时出现中文乱码的解决方法Excel导入Sqlserver数据库脚本MySQL导入sql脚本错误:2006 解决方法PHP处理SQL脚本文件导入到MySQL的代码实例C#创建数据库及导入sq
  • MySQL 导入EXCEL数据

    千次阅读 2015-09-12 21:46:11
    最近在把EXCEL数据导入MySQL数据库中,对此我做个笔记,方便自己后续查找方便。 一. 通过命令行导入: 1. 先将excel文档另存成CSV(逗号分隔): 2. 用记事本打开csv文件,另存为txt文件格式,编码格式为UTF-8...
  • MySQL导入EXCEL表格数据

    2015-12-04 10:31:51
    MySQL导入EXCEL表格数据: 1、先创建数据表,要导入的EXCEL列及字段类型应与数据表对应; 2、将EXCEL另存为.csv格式文件; 3、使用命令行导入: Load Data InFile 'E:\\Me\\2013.csv' Into Table `t_sampledata`...
  • mysql如何导入excel数据

    2015-09-28 16:08:51
    1、新建excel表格,准备录入自己的数据。 2、录入要导入数据,注意要与数据库的类型一致。 ...
  • mysql 导入 excel 数据

    2015-04-05 11:13:00
    客户准备了一些数据存放在 excel 中, 让我们导入到 mysql 中。先上来我自己把数据拷贝了 txt 文件中, 自己解析 txt 文件,用 JDBC 循环插入数据库中。 后来发现有更简单的方法: 1 先把数据拷贝 txt 文件...
  • Excel数据导入MySql

    2014-03-26 14:58:05
    具体没有实施,利用navicat 工具很容易将xls文件里面的数据导入到了数据库里面,命令行的,有机会实践一下,标出易错点 将Excel数据导入MySql  1.将选中的数据快儿拷贝一个TXT文本文件中(记得把后面...
  • mysql中快速导入excel数据的方法

    万次阅读 2017-05-13 07:52:21
    mysql中快速导入excel数据的方法 使用insert语句和命令行source执行已经编写好的sql语句快速导入excel数据
  • MySQL命令行导入导出

    2013-01-29 11:16:00
    导出数据库中所有的数据 mysqldump -uUserName -pPassword DatabaseName>FileName.sql 导出数据库中所有表的结构 mysqldump -uUserName -pPassword -d DatabaseName>FileName.sql 导出数据库中...
  • 从phpMyAdmin批量导入Excel内容到MySQL

    千次阅读 2016-01-20 11:16:28
    一、假设你的表格有A、B、C三列数据,希望导入到你的数据库中表格table,对应的字段分别是col1、col2、col3二、在你的表格中增加一列,利用excel的公式自动生成sql语句,具体方法如下:1、增加一列(假设是D列)2、...
  • 首先,在MySQL命令行输入: SHOW GLOBAL VARIABLES LIKE ‘local_infile’; SQLyog或者其他的可视化软件都可以 结果如下图: 如果local_infile的value是OFF,则使用以下命令将vaule改为ON SET GLOBAL local_infile...
  •  一.有个软件PHP Excel Parser Pro v4.2可以,你可以在这里下载: ...1.用EXCEL将你的字段按MYSQL中相关表的字段顺序列排列好,注意特殊字段的表示方式(例如:时间字段必须用2007-10-10形式表示
  • mysql导入excel数据

    2019-08-19 09:26:50
    选择源文件需要导入的sheet,只有sheet1有数据,所以选了sheet1 4、选择第一个的数据行,默认是1,实际上大多数第二行开始才是数据行 5、选择需要导入的列,这里我全选,下一步,点击开始,关闭。 6、刷新,点击表...
  • Python导入txt数据到mysql的方法本文实例讲述了Python导入txt数据到mysql的方法。分享给大家供大家参考。具体分析如下:从TXT文本转换数据到MYSQL数据库,接触一段时间python了 第一次写东西 用的是Python2.7#!/usr/...
  • 一:如何利用phpMyAdmin批量导入Excel内容到MySQL。我今天用的版本是phpMyAdmin 3.2.4,MySQL的版本是5.1.41。 1、第一步我们得到了一个excel表,里面有很多需要我们导入的数据。 2、删除第1行"准考证号""XXX...
  • Navicat for mysql 如何导入excel文件数据 1、先连接到mysql服务器 输入连接名、主机、端口、用户名和密码。连接名可以自定义,显示在左侧导航栏,主机输入服务器的ip地址,本地服务器是localhost或127.0.0.1,...
  • Mysql导入导出sql,txt,excel(都在linux或windows命令行操作) 首先我们通过命令行进入到mysql安装目录的bin目录下,比如我输入的命令行为: d: cd D:/wamp/bin/mysql/mysql5.6.17/bin   一、导入导出sql ...
  • mysql导入Excel表格数据

    千次阅读 2019-07-15 11:08:59
    mysql导入Excel表格数据 进入mysql数据库 在命令提示符面板输入如下命令,按回车键,输入自己的密码,再按回车键即可进入mysql数据库。 mysql -root -p 2. 创建数据库 */显示当前mysql中所有的数据库/* show ...
  • MySQL中,数据导入的方式有两种方式 通过第三方客户端导入(workbench) 通过mysql client 方式导入 通过mysql clinet的导入方式,相比第三方客户端导入的方式,效率会高点,但是类似workbench这种的,因为图形UI...
  • excel文档导入到mysql
  • 文件中的数据通过命令行导入到 mysql 数据库 1. 首先把 excel 中的所有不带首行英文名的数据,复制 .txt 文件(在 eclipse 中保存为 utf8  或者 gbk  编码格式);  注:不能有任何多余的制表符或者换行...
  • excel数据导入远程mysql数据库

    千次阅读 2018-01-24 14:42:44
    mysql数据库在公司内网服务器,而excel数据表在本地,需要堡垒机跳转才能访问,无法直连,因此在本地任何客户端导入数据表的方法都不好使。 数据库只开放了一个端口,无法将数据表传数据库服务器上,再登入数据库...
  • 上传excel文件,自动识别文件类型,文件名。 解析文件,对数据进行提取并处理。 导入数据库。 框架背景:spring boot+mybatis 二.工作流程 使用依赖包: <!-- ...
  • mysql命令行下执行以下sql语句: mysql>select * from xi_table into outfile ’/tmp/test.xls’; 一句话搞定。 linux系统尽量导出在/tmp目录下,不会涉及读写权限问题! 导出为txt文件: select * from xi_...
  • java操作excel数据导入mysql数据库中

    千次阅读 2019-05-26 19:25:15
    java操作excel数据导入mysql数据库中 前期准备 架包下载地址: https://mvnrepository.com/artifact/net.sourceforge.jexcelapi/jxl/2.6.12 mysql下载地址: ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 2,213
精华内容 885
关键字:

命令行导入excel数据到mysql

mysql 订阅