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

Question

>我有一个代码，可以在一个目录中生成多个CSV文件。我想在 excel 中生成一份报告，该报告将包含作为单独选项卡的 CSV 文件。 我使用了以下代码：

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()

但它给出了以下错误： 属性错误："生成器"对象没有属性"to_excel">不知道我哪里出错了。我是否需要导入任何特定的库来解决问题？

Python 版本是 2.7

Answer 1

这里有两个问题：

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

为此，您可以使用简单的for循环：

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.csv、data1.csv等。如果需要文件名作为工作表名称，可以重构逻辑并使用os模块从 path 中提取文件名：

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()

Answer 2

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

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()

Answer 3

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

' 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

Answer 4

你可以使用熊猫简洁的方法

csv1 = pd.read_csv(csv1_file_path)
csv2 = pd.read_csv(csv2_file_path)
merge_csv = pd.concat((csv1, csv2), axis=0)

轴是用户在哪个方向上合并