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]

Why not hit both at the same time by changing this line:

If (Cells(i, "F").Value) = "The power of machine was turned on." Then

to this:

If Left(Cells(i, "F"),31) = "The power of machine was turned" Then

 

[Joe4]

I see that you had also posted a similar question here: https://www.mrexcel.com/forum/excel...ic-words-cells-another-table-post5108295.html

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will typically be locked or deleted (rule 12 here: Forum Rules).

 

[Stacy Rueda]

Noted above sir, it's my fault. It's just I think nobody will make a reply with this thread because you are the one replying with my questions. And because this is urgent, I thought by creating a new thread will help me as soon as possible..But thank you for reminding me about the rules of our forum. and for answering my questions. May God bless you sir for your generous heart of giving reliable answers..

 

[Stacy Rueda]

Hi @Joe4, can I add another question related to the code above. I have post here that I have two rows to be deleted in all my CSV files right? which is like below:

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

But why is that my first (no.1) CSV file did not delete it's Loc. Cell A1 - V1.02, Cell B1 - MultiLanguage. I hope you can still help me with this..But Loc. Cell A2 is OK, i need that for my main header of all my imported CSV files. Thank you..

 

[Joe4]

It sounded like you had two header rows, so it allowed two for the first file, and then ignored the first two rows on all the other files.

One easy solution would be to just delete the first row after the imports are created (so add a line to delete the first row at the end of your code).

 

[Joe4]

That final code would look like this:

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
                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
    
[B][COLOR=#ff0000]    Rows(1).Delete[/COLOR][/B]
    
    If Cnt = 0 Then _
        MsgBox "No CSV files were found...", vbExclamation
    
End Sub

 

[Stacy Rueda]

Hi sir @Joe4, this works perfectly *insert happy dance**. Thank you so much for the help...

 

[Joe4]

You are welcome.