Python中将xlsx转化为csv中的坑与方法

最新推荐文章于 2024-09-05 08:31:05 发布

原创最新推荐文章于 2024-09-05 08:31:05 发布 · 5.9k 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#xlsx csv

Python 专栏收录该内容

54 篇文章

订阅专栏

本文介绍两种将Excel文件转换为CSV格式的方法，包括使用xlrd和pandas库的代码示例。方法二利用pandas成功保留了原始时间格式，实现了无损转换。

这里提供两种转换方法：
数据类型：a.xlsx

3.071,2017-03-25 09:58:59,S;I:150921;T:170217230212;X:0;F:1;D:4454;K:2;E;
3.146,2017-03-25 09:59:04,S;I:150921;T:170217230217;X:0;F:1;D:4541;K:2;E;
3.086,2017-03-25 09:59:09,S;I:150921;T:170217230222;X:0;F:1;D:4471;K:2;E;
3.103,2017-03-25 09:59:14,S;I:150921;T:170217230227;X:0;F:1;D:4491;K:2;E;
3.122,2017-03-25 09:59:19,S;I:150921;T:170217230232;X:0;F:1;D:4513;K:2;E;

方法一代码：

import xlrd
import csv
import codecs
def xlsx2csv():
    workbook=xlrd.open_workbook("/home/work/datasource/esa/1.xlsx")
    table = workbook.sheet_by_index(0)
    with codecs.open('/home/work/datasource/esa/1.csv','w',encoding='utf-8') as f:
        write = csv.writer(f)
        for row_num in range(table.nrows):
            row_value =table.row_values(row_num)
            write.writerow(row_value)

if __name__ == '__main__':

可以转化，可是是一个残次品。看结果：

1.1,42819.4163425926,S;I:15711;T:000014033838;X:0;F:1;D:12815;K:2;E;
1.063,42819.4159490741,S;I:15711;T:000014033843;X:0;F:1;D:12485;K:2;E;
1.013,42819.4160069444,S;I:15711;T:000014033848;X:0;F:1;D:12023;K:2;E;
1.056,42819.4160648148,S;I:15711;T:000014033853;X:0;F:1;D:12414;K:2;E;
1.092,42819.4161226852,S;I:15711;T:000014033858;X:0;F:1;D:12745;K:2;E;
1.078,42819.4161805556,S;I:15711;T:000014033903;X:0;F:1;D:12619;K:2;E;
1.084,42819.4162384259,S;I:15711;T:000014033908;X:0;F:1;D:12672;K:2;E;
1.064,42819.4162962963,S;I:15711;T:000014033913;X:0;F:1;D:12492;K:2;E;
1.344,42819.4163541667,S;I:15711;T:000014033918;X:0;F:1;D:15037;K:2;E;

时间转化的不对，我还是希望是原来的格式。
方法二：

import pandas as pd


def xlsx_to_csv_pd():
    data_xls = pd.read_excel('/home/work/datasource/esa/1.xlsx', index_col=0)
    data_xls.to_csv('/home/work/datasource/esa/1.csv', encoding='utf-8')


if __name__ == '__main__':
    xlsx_to_csv_pd()
    ```
    结果：
    ```
    1.1,2017-03-25 09:59:32,S;I:15711;T:000014033838;X:0;F:1;D:12815;K:2;E;
1.063,2017-03-25 09:58:58,S;I:15711;T:000014033843;X:0;F:1;D:12485;K:2;E;
1.013,2017-03-25 09:59:03,S;I:15711;T:000014033848;X:0;F:1;D:12023;K:2;E;
1.056,2017-03-25 09:59:08,S;I:15711;T:000014033853;X:0;F:1;D:12414;K:2;E;
1.092,2017-03-25 09:59:13,S;I:15711;T:000014033858;X:0;F:1;D:12745;K:2;E;
1.078,2017-03-25 09:59:18,S;I:15711;T:000014033903;X:0;F:1;D:12619;K:2;E;
1.084,2017-03-25 09:59:23,S;I:15711;T:000014033908;X:0;F:1;D:12672;K:2;E;
1.064,2017-03-25 09:59:28,S;I:15711;T:000014033913;X:0;F:1;D:12492;K:2;E;
1.344,2017-03-25 09:59:33,S;I:15711;T:000014033918;X:0;F:1;D:15037;K:2;E;

完全正确。这种方法不会对数据作任何改变。