将多个Excel工作簿中的数据合并并追加到一个工作表中 [英] Combine and Append data from multiple Excel workbooks into one worksheet

问题描述

下面，我找到了一个在线的VBA脚本，该脚本从目录中的工作簿中获取所有工作表，并将所有工作表合并为一个单独的工作表.但是，我不希望它们分开.我希望工作表中的所有数据都在一个工作表中.

Below I found a VBA Script online that takes all worksheets from workbooks in a directory, and combines them all in one workbook as separate sheets. However, I don't want them to be separate sheets. I want all data in the sheets to be in a single worksheet.

Sub GetSheets()
    Path = "Desktop\RandoDir"
    Filename = Dir(Path & "\*.csv*")
    Do While Filename <> ""
        Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
        For Each Sheet In ActiveWorkbook.Sheets
            'MsgBox Filename      ---Debugging
            Sheet.Copy After:=ThisWorkbook.Sheets(1)
        Next Sheet
        Workbooks(Filename).Close
        Filename = Dir()
   Loop
End Sub

如果我在两个文件中具有这样的数据结构:

If I had data structured like this in two files:

   **File1**                **File2**
Header1|Header2    |     Header1|Header2
Tim    |Smith      |     Mike   |Jones

我希望显示合并的工作表

I want the merged worksheet to display

   **File3**
Header1|Header2
Tim    |Smith
Mike   |Jones

推荐答案

如果不需要VBA，则可以在包含CSV的文件夹中使用DOS命令

If VBA is not required, you could use DOS commands in the folder containing the CSVs

mergeCSVs.bat (在CSV文件夹中):

mergeCSVs.bat (in the CSV folder):

copy *.csv all.csv

echo Header1,Header2 > result.csv

type all.csv | findstr /v Header1,Header2 >> result.csv

del all.csv

---

• 复制* .csv all.csv -将所有CSV合并到一个新文件 all.csv (具有重复标题)
• echo Header1，Header2>result.csv -创建一个新的 result.csv 并写入第一行
• 键入all.csv |findstr/v Header1，Header2>result.csv
  • 键入all.csv -从 all.csv
  中提取所有数据
  • findstr/v Header1，Header2 -从 all.csv
  过滤掉所有标题行
  • >>result.csv -将所有这些数据附加到 result.csv

  ---

  • copy *.csv all.csv - combines all CSVs into a new file all.csv (with repeating headers)
  • echo Header1,Header2 > result.csv - creates a new result.csv and writes the first row
  • type all.csv | findstr /v Header1,Header2 >> result.csv
    • type all.csv - extracts all data from all.csv
    • findstr /v Header1,Header2 - filters out all header rows from all.csv
    • >> result.csv - appends all this data into the result.csv

    results.csv将包含合并的数据

    The results.csv will contain the merged data

    csv1.csv:

    Header1,Header2
    Tim,Smith
    

    csv2.csv:

    Header1,Header2
    Mike,Jones
    

    result.csv:

    result.csv:

    Header1,Header2 
    Tim,Smith
    Mike,Jones
    

    ---

    注意:

    • 两个csv文件的末尾都包含一个空行
    • 它们之间用逗号分隔(而不是示例中的管道分隔)

    这篇关于将多个Excel工作簿中的数据合并并追加到一个工作表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！