首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >在Python中将多个CSV文件合并到电子表格的单独选项卡中

在Python中将多个CSV文件合并到电子表格的单独选项卡中
EN

Stack Overflow用户
提问于 2018-08-22 17:41:44
回答 4查看 16.3K关注 0票数 8

我有一个在一个目录中生成多个CSV文件的代码。我想在excel中生成一个报告,它将由CSV文件作为单独的选项卡组成。我已经使用了下面的代码来做同样的事情:

代码语言:javascript
复制
import pandas as pd
import os
import csv
import glob    
path = "/MyScripts"
all_files = glob.glob(os.path.join(path, "*.csv"))
df_from_each_file = (pd.read_csv(f) for f in all_files)
df_from_each_file.to_excel(writer, sheet_name='ReturnData.csv')
writer.save()

但它给出了下面的错误:AttributeError:'generator‘对象没有’to_excel‘属性不确定我哪里出错了。我需要导入任何特定的库来解决这个问题吗?

Python版本为2.7

EN

回答 4

Stack Overflow用户

回答已采纳

发布于 2018-08-22 17:56:12

这里有两个问题:

  1. 生成器表达式允许您懒惰地迭代dataframe对象。不能将生成器表达式导出到Excel文件。
  2. sheet_name参数是一个常量。若要导出到多个工作表,您需要为每个工作表指定不同的名称。

为此,您可以使用一个简单的for循环:

代码语言:javascript
复制
writer = pd.ExcelWriter('out.xlsx', engine='xlsxwriter')
df_from_each_file = (pd.read_csv(f) for f in all_files)

for idx, df in enumerate(df_from_each_file):
    df.to_excel(writer, sheet_name='data{0}.csv'.format(idx))

writer.save()

您的工作表将被命名为data0.csvdata1.csv等。如果您需要文件名作为工作表名称,您可以重构逻辑并使用os模块从path中提取文件名:

代码语言:javascript
复制
import os

writer = pd.ExcelWriter('out.xlsx', engine='xlsxwriter')

for f in all_files:
    df = pd.read_csv(f)
    df.to_excel(writer, sheet_name=os.path.basename(f))

writer.save()
票数 11
EN

Stack Overflow用户

发布于 2019-10-11 15:28:34

以下是jpp解决方案的完整源代码:

代码语言:javascript
复制
import os
import pandas as pd
import glob

path = './'
all_files = glob.glob(os.path.join(path, "*.csv"))

writer = pd.ExcelWriter('out.xlsx', engine='xlsxwriter')

for f in all_files:
    df = pd.read_csv(f)
    df.to_excel(writer, sheet_name=os.path.splitext(os.path.basename(f))[0], index=False)

writer.save()
票数 5
EN

Stack Overflow用户

发布于 2018-08-23 05:00:17

尽管与VBA相比,Python需要的代码行要少得多,但我可能会使用VBA来完成这类任务。

代码语言:javascript
复制
' Merge data from multiple sheets into separate sheets
Sub R_AnalysisMerger2()
    Dim WSA As Worksheet
    Dim bookList As Workbook
    Dim SelectedFiles As Variant
    Dim NFile As Long
    Dim FileName As String
    Dim Ws As Worksheet, vDB As Variant, rngT As Range
    Dim vFn, myFn As String

    Application.ScreenUpdating = False

    SelectedFiles = Application.GetOpenFilename(filefilter:="Excel Files (*.csv*), *.csv*", MultiSelect:=True)
    If IsEmpty(SelectedFiles) Then Exit Sub

    For NFile = LBound(SelectedFiles) To UBound(SelectedFiles)
        FileName = SelectedFiles(NFile)
        vFn = Split(FileName, "\")
        myFn = vFn(UBound(vFn))
        myFn = Replace(myFn, ".csv", "")
        Set bookList = Workbooks.Open(FileName, Format:=2)
        Set WSA = bookList.Sheets(1)
        vDB = WSA.UsedRange
        bookList.Close (0)
        Set Ws = Sheets.Add(after:=Sheets(Sheets.Count))
        ActiveSheet.Name = myFn
        Ws.Range("a1").Resize(UBound(vDB, 1), UBound(vDB, 2)) = vDB
    Next
    Application.ScreenUpdating = True

End Sub

' Merge data from multime files into one sheet.
Sub R_AnalysisMerger()
    Dim WSA As Worksheet
    Dim bookList As Workbook
    Dim SelectedFiles() As Variant
    Dim NFile As Long
    Dim FileName As String
    Dim Ws As Worksheet, vDB As Variant, rngT As Range

    Application.ScreenUpdating = False


    Set Ws = ThisWorkbook.Sheets(1)
    Ws.UsedRange.Clear
    'change folder path of excel files here
    SelectedFiles = Application.GetOpenFilename(filefilter:="Excel Files (*.csv*), *.csv*", MultiSelect:=True)


    For NFile = LBound(SelectedFiles) To UBound(SelectedFiles)
        FileName = SelectedFiles(NFile)
        Set bookList = Workbooks.Open(FileName, Format:=2)
        Set WSA = bookList.Sheets(1)
        With WSA
            vDB = .UsedRange
            Set rngT = Ws.Range("a" & Rows.Count).End(xlUp)(2)
            If rngT.Row = 2 Then Set rngT = Ws.Range("a1")
            rngT.Resize(UBound(vDB, 1), UBound(vDB, 2)) = vDB

            bookList.Close (0)
        End With
    Next
    Application.ScreenUpdating = True
    Ws.Range("A1").Select

End Sub
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/51964001

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档