最新消息:20190717 VPS服务器:Vultr新加坡,WordPress主题:大前端D8,统一介绍入口:关于

【记录】使用Python读取/导出(写入)CSV文件

Python crifan 11555浏览 0评论

想要用python处理csv文件。

去查了下,python中本身就自带csv模块。

然后参考在线手册:

http://docs.python.org/2/library/csv.html

去试试。

【用python生成csv】

1. 按照手册的例子,试了试:

import csv
with open('eggs.csv', 'wb') as csvfile:
    spamwriter = csv.writer(csvfile, delimiter=' ',
                            quotechar='|', quoting=csv.QUOTE_MINIMAL)
    spamwriter.writerow(['Spam'] * 5 + ['Baked Beans'])
    spamwriter.writerow(['Spam', 'Lovely Spam', 'Wonderful Spam'])

然后生成的文件,原始数据为:

Spam Spam Spam Spam Spam |Baked Beans|
Spam |Lovely Spam| |Wonderful Spam|

然后用excel打开结果为:

csv effect orig

很明显,不是想要的结果,因为只是一列,实际应该是6列才对。

2. 然后折腾了一下之后,参考手册的说明:

csv.writer(csvfile[, dialect=’excel’][, fmtparam])

Return a writer object responsible for converting the user’s data into delimited strings on the given file-like object. csvfile can be any object with a write() method. If csvfile is a file object, it must be opened with the ‘b’ flag on platforms where that makes a difference. An optional dialect parameter can be given which is used to define a set of parameters specific to a particular CSV dialect. It may be an instance of a subclass of the Dialect class or one of the strings returned by the list_dialects() function. The other optional fmtparam keyword arguments can be given to override individual formatting parameters in the current dialect. For full details about the dialect and formatting parameters, see section Dialects and Formatting Parameters. To make it as easy as possible to interface with modules which implement the DB API, the value None is written as the empty string. While this isn’t a reversible transformation, it makes it easier to dump SQL NULL data values to CSV files without preprocessing the data returned from a cursor.fetch* call. All other non-string data are stringified with str() before being written.

去改为:

import csv
with open('eggs.csv', 'wb') as csvfile:
    #spamwriter = csv.writer(csvfile, delimiter=' ',quotechar='|', quoting=csv.QUOTE_MINIMAL)
    spamwriter = csv.writer(csvfile, dialect='excel')
    spamwriter.writerow(['Spam'] * 5 + ['Baked Beans'])
    spamwriter.writerow(['Spam', 'Lovely Spam', 'Wonderful Spam'])

然后就可以显示正常了:

show ok in excel

去看了下源码为:

Spam,Spam,Spam,Spam,Spam,Baked Beans
Spam,Lovely Spam,Wonderful Spam

 

【总结】

使用python的csv生成excel所兼容的csv文件的话,主要就是创建writer时的参数时要有dialect=’excel’,就可以了。

 

【用Python读取从excel导出的csv文件】

再去尝试用python处理,从excel 2010导出的一个csv文件:

excel 2010 ms-dos csv test

参考手册的代码,使用如下代码:

import csv
with open('excel_2010_ms-dos.csv', 'rb') as csvfile:
    #spamreader = csv.reader(csvfile, delimiter=' ', quotechar='|')
    spamreader = csv.reader(csvfile, dialect='excel')
    for row in spamreader:
        print ', '.join(row)

去测试,然后是可以正常打印出结果的:

D:\tmp\tmp_dev_root\python\test_csv>test_csv.py
a1, b1, c1, d1
a2, , c2,
, b3, ,

【总结】

只要指定了对应的dialect=’excel’,也是可以很方便的使用python的csv处理excel所导出的ms-dos的csv文件的。

转载请注明:在路上 » 【记录】使用Python读取/导出(写入)CSV文件

发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

网友最新评论 (3)

  1. csv可以当成txt来操作的
    Joe7年前 (2012-10-30)回复
    • 恩。之前偶不知道,现在才知道的。
      crifan7年前 (2012-10-30)回复
    • 请问我有个csv文件,里面有数据,我想导入到python中进行训练集和测试集的提取,请问怎么做? csv中的数据 我怎么都导入不到python 中
      殇诚6年前 (2014-03-20)回复
59 queries in 0.100 seconds, using 18.35MB memory