使用宏合并CSV文件

问题描述

有人知道如何合并一个文件夹中的* .csv文件吗？

Any idea how to merge *.csv files from one folder?

我有很多结构相同的* .csv文件（列的计数和标题），我需要将它们的内容合并为一张纸。

I have many *.csv files with same structure (count & heading of columns) and I need to merge their content into one sheet.

我知道这并不难。但是，当我从一个表中添加内容时，我需要添加新列，并在其中添加要从中复制此数据的表的名称。

It's not so hard, I know. But When I add content from one table, I need to add new column with name of table where I copied this data from.

请帮忙吗？

谢谢！

推荐答案

这似乎是解决此问题的好方法。它合并所有csv文件的内容，并从第二个文件中删除标头！ ：），但仍然需要通过添加源文件名称来解决该问题。

This seems like a good aproach how to solve this. It merge content of all csv files and delete headers from 2nd file! :) But still need to solve that problem with adding name of source file.

Option Explicit

Option Explicit

Sub ImportCSV （）

Sub ImportCSV()

Dim strSourcePath As String
Dim strDestPath As String
Dim strFile As String
Dim strData As String
Dim x As Variant
Dim Cnt As Long
Dim r As Long
Dim c As Long

Application.ScreenUpdating = False

'Change the path to the source folder accordingly
strSourcePath = "C:\Path\"

If Right(strSourcePath, 1) <> "\" Then strSourcePath = strSourcePath & "\"

'Change the path to the destination folder accordingly
strDestPath = "C:\Path\"

If Right(strDestPath, 1) <> "\" Then strDestPath = strDestPath & "\"

strFile = Dir(strSourcePath & "*.csv")

Do While Len(strFile) > 0
    Cnt = Cnt + 1
    If Cnt = 1 Then
       r = 1
   Else
       r = Cells(Rows.Count, "A").End(xlUp).Row + 1
   End If
    Open strSourcePath & strFile For Input As #1
        If Cnt > 1 Then
           Line Input #1, strData
       End If
        Do Until EOF(1)
            Line Input #1, strData
            x = Split(strData, ",")
            For c = 0 To UBound(x)
                Cells(r, c + 1).Value = Trim(x(c))
            Next c
            r = r + 1
        Loop
    Close #1
    Name strSourcePath & strFile As strDestPath & strFile
    strFile = Dir
Loop

Application.ScreenUpdating = True

If Cnt = 0 Then _
    MsgBox "No CSV files were found...", vbExclamation

End Sub

这篇关于使用宏合并CSV文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！