Consulting

Page 4 of 6 FirstFirst ... 2 3 4 5 6 LastLast
Results 61 to 80 of 119

Thread: Process All CSV Files In SubFolders

  1. #61
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,077
    Location
    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.
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  2. #62
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,903
    Location
    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\") <> ""

  3. #63
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,744
    My umpthiest contribution


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

  4. #64
    VBAX Regular
    Joined
    May 2015
    Posts
    33
    Location
    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

  5. #65
    VBAX Regular
    Joined
    May 2015
    Posts
    33
    Location
    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..

  6. #66
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,903
    Location
    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
    Last edited by Kenneth Hobs; 06-02-2015 at 12:34 PM.

  7. #67
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,744
    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.
    Attached Files Attached Files

  8. #68
    VBAX Regular
    Joined
    May 2015
    Posts
    33
    Location
    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

  9. #69
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,077
    Location
    Start over.

    Read the entire thread for bottom to top. Yes, read #67 first, then #66, 65, 64, etc.
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  10. #70
    VBAX Regular
    Joined
    May 2015
    Posts
    33
    Location
    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

  11. #71
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,077
    Location
    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
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  12. #72
    VBAX Regular
    Joined
    May 2015
    Posts
    33
    Location
    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?
    Attached Files Attached Files

  13. #73
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,077
    Location
    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
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  14. #74
    VBAX Regular
    Joined
    May 2015
    Posts
    33
    Location
    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"

  15. #75
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,903
    Location
    Change all of FormulaResult to Formula1Result.

  16. #76
    VBAX Regular
    Joined
    May 2015
    Posts
    33
    Location
    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"

  17. #77
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,077
    Location
    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
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  18. #78
    VBAX Regular
    Joined
    May 2015
    Posts
    33
    Location
    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"

  19. #79
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,077
    Location
    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
    Last edited by SamT; 06-11-2015 at 12:10 PM.
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  20. #80
    VBAX Regular
    Joined
    May 2015
    Posts
    33
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •