Combining multiple Microsoft Excel Comma Separated Values

[Stacy Rueda]

Hi everyone!

Please help me with combining multiple Microsoft Excel Comma Separated Values, I want to combine my 40 csv files in one excel without opening and copy paste them one by one, and everyday our production machine producing this csv file.After creating a macro tool, I hope this tool will help me to combine into my master data if possible it will be formatted in pivot table. Thank you so much. can someone help me, if you have code already and have a big heart to share it with me.

Any assistance regarding this matter will be much appreciated.

 

[Joe4]

I am not quite clear if for your end result you want one large CSV file, or one large Excel file.

If CSV, see if this helps:
https://www.mrexcel.com/forum/excel...-can-i-merge-multiple-csv-files-into-one.html

If Excel, there are many other threads that deal with that, i.e.
https://www.mrexcel.com/forum/excel...e-all-new-csvs-folder-into-one-worksheet.html
https://www.mrexcel.com/forum/excel...vba-non-responsive-locals-filename-shows.html
https://www.mrexcel.com/forum/excel-questions/623417-combine-multiple-csv-files-one-excel-file.html

 

[Stacy Rueda]

Hi @Joe4, Thank you so much for the help, the first link (combine to excel) helps me a lot but I still have one problem, all header of CSV files were uploaded or imported also in one excel, which is one top header is enough. How can I remove that? Thanks a lot!

 

[Joe4]

all header of CSV files were uploaded or imported also in one excel, which is one top header is enough. How can I remove that?

How many rows is your header? Is it always just the first row?
Can you post your current code, and we can help you amend it to only include the header on the first file?

 

[Stacy Rueda]

Hi @Joe4 , thank you so much for the reply. Here is my import/compilation code of CSV files to one excel. Currently I have 38 csv files, and my excel have 38 headers. I wanted to get the header of my 1st csv file and ignore the header of 2nd up to 38th csv file. Thank you so much again for the help.

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 = "D:\Error Log\Error Log_Y"
    
    If Right(strSourcePath, 1) <> "\" Then strSourcePath = strSourcePath & "\"
    
    'Change the path to the destination folder accordingly
    strDestPath = "D:\Error Log\Error Log_Y"
    
    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 [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] 
            If Cnt > 1 Then
                Line Input [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] , strData
            End If
            Do Until EOF(1)
                Line Input [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] , 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 [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] 
        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

 

[Joe4]

It seemed to work fine for me.
It works if there is exactly one row of header, and it is the first row.
How are your CSV files structured?

 

[Stacy Rueda]

Thank you again..Sorry @Joe4, but I checked only this time that the format of csv files are like these.

Loc. Cell A1 - V1.02, Cell B1 - MultiLanguage
Loc. Cell A2 - Event Date, Cell B2 - Event Name and so on...

It means that there are two headers that needs to be deleted. Can you help me?
Above code deleted only the first header which is "Loc. Cell A1 - V1.02, Cell B1 - MultiLanguage" but I need also to delete this Row "Loc. Cell A2 - Event Date, Cell B2 - Event Name and so on..."

 

[Joe4]

If there are two header rows, try changing this section:

            If Cnt > 1 Then
                Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , strData
            End If

to this:

            If Cnt > 1 Then
                Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , strData
                Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , strData
            End If

 

[Stacy Rueda]

Wow, THANK YOU SO MUCH @joe4, It works perfectly! You are really a blessing to a kind of human like me who is a novice in excel vba macro..

 

[Stacy Rueda]

Hi @Joe4, related to my csv file above questions. I want to delete rows also in cells that contains "The power of machine was turned on" and "The power of machine was turned off" because I don't need that in my pivot table.

I used below code, that one performs perfectly but deleted only "The power of machine was turned on". My question is, how can I delete also ""The power of machine was turned off". And can I put below code to above code (import of csv file) so I don't have two command button? Sorry, but I don't have knowledge in macro. Thank you so much

Btw, I have different worksheet (named Function button) which is my 3 command button (Clear "Data" sheet, Import CSV files, Refresh pivot table) located. I just want also to target the specific "Data" Sheet created by import macro code.

Sub Button2_Click()
'========================================================================
' DELETES ALL ROWS FROM A2 DOWNWARDS WITH THE WORDs "The power of machine was turned on" IN COLUMN F
'========================================================================
    Last = Cells(Rows.Count, "F").End(xlUp).Row
    For i = Last To 1 Step -1
        If (Cells(i, "F").Value) = "The power of machine was turned on." Then
          'Cells(i, "A").EntireRow.ClearContents ' USE THIS TO CLEAR CONTENTS BUT NOT DELETE ROW
            Cells(i, "A").EntireRow.Delete
        End If
    Next i
 End Sub