0

I have many data files that I need to consolidate into a single file. The source data files are simple csv text files with a single column. I need to take maybe a few hundred of these csv files and copy each of their column data over to a single excel file. So it would be something like this:

Point to a directory that contains the csv files. For all csv files in that directory, copy their single column data into a single excel file.

So if I had CSV files A, B, C, D, E, the resulting excel file would have 5 columns in it.

I know basic macros to handle where I want data to go from on file to another, but what I could use some assistance on is all the file handling in the first place.

Think I would have to be able to specify the source directory, get a count/list of all the csv files and then loop through the filename list. Inside the loop would be the copying of the column data.

So basically my hurdle here is the handling of all the files with a macro. Once I can do that, the transfer of data in the loop should be straightforward. Thanks for any ideas.

I can write a macro to transfer data from one csv to the excel file one at a time but I have to do this a couple of hundred times. Just need to figure out how to loop through all the files.

Improve this question
4
  • You could look at using the FileSystemObject learn.microsoft.com/en-us/office/vba/language/reference/… Commented Oct 16 at 15:30
  • Maybe this will help Loop through files in a folder Commented Oct 16 at 15:37
  • See ActiveSheet.QueryTables.Add("TEXT;" & xFileName, Range(xAddress)) Commented Oct 16 at 15:39
  • One way that you might be able to do it without it becoming too complicated is to record a macro whilst manually opening a csv file manually select column a and copy it to the master worksheet and hope that it records enough detail to give you something to start from. The simplest way will be to open each csv file in turn and copy column 1 to the appropriate column in your consolidated worksheet. This won't be the most efficient way to do it but it probably is the easiest for a beginner to get working. Commented Oct 16 at 15:56

1 Answer 1

Reset to default
1 
Option Explicit

Sub ImportCSV()
   
    Dim wbCSV As Workbook, wbAll As Workbook
    Dim wsCSV As Worksheet, rng As Range
    Dim sFolder As String, sFile As String, n As Long
    
    'Opens the folder picker dialog to allow user selection
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Please select a folder"
        .Show
        .AllowMultiSelect = False
        If .SelectedItems.Count = 0 Then 'If no folder is selected, abort
            MsgBox "You did not select a folder"
            Exit Sub
        End If
        sFolder = .SelectedItems(1) & "\" 'Assign selected folder to MyFolder
    End With
    
    ' create new workbook for result
    Set wbAll = Workbooks.Add(1)
    
    'Loop through all files in a folder until DIR cannot find anymore
    Application.ScreenUpdating = False
    sFile = Dir(sFolder & "*.csv")
    Do While sFile <> ""
        
        Debug.Print sFolder, sFile
        Set wbCSV = Workbooks.Open(sFolder & sFile, False, False)
        Set wsCSV = wbCSV.Sheets(1)
        Set rng = wsCSV.UsedRange
        
        ' copy data to column, first row as filename
        n = n + 1
        wbAll.Sheets(1).Cells(1, n) = sFile
        rng.Copy wbAll.Sheets(1).Cells(2, n)
        
        ' close csv file
        wbCSV.Close SaveChanges:=False
        sFile = Dir 'DIR gets the next file in the folder
 
    Loop
    Application.ScreenUpdating = True
    MsgBox n & " files imported from " & sFolder, vbInformation

End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.