CODEWITHSUNDEEP
vbaexcel
user1742416
user1742416

Reputation: 23

Count data from multiple csv files

I've got hundreds of folders in links to them in a shared folder
Eg:

a:\Images\abc\year\month\date\names1
a:\Images\abc\year\month\date\names2
a:\Images\abc\year\month\date\names3 etc,.

Each folder has a csv in it (abc.csv constant name in each folders) containing few info.

Eg:

1 Orange USA
2 Apple England
3 Orange Australia
4 Orange Austria
5 Apple India

Here I would like to count the number of Oranges, Apples, Grapes and Melons in CSV of Different Folders.

I'm expecting output like:

Link    Oranges Apples  Grapes  Melon

\names1 5846    2000    85215   586

\Names2 4521    1542    45852   125

\Names3 365     4856    25415   548

Kindly Help...

Upvotes: 2

Views: 481

Answers (1)

nutsch
nutsch

Reputation: 5962

Try this code. It will open the file abc.csv contained in each folder listed in columns(1), count the items, then close the file and move on.

Option Explicit

Sub CountApples()
Dim wbk As Workbook, sht As Worksheet, wbkTemp As Workbook, lLoop As Long, lLastRow As Long

'turn off updates to speed up code execution
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With

Set sht = ActiveSheet

lLastRow = sht.Cells(sht.Rows.Count, 1).End(xlUp).Row

For lLoop = 2 To lLastRow
    Set wbkTemp = Workbooks.Open(sht.Cells(lLoop, 1) & "\abc.csv")

    sht.Cells(lLoop, 2).Value = Application.WorksheetFunction.CountIf(wbkTemp.Sheets(1).Columns(1), "Oranges")
    sht.Cells(lLoop, 3).Value = Application.WorksheetFunction.CountIf(wbkTemp.Sheets(1).Columns(1), "Apples")
    sht.Cells(lLoop, 4).Value = Application.WorksheetFunction.CountIf(wbkTemp.Sheets(1).Columns(1), "Grapes")
    sht.Cells(lLoop, 5).Value = Application.WorksheetFunction.CountIf(wbkTemp.Sheets(1).Columns(1), "Melon")

    wbkTemp.Close (False)
Next lLoop

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
End With


End Sub

Upvotes: 2

Related Questions