Consulting

Page 2 of 6 FirstFirst 1 2 3 4 ... LastLast
Results 21 to 40 of 119

Thread: Process All CSV Files In SubFolders

  1. #21
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    Quote Originally Posted by SamT View Post
    Thanks, Kenneth,

    I spent hours analyzing snb's code. That's my excuse and I'm sticking to it.

    Yeah..... Sure you were.....


    You fell asleep again didn't you

    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  2. #22
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    shaddup, you
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #23
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    @KH

    Writing 10^6 results into the workbook wont' be that tedious if we use an array of that size

    redim kh(10^6,0)
    cells(1).resize(10^6+1)=kh

  4. #24
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Probably
    ReDim kh(10^5 ,3)
    What do you think of the difference in appearance between your and my versions of the same code?

    snb --
    SamT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #25
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    Here is my last revision of snb's code.

    I am still concerned about speed for M. I have seen this code run slow the 1st time but after that it ran fast. ADO might be another route but it often run's slow too.

    You will notice that I like to use Option Explicit so I added Dim accordingly. I also like to use early bound methods for the FSO object but I know many like the late bound method so I left it with the intent not to change snb's code too much which might confuse you. I also added the concept that Sam used where the full filename was trimmed to the basename.

    Option Explicit
    
    
    
    Sub M_snb3()
      Dim sn() As String, c00() As String, j As Long, jj As Long
      Dim sp() As String, y As Double
    
    
      sn = Filter(Split(CreateObject("wscript.shell").exec _
        ("cmd /c Dir ""X:\snb\maxxino\*.csv"" /b /s").stdout.readall, vbCrLf), ".")
        
      ReDim c00(1 To UBound(sn) + 1, 1 To 2) As String
        
      With CreateObject("scripting.filesystemobject")
        For j = 0 To UBound(sn)
          y = 0
          sp = Split(.opentextfile(sn(j)).readall, vbCrLf)
           
          For jj = 0 To UBound(sp) - 1
            sp(jj) = Log(Split(sp(jj), ",")(5)) / Log(10)
            If jj > 0 Then y = y + 100 * (sp(jj) - sp(jj - 1)) ^ 2
          Next jj
          'c00(j + 1, 1) = sn(j)                'CSV Full Filename
          c00(j + 1, 1) = .GetBasename(sn(j)) 'CSV Base Filename
          c00(j + 1, 2) = y                    'Result
        Next j
      End With
        
      Range("A1").Resize(UBound(c00), 2).Value = c00
      Columns("A:B").AutoFit
    End Sub

  6. #26
    VBAX Regular
    Joined
    May 2015
    Posts
    33
    Location
    Thanks very much to all of you guys for your effort!!

    The last code by Kenneth works best for me; I get the results the way I need them - in the column A the name of the .csv file, in the column B is the calculation, the only problem is, that in the column B all results are zero, thuscI suppose there is an error in the calculation part of the VBA code.

    Time is not an issue, I have these 50k files (not 500k) divided into 500 subfolders, thus I can run the code in the particular subfolder and it is done in a sec.
    I can repeat the code in all subdolders by myself, but if someone could even help me how to run the code through all the subfolders, I will be even more grateful

    SamT: each source .csv file has max 200 rows

    I really cherish your contribution, it's a huge help for me as I am in a time press to finish the diploma thesis, so I though that I could donate some smaller amount of my student money to your forum as a reward, if it is possible? For your time and effort.

  7. #27
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    No reward other than a thanks and marking a thread solved and maybe adding to a helper's reputation (lower left star) is needed.

    I suspect that your CSV files are not setup as we tested. I noted that you detailed some translation of periods to commas in your XLSX file but I did not see a need for that. I have attached the XLSM and 3 CSV files that I used for testing.
    Attached Files Attached Files

  8. #28
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    @KH

    the first time a cmd command is being run the 'command.com' has to be loaded. When loaded (the second time) it's fast as lightning.

  9. #29
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Quote Originally Posted by SamT View Post
    Probably
    ReDim kh(10^5 ,3)
    What do you think of the difference in appearance between your and my versions of the same code?

    snb --
    SamT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------




    What difference ?

  10. #30
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    snb's code and Kenneth's last code take the Log of Column F and divides it by Log(-10)

    My code (#16 Sub SamT_snb) and Kenneth's corrected version of that (#19 Sub KenH_SamT_snb) doesn't divide by Log(-10) and puts the formula result(s) in columns B & C and puts the name in Column D. You can easily edit the locations at the bottom of the code in Ken's version.

    There is a space in Sub KenH_SamT_snb to add more formulas to be run on those files at the same time.

    Point the code at the main folder and it will process all files in all the sub folders.

    Please Private Message the owner of the Forum, Link: Jacob Hilderbrand, about contributing.
    Last edited by SamT; 05-31-2015 at 02:37 PM.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  11. #31
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    The /s will take care that all subfolders will be looked into too. So there's nothing more to add to the code to involve all subfolders.

  12. #32
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I'll edit my poast. Toast? Poasted eggs? Post Toasted
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  13. #33
    VBAX Regular
    Joined
    May 2015
    Posts
    33
    Location
    Kenneth

    I believe that my csv files are setup as I described, the replace of dot with comma is for columns C, D, E and F - these columns are prices and they have a dot to separate integer from decimals (but I want to replace dot with commas for calculation purposes)
    Maybe the problem could be that I did not describe properly that text to columns should be done via comma - thus period are in column A and B (not included in the further calculation)

    and as SamT noted - I do not want to divide log of Column F by Log(-10), the sequence of calculation is as I wrote in the sample

  14. #34
    VBAX Regular
    Joined
    May 2015
    Posts
    33
    Location
    SamT

    I tried your code (nr. 16 Sub SamT_snb) and Kenneth's corrected version of that (nr. 19 Sub KenH_SamT_snb) and actually it is more understandable for me as it is more itemized,
    but unfortunately these codes did not work for me:

    #16 gives me an error: Run-time error'-2147024894 (80070002)': The system cannot find the file specified
    eventhough I put the file path into the line 22

    and #19 runs without any error, but there is no output - no message "IT Works! Stopping Sub"

  15. #35
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Yes, ignore #16. It has errors built in. # 19 does not have those errors, but may have others.

    in this part
    If Fn >= 10 Then 
                    MsgBox "IT Works! Stopping Sub" 
                    Exit Sub 
                End If
    Change Fn >= 10 to Fn >= 1

    This will stop the code at the end of the first file. it should print some thing in the first row on the worksheet.

    As each of your CSV files are ~200 lines. Please attach two here so we can have a meaningful test on our computers. You can zip them into one if you please.

    So not despair. This is typical of programming. it is just slower with remote programming.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  16. #36
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    After we get the code to work, and your sheet is filled with numbers using dots as decimal separaters, select the column(s) and press Ctrl+H. in that dialog's "Find What" box, type a dot. In the "Replace With" Box, type a comma. The find and Replace Dialog will change them all in a short time.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  17. #37
    VBAX Regular
    Joined
    May 2015
    Posts
    33
    Location
    allstocks_20130102.zip

    It did not show anything either =/

    I attach two csv files.

  18. #38
    VBAX Regular
    Joined
    May 2015
    Posts
    33
    Location
    ye, I know Ctrl+H, but I wanted to include this Find&Replace into the code, as the calculation can be made only with commas, thus it somehow has to be in the code or I am not sure what you mean

  19. #39
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    Open one of your CSV files in Notepad and you will see the problem. It is essentially a one record CSV file. If this is truly the case, more code is needed to split the file into the proper number of columns with added vbCrLf characters to demarcate the "records".

  20. #40
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I wanted to include this Find&Replace into the code, as the calculation can be made only with commas
    Ah Ha! Before we do any more work here, will you, on your computer, go the the Start Menu, Settings, ControlPanel, Regional and Language Settings, Regional Options Tab, Customize button, and change the decimal symbol on your computer to a dot, then run the #19 code?
    Please.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

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