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

 

[Anonymous1378]

CSV files fundamentally cannot support multiple sheets; all sheets besides the first one will evaporate once you save as CSV. What exactly are you trying to do with the single CSV anyway?

 

[Herakles]

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

This code will copy all of the CSV worksheets into a workbook named Dump.xlsx.

Comment from Anonymous1378 applies.

Folder containing CSV files needs to be selected.

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 = False

    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
    
    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]

Dear Herakles,
Thanks a lot for your time and support.
I have copied the code in the module of the DUMP excel file and then selected the folders where the 6 CSV files are kept but while running the code, the DUMP file is getting closed and data is not populated in the DUMP sheet.
Is there anything else which needs to be done at my end to get this done?
Pls help.
Regards

 

[Herakles]

Dear Herakles,
Thanks a lot for your time and support.
I have copied the code in the module of the DUMP excel file and then selected the folders where the 6 CSV files are kept but while running the code, the DUMP file is getting closed and data is not populated in the DUMP sheet.
Is there anything else which needs to be done at my end to get this done?
Pls help.
Regards

The code needs to be in a seperate .xlsm workbook. That may be the problem.

The code creates the Dump workbook.

 

[Herakles]

The code needs to be in a seperate .xlsm workbook. That may be the problem.

The code creates the Dump workbook.

The code assumes that all csv files exist.

 

[xlmaniac]

Thanks Herakles for your support again.
I have followed your guidelines and have run the macro twice and in every instance data is getting populated only from the following 4 Files namely:-MSF, GRN, PGI & Sales. But the confirmation window is coming stating that the split has been done for all 6 files.
Data from PO & OD files are not getting populated.
Could you pls check one more time?
Regards

 

[Herakles]

Thanks Herakles for your support again.
I have followed your guidelines and have run the macro twice and in every instance data is getting populated only from the following 4 Files namely:-MSF, GRN, PGI & Sales. But the confirmation window is coming stating that the split has been done for all 6 files.
Data from PO & OD files are not getting populated.
Could you pls check one more time?
Regards

That is weird.

Do you have the 6 worksheets in Dump.xlsx but just not data in PO and OD?

It would error if the files could not be found.

 

[xlmaniac]

Thanks Herakles for your reply!!
6 Files are there in the folder with data.
I have again run the macro and this time also 4 sheets are getting generated in the Dump.xls with sheet2 & sheet3 with no data in it.
Pls check once and help.
Regards

 

[Herakles]

Do all of the 6 csv workbooks only have one worksheet. In theory this is impossible as the csv format does not support more than one.

Do the sheets in Dump.xlsx have the correct names?

It is copying the 6 named worksheets with the right names from somewhere.

sheet2 & sheet3?

Not the PO and OD sheets.