Process All CSV Files In SubFolders

**SamT** · 06-02-2015, 08:10 AM

1) open one excel from Start Menu
2) open VBA
3) insert Module into VBAProject
4) past the code #58
5) go back to excel.
Run macro from menu
Result on Excel sheet.

However. #58 has three message boxes. One for every file name. One for every file line. One for current result. It will be a very long time to run the sub thru even one folder if you must press on three mesgBox.

Run the code as above. See if you see the Message boxes. The first one will say "Working on file (filename). The next will say "Line # (n) is (shows the line). The third box will let you stop the code.

Do you see any message boxes?

I will be back in an hour or so.

**Kenneth Hobs** · 06-02-2015, 08:11 AM

You seem to be going in circles. Advice was given earlier on ways to check things.

Again, this must show True:

    MsgBox Dir("C:\Users\dbrandejs\david\skola\IES\Diplomka\adjusted data\allstocks_20130102\") <> ""

**snb** · 06-02-2015, 08:44 AM

My umpthiest contribution

NB. Never (yes, never!) use spaces in foldernames.

**maxxino** · 06-02-2015, 11:50 AM

snb - when I open your xlsb file and push start - numbers show in following order: 99, 151, and then 9 on and on

SamT - when I change foldername to "adjusted_data", then run #58 - the first msg box says: Working on File C:\Users\dbrandejs\david\skola\IES\Diplomka\adjusted_data\allstocks_2013010 2\table_aapl.csv
the second one: Line #1 is a20130102,700,544.72,546.4,544.35,545.75,15605,1,0,0
the third one: Run-time error '13': Type mismatch

kenneth - msg box shows True

**maxxino** · 06-02-2015, 11:58 AM

SamT - huh, I again changed the settings of dots and commas in control panel and now it seems to work, the first two msg boxes remain the same, but the third one is showing zero now,
further it goes accordingly, firstly msx box shows what is in the line and then the result..

**Kenneth Hobs** · 06-02-2015, 12:23 PM

We have already taken care of the space character issue in previous code. SNB explained it again in his code. For Sam's code you need to add those quote characters to take care of the space characters.
Of course when you do this, checks for folder or filename existence use Dir() as I demonstrated, will fail. I normally just define a string as Sam did and then add the quote characters by concatenating them via q where q="""".

   Const FolderPath As String = """C:\Users\dbrandejs\david\skola\IES\Diplomka\adjusted data\allstocks_20130102\""" 'include ending \

This is really something that you should be addressing. Use the debugging methods that I detailed in some past post here. We can only do so much for you.

We did some error checks but there are others that can be done.

Sub ken()   
  Dim FolderPath As String, q As String, fn As String, Msg As String
   
   On Error GoTo ErrMsg
   
   q = """"
   FolderPath = "C:\Users\dbrandejs\david\skola\IES\Diplomka\adjusted data\allstocks_20130102\" 'include ending \
   
   fn = q & FolderPath & q
   
   MsgBox FolderPath & vbLf & "Exists? " & (Dir(FolderPath, vbDirectory) <> "")
   'Next line will error:
   MsgBox fn & vbLf & "Exists? " & (Dir(fn, vbDirectory) <> "")
   
    'Exit Sub
ErrMsg:
    If Err.Number <> 0 Then
        Msg = "Error # " & Str(Err.Number) & " was generated by " _
                & Err.Source & Chr(13) & Err.Description
        MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
        Exit Sub
    End If
End Sub

**snb** · 06-02-2015, 12:46 PM

Hi it means:

- 100 files have been found
- a file consists of 152 lines
- a line consists of 10 'fields'

So the macro in the attachment will perform it's duty without any msgbox interruption.

**maxxino** · 06-02-2015, 01:56 PM

OK so now when it is clarified, I tried to run the code from #19 according to SamT's post #44, I deleted "For testing only" part from the code in #19 and finally I get some output - in column D there is a name of the file and in columns B and C there are numbers, problem is that all the numbers are zeros - Sam did you make any corrections to code #19? As you wrote that you got non-zero result in #44

**SamT** · 06-02-2015, 02:00 PM

Start over.

Read the entire thread for bottom to top. Yes, read #67 first, then #66, 65, 64, etc.

**maxxino** · 06-08-2015, 08:51 AM

Hi guys,

I made some adjustments in the settings of my pc and code #53 works for me exactly in the way as Sam describes. thank you very much!!
table_aapl.csv -4.8E+48
table_abbv.csv -9E+30

The only remainig problem is, that the calculation included is not exactly the same as I described, I need a slightly different equation, it seems to me that in #23 there is an equation: [Log(F1) - Log(F1-1)^2)] * 100, but I rather need:
Y_i = {[Log (X) - Log (X-1)] * 100 }^2 and then the summary of all Y for i=1,...,I; if I itemize it:

1)equation in column K: K= log (F)
2)equation since column L2: L2=[100*(K2-K1)]^2, this equation is made accordingly in the whole column L, except cell L1, i.e. L3=[100*(K3-K2)]^2; L4=[100*(K4-K3)]^2;...
3)sum of the whole column L, and this result (one number) should get into column B according to code #53

I tried to calculate manually table_aapl.csv and the result is 0,504763141272035, thus it should be the result generated in the column B

**SamT** · 06-08-2015, 10:51 AM

The only remainig problem is, that the calculation included is not exactly the same as I described, I need a slightly different equation, it seems to me that in #23 there is an equation: [Log(F1) - Log(F1-1)^2)] * 100, but I rather need:
Y_i = {[Log (X) - Log (X-1)] * 100 }^2 and then the summary of all Y for i=1,...,I; if I itemize it:

1)equation in column K: K= log (F)
2)equation since column L2: L2=[100*(K2-K1)]^2, this equation is made accordingly in the whole column L, except cell L1, i.e. L3=[100*(K3-K2)]^2; L4=[100*(K4-K3)]^2;...

Do you mean
Y_i = {[Log (F) - Log (F-1)] * 100 }^2

Maybe this

  'Replace file line with Log of 6th value. Split(BlahBlah)(5)
                Formula1LinesArray(Fl) = Log(Split(Formula1LinesArray(Fl), ",")(5)) 'Column F
                 'After the first line... Formula1Result = Sum
                If Fl > 0 Then Formula1Result = Formula1Result +  _ 
                (Formula1LinesArray(Fl) - Formula1LinesArray(Fl - 1)  * 100 ) ^2 'Column L

**maxxino** · 06-08-2015, 03:34 PM

Hi Sam,

yes I mean that: Y_i = {[Log (F) - Log (F-1)] * 100 }^2, but it means to add one parenthesis in the code:

((Formula1LinesArray(Fl) - Formula1LinesArray(Fl - 1)) * 100) ^ 2 'Column L

however even this code gives a wrong result 2.67620274495088, I attach the testing csv file, where I carried out the manual calculation with the right result 0.504763141272035

is the calculation in the code right according to my manual calculation?

**SamT** · 06-08-2015, 07:37 PM

I rewrote the procedure long form so that you can see the steps in the calculations and adjust the formula as needed. It will take a few seconds longer to run.

Option Explicit
 
Sub SamT_2()
    Dim Filename As String
    Dim NameLength As Long
     
    Dim FileNameArray As Variant 'sn
    Dim FileLinesArray As Variant 'sp
    Const ColumnF As Long = 5 'CSV field number counting from zero
     
    Dim Formula1Result As Double 'y
    
    Dim ColumnKArray As Variant
    Dim ColumnLArray As Variant
     
    Dim Fn As Long 'Fn = Index number for FileNameArray                     'jj
    Dim CR As Long 'CR = Column Arrays Index number. Same As Column Row number                                'j
     
    Const FolderPath As String = "C:\Users\dbrandejs\david\skola\IES\Diplomka\adjusted data\allstocks_20130102\" 'include ending \
     
     
     '''' Put all the file names in the path in Array
    FileNameArray = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir " & _
    FolderPath & "*.csv /b /s").stdout.readall, vbCrLf), ".")
     
     
     '''' Open one file at a time
    With CreateObject("scripting.filesystemobject")
        For Fn = 0 To UBound(FileNameArray)
             
              'Initializ
            Formula1Result = 0
            
             ''''Put all lines from one file in Arrays
            FileLinesArray = Split(.opentextfile(FileNameArray(Fn)).readall, vbLf)
            ReDim ColumnKArray(UBound(FileLinesArray))
            ReDim ColumnLArray(UBound(FileLinesArray) + 1)
             
             ''''Log(F) into Column K
            For CR = 0 To UBound(FileLinesArray)
               ColumnKArray(CR) = Log(Split(FileLinesArray(CR), ",")(ColumnF))
            Next CR
                 
            ''''Calculate Formula on Column K, put in Column L
            For CR = 0 To UBound(ColumnKArray) - 1 '-1 to compensate for column formula offsets
               ColumnLArray(CR + 1) = (100 * (ColumnKArray(CR + 1) - ColumnKArray(CR))) ^ 2
            Next CR
                 
            ''''Sum of Column L
            For CR = 1 To UBound(ColumnKArray)
              FormulaResult = FormulaResult + ColumnLArray(CR)
            Next CR
             
             '''' Put results in sheet
             
             'Get FileName
            NameLength = Len(FileNameArray(Fn)) - InStrRev(FileNameArray(Fn), "\")
            Filename = Right(FileNameArray(Fn), NameLength)
             
             'Place result
            With ActiveSheet.Rows(Fn + 1)
                .Columns(1) = Filename
                .Columns(2) = Formula1Result
            End With
             
        Next Fn 'Work on next File
    End With
End Sub

**maxxino** · 06-09-2015, 08:20 AM

Hi Sam, I ran your code, but the error shows up: "Compile error: Variable not defined" and marks Ln51, Col 39; I though that it could be a typo and changed Ln59 in this way:
Formula1Result = Formula1Result + ColumnLArray(CR), but then this shows up: "Run-time error '9': Subscript out of range"

**Kenneth Hobs** · 06-09-2015, 09:19 AM

Change all of FormulaResult to Formula1Result.

**maxxino** · 06-09-2015, 12:57 PM

Hi Kenneth, yes I did that, I changed it only in Ln59 as only in this line is FormulaResult, but as I write in #75 then the error shows up: "Run-time error '9': Subscript out of range"

**SamT** · 06-09-2015, 04:02 PM

it is
For CR = 1 To UBound(ColumnKArray)

IT needs to be
For CR = 1 To UBound(ColumnLArray)

My bad.

Option Explicit
 
Sub SamT_3()
    Dim Filename As String
    Dim NameLength As Long
     
    Dim FileNames As Variant
    Dim FileLines As Variant
    Const F As Long = 5 'CSV field number counting from zero
     
    Dim K_Array As Variant
    Dim L_Array As Variant
    Dim Sum_L As Double
    
    Dim Fn As Long 'Fn = Index number for FileNames
    Dim CR As Long 'CR = Column Arrays Index number. Same As Column Row number                                'j
     
    Const FolderPath As String = "C:\Users\dbrandejs\david\skola\IES\Diplomka\adjusted data\allstocks_20130102\" 'include ending \
     
     
     '''' Put all the file names in the path in Array
    FileNames = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir " & _
    FolderPath & "*.csv /b /s").stdout.readall, vbCrLf), ".")
     
     
     '''' Open one file at a time
    With CreateObject("scripting.filesystemobject")
        For Fn = 0 To UBound(FileNames)
             
             'Initializ
            L_Array = 0
             
             ''''Put all lines from one file in Arrays
            FileLines = Split(.opentextfile(FileNames(Fn)).readall, vbLf)
            ReDim K_Array(UBound(FileLines))
            ReDim L_Array(UBound(FileLines) + 1)
             
             ''''Log(F) into Column K
            For CR = 0 To UBound(FileLines)
                K_Array(CR) = Log(Split(FileLines(CR), ",")(F))
            Next CR
             
             ''''Calculate Formula on Column K, put in Column L
            For CR = 0 To UBound(K_Array) - 1 '-1 to compensate for column formula offsets
                L_Array(CR + 1) = (100 * (K_Array(CR + 1) - K_Array(CR))) ^ 2
            Next CR
             
             ''''Sum of Column L
            For CR = 1 To UBound(K_Array)
                Sum_L = Sum_L + L_Array(CR)
            Next CR
             
             '''' Put results in sheet
             
             'Get FileName
            NameLength = Len(FileNames(Fn)) - InStrRev(FileNames(Fn), "\")
            Filename = Right(FileNames(Fn), NameLength)
             
             'Place result
            With ActiveSheet.Rows(Fn + 1)
                .Columns(1) = Filename
                .Columns(2) = Sum_L
            End With
             
        Next Fn 'Work on next File
    End With
End Sub

**maxxino** · 06-10-2015, 08:38 AM

Hi Sam, I copy-pasted your code from #77 and I tried both examples: "For CR = 1 To UBound(K_Array)" and "For CR = 1 To UBound(L_Array)", but in both cases it shows: "Run-time error '9': Subscript out of range"

**SamT** · 06-11-2015, 11:52 AM

After some investigation, I found that at least some CSV files have extra LineFeeds at the end. Trying to get the Log of the 6th element of "" caused the problem.

Check these results manually and see if this Sub is working now.

table_aapl.csv	2.6762027449490400
table_abbv.csv	16.3658370318833000

Also, Read and understand the formula in this code. It is still giving the same wrong result. But this is the formula you gave us.

BTW, In Table_appl_Test.csv, the value separator is a semicolon. This code only works with a comma separator. Don't be changing things on us without telling us.

Option Explicit
 
Sub SamT_3()
    Dim Filename As String
    Dim NameLength As Long
     
    Dim FileNames As Variant
    Dim FileLines As Variant
    Const F As Long = 5 'CSV field number counting from zero
     
    Dim K_Array As Variant
    Dim L_Array As Variant
    Dim Sum_L As Double
     
    Dim Fn As Long 'Fn = Index number for FileNames
    Dim CR As Long 'CR = Column Arrays Index number. Same As Column Row number                                'j
     
    Const FolderPath As String = "C:\TestFolder\" 'include ending \ '<<<<<<<<<<<<<<<<<<<<<<<<<
     
     
     '''' Put all the file names in the path in Array
    FileNames = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir " & _
    FolderPath & "*.csv /b /s").stdout.readall, vbCrLf), ".")
     
     
     '''' Open one file at a time
    With CreateObject("scripting.filesystemobject")
        For Fn = 0 To UBound(FileNames)
             
             ''''Put all lines from one file in Arrays
            FileLines = Split(.opentextfile(FileNames(Fn)).readall, vbLf)
            
            'Compensate for extra vbLf's in FileLines '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
            Do While FileLines(UBound(FileLines)) = ""
              ReDim Preserve FileLines(UBound(FileLines) - 1)
            Loop
            
            ReDim K_Array(UBound(FileLines))
            ReDim L_Array(UBound(FileLines) + 1)
             
             ''''Log(F) into Column K
            For CR = LBound(FileLines) To UBound(FileLines) - 1
                K_Array(CR) = Log(Split(FileLines(CR), ",")(F))
            Next CR
             
             ''''Calculate Formula on Column K, put in Column L
            For CR = 0 To UBound(K_Array) - 1 '-1 to compensate for column formula offsets
                L_Array(CR + 1) = (100 * (K_Array(CR + 1) - K_Array(CR))) ^ 2
            Next CR
             
             ''''Sum of Column L
            For CR = 1 To UBound(K_Array)
                Sum_L = Sum_L + L_Array(CR)
            Next CR
             
             '''' Put results in sheet
             
             'Get FileName
            NameLength = Len(FileNames(Fn)) - InStrRev(FileNames(Fn), "\")
            Filename = Right(FileNames(Fn), NameLength)
             
             'Place result
            With ActiveSheet.Rows(Fn + 1)
                .Columns(1) = Filename
                .Columns(2) = Sum_L
            End With
             
        Next Fn 'Work on next File
    End With
End Sub

**maxxino** · 06-11-2015, 02:01 PM

Hi Sam, yes, this code works, I get the same results as you do with #79:

table_aapl.csv	397845.6
table_abbv.csv	524507.7

but I already calculated manually the correct result in #72 and it should be 0.504763 for table_aapl.csv

Thread: Process All CSV Files In SubFolders

Thread Tools

Display

Posting Permissions