使用超过一列来排序excel数据

I am trying to sort excel data using 2 or 3 columns (so a nested sort if you will). Currently what occurs is I get a text file (some times more than 1) with log entries, I then have to convert the entries into a managed Excel spread sheet.

我正在尝试使用2或3列来对excel数据进行排序(如果您愿意的话，可以使用嵌套排序)。目前发生的情况是，我得到一个带有日志条目的文本文件(有时超过1次)，然后我必须将这些条目转换为托管的Excel扩展表。

I have managed to do this, but now my manager wants me to also sort it by the 'date' and 'time' columns respectively.

我已经做到了这一点，但现在我的经理想让我也按“日期”和“时间”列分别进行排序。

So to break it down I am just trying to figure out how I can do this.

为了把它分解，我只是想知道怎么做。

Requirements:

要求:

Sort a csv or excel spreadsheet via multiple columns and write them to a document (of the same type preferably).

通过多个列对csv或excel电子表格进行排序，并将它们写入文档(最好是同一类型)。

CODE:

代码:

Currently the code I have is:

目前我的代码是:

import csv, os
import operator

testcsv= open('test.csv', 'r')
csvSort = csv.reader(testcsv, delimiter=",")
sort = sorted(csvSort,key=operator.itemgetter(0,1))

for eachline in sort:
    print eachline

This works fine for "printing" and checking. But I can't get this to write the data back into an excel spreadsheet.

这适用于“打印”和检查。但是我不能用这个把数据写回excel电子表格。

If I did that it goes all into 1 row instead of multiple rows. When I do the write lines to files this is the code I use:

如果我这样做，它就会变成一行而不是多行。当我写行到文件这是我使用的代码:

dest = "C:/Test2.csv"
for eachline in sort:
    if not os.path.exists(dest):
        fileToCreate = open(dest, 'w')
        fileToCreate.writelines(eachline)
    else:
        fileToCreate = open(dest, 'a+')
        fileToCreate.writelines(eachline)
    fileToCreate.close()

Perhaps if someone has a better option for me to use or something to add that would help me, I'd be grateful.

也许如果有人有更好的选择让我使用或者添加一些可以帮助我的东西，我会很感激。

Test input data file which is being used

正在使用的测试输入数据文件。

05/02/2015,20:22:24,201534
07/02/2015,20:23:24,201534
05/02/2015,20:23:24,201534
06/02/2015,20:23:24,201534
05/02/2015,20:24:24,201534
13/02/2015,20:24:24,201534
13/02/2015,20:24:24,201534
12/02/2015,20:21:24,201534
01/02/2015,20:21:24,201534
21/02/2015,20:21:24,201534
21/02/2015,20:21:24,201534
13/02/2015,20:21:24,201534
13/02/2015,20:21:24,201534
13/02/2015,20:21:24,201534
13/02/2015,20:21:24,201534
13/02/2015,20:21:24,201534
13/02/2015,20:21:24,201534
13/02/2015,20:21:24,201534
13/02/2015,20:21:24,201534
05/02/2015,20:21:24,201534
05/02/2015,20:21:24,201534
05/02/2015,20:21:24,201534
05/02/2015,20:18:24,201534
05/02/2015,20:18:24,201534
05/02/2015,20:18:24,201534
05/02/2015,20:22:24,201534

1 个解决方案

#1

You can reuse csv.writer to write back the entire sorted list of lists. It has a function called writerows() for that. Example -

您可以重用csv。写回全部排序的列表。它有一个名为writerows()的函数。的例子,

dest = "C:/Test2.csv"
with open(dest,'w') as f:
    writer = csv.writer(f)
    writer.writerows(sort)

Please note you do not need to specify delimiter as , , default delimiter is comma.

请注意，您不需要将分隔符指定为，默认的分隔符是逗号。

Currently when you use writelines with eachline list, it writes each element of the list simultaneously, but it would not add the required newline characters. So all of the data would appear to be in a single line. You should be using csv.writer for writing back csv/excel files.

目前，当您使用writelines with eachline列表时，它会同时写入列表的每个元素，但它不会添加所需的换行字符。所有的数据看起来都在一条直线上。你应该使用csv。写回csv/excel文件。

#1