none
Automated reading of multiple files RRS feed

  • Question

  • I have a number of .csv files in a folder that contain data that I would like to read into a worksheet. Is there a way that I can write a VBA macro to read the data from all of the files in that folder without having to provide the macro with a list of those files?

    Saturday, January 27, 2018 11:59 PM

Answers

  • Hi sdkorn,

    I have made a sample.  Reading each *.csv file in a folder and storing their data into one sheet (not different sheets).


    There are 3 csv files in a folder "C:\temp\CSV files"


    csv_1.csv
    :

    aaa,bbb,ccc,ddd,eee
    1,2,3,4,5


    csv_2.csv
    :

    uuu,vvv,www,xxx,yyy,zzz
    21,22,23,24,25,26

    csv_2.csv:

    USA,Canada,Japan,India,China, Austraria,Great Britain
    31,32,33,34,35,36,37


    [VBA]

    Private RowIdx As Long
    
    ' ---[Import CSV] button
    Private Sub btn_ImportCSV_Click()
        Dim csvFolder As String
        csvFolder = Range("A1").Value & "\"
        ' ---
        Dim FSO As Object
        Dim F As Object
        Set FSO = CreateObject("Scripting.FileSystemObject")
        ' ---
        RowIdx = 0
        For Each F In FSO.getfolder(csvFolder).Files
            Dim fileName As String
            fileName = csvFolder & "\" & F.Name
            'MsgBox "filename = " & fileName ' -- for debugging
            Call prc_ImportCSV(fileName)
        Next
        ' ---
        Set FSO = Nothing
        Set F = Nothing
        ' ---
        Sheets("CSV contents").Select
    End Sub
    
    ' --- Read a CSV file, store data into array, and put array data onto cell
    Private Sub prc_ImportCSV(ByVal fileName As String)
        Dim lineStr As String   ' -- buffer for 1 line
        Dim aryStr As Variant   ' -- array for 1 line
        ' ---
        Open fileName For Input As #1   ' -- open the specified CSV file
        ' --- read a file at the end of file
        Do While Not EOF(1)
            Line Input #1, lineStr  ' -- read 1 line
            aryStr = Split(lineStr, ",")    ' -- delimited by commma and store data into array
            ' --- (from A1) increase Column to the number of array elements, and set array data
            Sheets("CSV Contents").Range("A1").Resize(1, UBound(aryStr) + 1).Offset(RowIdx) = aryStr
            RowIdx = RowIdx + 1
        Loop
        ' ---
        Close #1    ' -- close the CSV file
    End Sub
    
    ' ---[Clear Contents] button
    Private Sub btn_ClearContents_Click()
        Sheets("CSV contents").Range("A1:Z9999").Value = ""
        MsgBox "sheet [CVS Contents] cleared !!"
    End Sub
    

    Regards,  


    Ashidacchi

    • Marked as answer by sdkorn Wednesday, January 31, 2018 12:47 AM
    Sunday, January 28, 2018 5:00 AM
  • Hi Stanley,

    I have shared my sample "Import_CSV.zip" via OneDrive.
    Please download and unzip it.

    [note] I've add a feature for choosing Comma or TAB as a delimiter in CSV file. So, if you are using comma as a delimiter in CSV files, please check the upper checkbox and [Import CSV].


    Regards,

    Ashidacchi


    • Edited by Ashidacchi Tuesday, January 30, 2018 4:13 AM
    • Marked as answer by sdkorn Wednesday, January 31, 2018 12:46 AM
    Tuesday, January 30, 2018 4:05 AM

All replies

  • Hi sdkorn,

    I would like to confirm:
    (1) what is a delimiter of these *.csv files?  Is it "commna"?
    (2) where should each *.csv file be read into? 
      e.g. if there are five *.csv files, they are read into one worksheet, or read into five different worksheets?

    Regards,

    Ashidacchi

    Sunday, January 28, 2018 3:45 AM
  • Hi sdkorn,

    I have made a sample.  Reading each *.csv file in a folder and storing their data into one sheet (not different sheets).


    There are 3 csv files in a folder "C:\temp\CSV files"


    csv_1.csv
    :

    aaa,bbb,ccc,ddd,eee
    1,2,3,4,5


    csv_2.csv
    :

    uuu,vvv,www,xxx,yyy,zzz
    21,22,23,24,25,26

    csv_2.csv:

    USA,Canada,Japan,India,China, Austraria,Great Britain
    31,32,33,34,35,36,37


    [VBA]

    Private RowIdx As Long
    
    ' ---[Import CSV] button
    Private Sub btn_ImportCSV_Click()
        Dim csvFolder As String
        csvFolder = Range("A1").Value & "\"
        ' ---
        Dim FSO As Object
        Dim F As Object
        Set FSO = CreateObject("Scripting.FileSystemObject")
        ' ---
        RowIdx = 0
        For Each F In FSO.getfolder(csvFolder).Files
            Dim fileName As String
            fileName = csvFolder & "\" & F.Name
            'MsgBox "filename = " & fileName ' -- for debugging
            Call prc_ImportCSV(fileName)
        Next
        ' ---
        Set FSO = Nothing
        Set F = Nothing
        ' ---
        Sheets("CSV contents").Select
    End Sub
    
    ' --- Read a CSV file, store data into array, and put array data onto cell
    Private Sub prc_ImportCSV(ByVal fileName As String)
        Dim lineStr As String   ' -- buffer for 1 line
        Dim aryStr As Variant   ' -- array for 1 line
        ' ---
        Open fileName For Input As #1   ' -- open the specified CSV file
        ' --- read a file at the end of file
        Do While Not EOF(1)
            Line Input #1, lineStr  ' -- read 1 line
            aryStr = Split(lineStr, ",")    ' -- delimited by commma and store data into array
            ' --- (from A1) increase Column to the number of array elements, and set array data
            Sheets("CSV Contents").Range("A1").Resize(1, UBound(aryStr) + 1).Offset(RowIdx) = aryStr
            RowIdx = RowIdx + 1
        Loop
        ' ---
        Close #1    ' -- close the CSV file
    End Sub
    
    ' ---[Clear Contents] button
    Private Sub btn_ClearContents_Click()
        Sheets("CSV contents").Range("A1:Z9999").Value = ""
        MsgBox "sheet [CVS Contents] cleared !!"
    End Sub
    

    Regards,  


    Ashidacchi

    • Marked as answer by sdkorn Wednesday, January 31, 2018 12:47 AM
    Sunday, January 28, 2018 5:00 AM
  • I did something like this for csv files produced by some measurement equipment.  Check the files.  Some files where in UTF8 format and some used a different end of line method.  I had to fix the files before processing them.
    Sunday, January 28, 2018 1:22 PM
  • Hi sdkorn,

    I provided a sample with code and screenshot yesterday. But I didn't hear from you.
    I hope you will send a reply.

    Regards,

    Ashidacchi

    Monday, January 29, 2018 1:19 AM
  • Hi Ashidacchi,

    Thanks for getting back to me so soon.

    Yes, the delimiter in the .csv files is a comma.

    The files are all in the folder called Training and are to all be read into one worksheet of workbook Solution.xlsm.

    Does that change the code you provided?

    It's not clear to me from your worksheet animation if the user has to click the "Import CSV" button for each file read in. I would like all of the files to be read in automatically without requiring any user intervention after the macro is executed.

    I look forward to you getting back to me.

    Stanley Korn

    Monday, January 29, 2018 8:21 PM
  • Hi Stanley,

    I cannot understand the meaning of "Does that change the code you provided?".
    What do you mean by that?

    My code in [Import CSV] button can read all csv files in a folder.  It does not require users to click the button many times, i.e. by one click of the button, all files will be read.

    Have you tried my code?

    Regards,

    Ashidacchi

    Monday, January 29, 2018 10:25 PM
  • Ashidacchi,

    I created the three <g class="gr_ gr_11 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="11" id="11">csv</g> files to duplicate what you did and copied the code you provided into a macro. When I executed the macro, I got a compile error on the statement Private RowIdx As Long. The error message was <g class="gr_ gr_12 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" data-gr-id="12" id="12">Invalid</g> attribute in Sub or Function. When I tried to send a screen image, I got the message:

    Social.msdn.microsoft.com says

    Body text cannot contain images or links until we are able to verify your account.

    - Stanley


    Tuesday, January 30, 2018 3:45 AM
  • Hi Stanley,

    Thank you for testing and explaining your issue.

    You cannot insert an image (and URL link) until you will give some contribution in this forum. So, I hope you will share your screenshot via cloud storage such as OneDrive, Dropbox, etc. Of course, it would be better to share your Excel file (instead of a screenshot) via cloud storage.

    Regards,

    P.S.
    If you write a post with some mail software, please use web browser and write in browser, instead of mail software. (for avoiding tags in your body text)

    Ashidacchi

    Tuesday, January 30, 2018 3:59 AM
  • Ashidacchi,

    The message I just sent appears to be garbled, perhaps because I copied parts of code. Let me try again.

    I created the three files to duplicate what you did and copied the code you provided into a macro. When I executed the macro, I got a compile error on the statement Private RowIdx As LongThe error message was Invalid attribute in Sub or FunctionWhen I tried to send a screen image, I got the message:

    Social.msdn.microsoft.com says

    Body text cannot contain images or links until we are able to verify your account.

    - Stanley

    Tuesday, January 30, 2018 4:00 AM
  • Hi Stanley,

    I have shared my sample "Import_CSV.zip" via OneDrive.
    Please download and unzip it.

    [note] I've add a feature for choosing Comma or TAB as a delimiter in CSV file. So, if you are using comma as a delimiter in CSV files, please check the upper checkbox and [Import CSV].


    Regards,

    Ashidacchi


    • Edited by Ashidacchi Tuesday, January 30, 2018 4:13 AM
    • Marked as answer by sdkorn Wednesday, January 31, 2018 12:46 AM
    Tuesday, January 30, 2018 4:05 AM
  • Hi Ashidacchi,

    When I made the appropriate change to the full path to the folder containing the csv files, I was able to recreate the data on the second worksheet by clicking on the Import CSV button. So it appears to be working properly. However, when I attempted to locate the macro code by clicking on the Developer tab and then on the Macro option, there were no macros listed. I did find the code when I clicked on the Visual Basic option. Is that code not part of a macro?

    If the workbook containing the VBA program that reads the CSV files is in the same folder as the folder containing the CSV files, is it possible to specify the CSV folder by just providing its name rather than listing the full path to it?

    Thank you for your assistance.

    Stanley

    Tuesday, January 30, 2018 5:24 AM
  • Hi Stanley,

    (1) my VBA code is in Sheet1.     

    (2) Yes. You can write folder name (with full path) in your code, directly.
      But I don't like to write folder name in VBA code, because it is not universal way, i.e. we must modify code, whenever folder name is changed. So, I put it on worksheet.

    Regards,

    Ashidacchi

    Tuesday, January 30, 2018 5:43 AM
  • Ashidacchi,

    I was wanting to set it up so that you could just write the csv folder name on the worksheet without the full path when the csv folder is in the same folder as the workbook. Is that possible?

    Stanley

    Tuesday, January 30, 2018 5:58 AM
  • Hi Stanley,

    Yes, it is possible.

    BTW, when you will let me go? Sorry, I feel tired a little.
    Would you close this thread and create a new thread?  This thread seems to be too long and need to long scrolling.

    Regards,

    Ashidacchi

    Tuesday, January 30, 2018 6:47 AM
  • Hi Ashidacchi,

    Thanks for all your help.

    It will take me some time to familiarize myself with your code so that I can adapt it to my application.

    Get some sleep.

    Stanley

    • Marked as answer by sdkorn Tuesday, January 30, 2018 9:09 AM
    • Unmarked as answer by sdkorn Wednesday, January 31, 2018 12:44 AM
    Tuesday, January 30, 2018 9:09 AM
  • Hi,

    Collecting points is not my hobby. But, to be honest, I was expecting you would mark my post as answer. I'm afraid you misunderstand the meaning of marking as answer.

    Good night (from Japan).

    Ashidacchi

    • Marked as answer by sdkorn Tuesday, January 30, 2018 8:02 PM
    • Unmarked as answer by sdkorn Wednesday, January 31, 2018 12:44 AM
    Tuesday, January 30, 2018 9:47 AM
  • Ashidacchi,

    I thought that had marked your post as answer when it was my own post that I had marked as answer. I apologize for the error. I have marked your last post as answer. Let me know if you post is now marked as answer or if there is something more that I need to do to accomplish that.

    Stanley

    Tuesday, January 30, 2018 8:14 PM
  • Stanley,

    Thank you for marking my post as answer.
    But I'm afraid your choice is not appropriate. I hope you will choose one or more than one post which really helped you. Why? A helpful post (marked as answer) will help others who have the same question. They will find answer easily, if post is marked as answer.

    Please do not hesitate about removing mark from my post and choose appropriate post. That will help others in the future.

    Regards,  

    Ashidacchi

    Tuesday, January 30, 2018 10:27 PM
  • Ashidacchi,

    I marked two of your most helpful posts as answers. Does this address your concerns?

    Stanley

    Wednesday, January 31, 2018 12:51 AM
  • Stanley,

    Thank you again. I'm very  satisfied with your marking.
    BTW, have your issues/questions been resolved?

    Regards,

    Ashidacchi

    Wednesday, January 31, 2018 12:55 AM
  • Ashidacchi,

    Thanks again for all of your help. You did answer the question I had. If I encounter other problems that I cannot resolve on my own, I will post them on a new thread.

    Stanley

    Wednesday, January 31, 2018 3:39 AM