How To Consolidate Multiple CSV Files In A Single CSV File Automatically?

[xlmaniac]

Dear All,

I do have six different CSV files in a FTP folder.

Content of the files are refreshed on a daily basis from system(once in a day) and spooled in the FTP folder.

Name of the CSV files are MSF,PO, GRN,OD,PGI & Sales.

In each of these files, data is available only in Sheet1.

The content of these 6 files need to be consolidated in a single CSV file, named DUMP, located in the FTP folder.

6 different sheets need to be created in the DUMP file with TAB names MSF,PO, GRN,OD,PGI & Sales.

Post this, the entire data-set from each of the 6 source files, need to be populated in the destination file, named DUMP across 6 different sheets.

For example, data from MSF file needs to be put in the Sheet named MSF under DUMP file.

Similarly, data from PO file needs to be put in the Sheet named PO under DUMP file.

Would be grateful, if somebody helps with a solution to achieve this.

Regards

 

[xlmaniac]

Dear Herakles,
Thanks again for your reply.
All the files have only one sheet with data in it.
Yes..The Tab Name is correct and as per the File name e.g MSF, GRN, PGI, Sales.
There are 2 more sheets named Sheet2 & Sheet3 but there is no data.
OD & PO file tabs are not getting created.
Pls check.
Thanks a lot for all your support.
Regards

 

[Herakles]

Dear Herakles,
Thanks again for your reply.
All the files have only one sheet with data in it.
Yes..The Tab Name is correct and as per the File name e.g MSF, GRN, PGI, Sales.
There are 2 more sheets named Sheet2 & Sheet3 but there is no data.
OD & PO file tabs are not getting created.
Pls check.
Thanks a lot for all your support.
Regards

Try this.

You will get a prompt before each file import. We can take that out later.

How many sheets do you get by default if you create a new wrokbook?

Public Sub subImportCSVFiles()
Dim arrFiles() As String
Dim i As Integer
Dim strPath As String
Dim WbDump As Workbook
Dim WbMain As Workbook
Dim intSheets As Integer
Dim strFolder As String
Dim FldrPicker As FileDialog

    ActiveWorkbook.Save

    Set WbMain = ActiveWorkbook
    
    strPath = WbMain.Path & "\"

    Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
        .Title = "Select The Folder Containing The CSV Files."
        .InitialFileName = strPath
        .AllowMultiSelect = False
        If .Show <> -1 Then
            Exit Sub
        End If
        strPath = .SelectedItems(1) & "\"
    End With
  
    On Error Resume Next
    Workbooks("Dump.xlsx").Close savechanges:=False
    Kill (WbMain.Path & "\" & "Dump.xlsx")
    On Error GoTo 0

    Workbooks.Add
    
    Set WbDump = ActiveWorkbook
    
    With WbDump
        .SaveAs Filename:=WbMain.Path & "\" & "Dump.xlsx"
    End With
    
    intSheets = WbDump.Sheets.Count
    
    arrFiles = Split("MSF,PO,GRN,OD,PGI,Sales", ",")

    Application.ScreenUpdating = True

    For i = UBound(arrFiles) To LBound(arrFiles) Step -1
        
        Workbooks.Open strPath & arrFiles(i) & ".csv"
        
        If MsgBox("Copy this sheet?", vbYesNo, "Question") = vbYes Then
    
            Sheets(1).Copy after:=WbDump.Sheets(Sheets.Count)
      
        End If
    
        Workbooks(arrFiles(i) & ".csv").Close savechanges:=False
    
    Next i
    
    Application.DisplayAlerts = False
    For i = 1 To intSheets
        ' WbDump.Sheets(i).Delete
    Next i
    Application.DisplayAlerts = False

    WbDump.Save

    Application.ScreenUpdating = True

    MsgBox "Data from the following CSV files has been" & vbCrLf & _
        "imported into the Dump.xlsx workbook." & vbCrLf & _
        vbCrLf & Join(arrFiles, ".csv" & vbCrLf) & ".csv", vbOKOnly, "Confirmation"

End Sub

 

[xlmaniac]

Thank you so much Herakles for your support.
Truly appreciate the same.
Yes, now all sheets are getting populated as per the File names with relevant data.
Prompt is also coming up to facilitate this transition.

 

[Herakles]

Thank you so much Herakles for your support.
Truly appreciate the same.
Yes, now all sheets are getting populated as per the File names with relevant data.
Prompt is also coming up to facilitate this transition.

I assumed that a new workbook had one worksheet in it instead of however many you specified on your Excel installation.

Do you want to take the prompt out and the empty sheets?

 

[xlmaniac]

I assumed that a new workbook had one worksheet in it instead of however many you specified on your Excel installation.

Do you want to take the prompt out and the empty sheets?

Thanks for your reply.
It would be better if the prompt could be removed for ease of operation.
Rest is perfect.
Thanks once again for the mesmerising solution!!

 

[Herakles]

Thanks for your reply.
It would be better if the prompt could be removed for ease of operation.
Rest is perfect.
Thanks once again for the mesmerising solution!!

Prompt removed.

Public Sub subImportCSVFiles()
Dim arrFiles() As String
Dim i As Integer
Dim strPath As String
Dim WbDump As Workbook
Dim WbMain As Workbook
Dim intSheets As Integer
Dim strFolder As String
Dim FldrPicker As FileDialog

    ActiveWorkbook.Save

    Set WbMain = ActiveWorkbook
    
    strPath = WbMain.Path & "\"

    Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
        .Title = "Select The Folder Containing The CSV Files."
        .InitialFileName = strPath
        .AllowMultiSelect = False
        If .Show <> -1 Then
            Exit Sub
        End If
        strPath = .SelectedItems(1) & "\"
    End With
  
    On Error Resume Next
    Workbooks("Dump.xlsx").Close savechanges:=False
    Kill (WbMain.Path & "\" & "Dump.xlsx")
    On Error GoTo 0

    Workbooks.Add
    
    Set WbDump = ActiveWorkbook
    
    With WbDump
        .SaveAs Filename:=WbMain.Path & "\" & "Dump.xlsx"
    End With
    
    intSheets = WbDump.Sheets.Count
    
    arrFiles = Split("MSF,PO,GRN,OD,PGI,Sales", ",")

    Application.ScreenUpdating = True

    For i = UBound(arrFiles) To LBound(arrFiles) Step -1
        
        Workbooks.Open strPath & arrFiles(i) & ".csv"
    
        Sheets(1).Copy after:=WbDump.Sheets(Sheets.Count)
    
        Workbooks(arrFiles(i) & ".csv").Close savechanges:=False
    
    Next i
    
    WbDump.Save

    Application.ScreenUpdating = True

    MsgBox "Data from the following CSV files has been" & vbCrLf & _
        "imported into the Dump.xlsx workbook." & vbCrLf & _
        vbCrLf & Join(arrFiles, ".csv" & vbCrLf) & ".csv", vbOKOnly, "Confirmation"

End Sub

 

[xlmaniac]

Simply awesome!!
Pls accept my sincere gratitude for your support and solution.