超大csv文件转xlsx文件¶

!ls ./tmp -al

total 4104
drwx------+ 1 yangz yangz       0 Mar  2 18:09 .
drwx------+ 1 yangz yangz       0 Mar  2 18:11 ..
drwx------+ 1 yangz yangz       0 Mar  2 17:39 .ipynb_checkpoints
-rwx------+ 1 yangz yangz 4185300 Mar  2 16:35 tmp.csv

import pandas as pd

%%timeit
df = pd.read_csv('./tmp/tmp.csv', dtype=str, encoding='utf-8')
with pd.ExcelWriter("./tmp/xlsxwriter.xlsx", engine="xlsxwriter") as writer:
    df.to_excel(writer)

6.36 s ± 338 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%%timeit
df = pd.read_csv('./tmp/tmp.csv', dtype=str, encoding='utf-8')
with pd.ExcelWriter("./tmp/openpyxl.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer)

10.2 s ± 162 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

import zipfile

%%timeit
df = pd.read_csv('./tmp/tmp.csv', dtype=str, encoding='utf-8')
with zipfile.ZipFile("./tmp/tmp.zip", "w") as zf:
    with zf.open("zipfile.xlsx", "w") as buffer:
        with pd.ExcelWriter(buffer, engine="xlsxwriter") as writer:
            df.to_excel(writer)

6.42 s ± 148 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

with open('./tmp/gbk.csv', 'w+', encoding='gbk') as f:
    with open('./tmp/tmp.csv', 'r', encoding='utf8') as raw:
        f.write(raw.read())

import win32com.client as wc

%%time

excel = wc.gencache.EnsureDispatch("Excel.Application")
csv = excel.Workbooks.Open('c:/users/yangz/Desktop/tmp/gbk.csv')
csv.SaveAs(Filename='tmp.xlsx', FileFormat=51)
excel.Quit()

CPU times: total: 46.9 ms
Wall time: 3.65 s

import os

%%timeit
os.system("csv2xlsx.exe -o ./tmp/csv2xlsx.xlsx ./tmp/tmp.csv ")

2.39 s ± 90.4 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)