数据清洗-- 使用Python脚本处理excel单元格换行符

原文地址http://blog.csdn.net/collaboom/article/details/52227950

在下在做数据分析时，原始数据是excel格式的，导入Hive表时将其转化为txt格式。 
excel文件转txt格式的步骤如下： 
1.打开另存为 
2.选择txt格式保存 
3.打开转成的txt将编码修改为UTF-8

在往hive表中导入数据时常常以换行符‘\n’来分割条数据。然而有时因为数据格式不够标准，即excel单元格中存在着换行符情况。例子如下：

在这个表格中，B3，B4单元格都存在这换行符。如果将其导入到hive中，查询得到的结果将是这样：

姓名  与宝宝的关系

王宝强 宝宝本人

马蓉  "曾经是宝宝的宝宝


现在是宝宝的前妻"

宋喆  "曾经是宝宝的经纪人

现在是宝宝曾经的宝宝的宝宝"
 
 
1
2
3
4
5
6
7
8
9
10
11
12
 
 
1
2
3
4
5
6
7
8
9
10
11
12

与我们预先想得到的不一样

姓名  与宝宝的关系
王宝强 宝宝本人
马蓉  曾经是宝宝的宝宝 现在是宝宝的前妻
宋喆  曾经是宝宝的经纪人 现在是宝宝曾经的宝宝的宝宝
 
 
1
2
3
4
 
 
1
2
3
4

因此尝试使用Python编写脚本来处理问题。开始尝试使用正则表达式进行处理。后面找到了更简便的办法，使用xlrd包对excel文件进行处理。使用pip install xlrd可以安装该包。 
下面的代码读取每一个单元格并将其中内容转化为字符串格式（默认为float格式，因此原本的整数可以转化为int去除小数点在转化为字符串），去除字符串中的换行符，将数据直接存储到txt文件中，编码格式为utf-8。

废话不说上代码：

# coding: utf-8
# author: turbob00st version:1.0 
''' 
usage：python version 3  python脚本与excel文件放在同一路径，cmd输入：py -3 excel2txt.py
清除excel表格中的单元格换行符，并转化为txt格式文件，可以直接导入hive表
'''
import xlrd     #导入处理excel文件的包，可以使用pip安装
file = input("输入需要处理的文件名(包含.xls,.xlsx)：")
filename = file.split('xls')[0] + 'txt' #定义输出文件名
xls2txt = open(filename,'w',encoding ='utf-8') #创建写入的文件
data = xlrd.open_workbook(file) #打开excel表格
table = data.sheets()[0]    #读取第一个sheet
rows = table.nrows  #excel文件的行数
cols = table.ncols  #ecel文件的列数
for rownum in range(0,rows):    #读取行
    for colnum in range(0,cols):    #读取列
        celldata = table.cell(rownum,colnum).value  #读取单元格数据，数据格式为float，下面判断将整数数据转化为int
        if type(celldata) == float:
            if int(celldata) == celldata:
                celldata = int(celldata)
        celldata = str(celldata)    #将数据转化为字符串，再对其中的换行符进行处理
        celldata = celldata.replace('\n',' ')   #使用python中字符串函数替换换行符为空格
        xls2txt.write(celldata +'\t') #单行中的数据用tab分隔符分离
    xls2txt.write('\n') #每行之间使用换行符
print('清洗完毕')
xls2txt.close() #重要，防止内存溢出

 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

总结：该代码可以实现将excel表格标准化并转化为UTF-8的txt文件，方便导入数据库。