How to import multiple csv files in one excel spread sheet

Question

Hi,

I have hundreds of csv files that contain data which i need to have in one excel sheet. I know I can insert one at a time but i am looking for an easier way. 

Details:

1. each csv file has two columns of data
2. i only need the data from the second column, the first column in each csv is identical in all files 
3. all data should be in one file in one sheet
4. all csv files have different names and are placed in one folder

I would greatly appreciate the help.

Thanks you

Answer 1

Try this:

Sub GetFromCSVs()

  Dim WB As Workbook

  Dim R As Range

  Dim bFirst As Boolean

  Dim stFile As String

  Dim stPath As String

  stPath = "C:\Temp\CSVs" ' change the path to suit

  stFile = Dir(stPath & "*.csv")

  bFirst = True

  Set R = Workbooks.Add(xlWorksheet).Sheets(1).Range("A1")

  Do Until stFile = ""

    Set WB = Workbooks.Open(stPath & stFile, ReadOnly:=True)

    If bFirst Then

      WB.Sheets(1).Range("A1").CurrentRegion.Copy Destination:=R

      Set R = R.Offset(, 2)

      bFirst = False

    Else

      WB.Sheets(1).Range("A1").CurrentRegion.Columns(2).Copy Destination:=R

      Set R = R.Offset(, 1)

    End If

    WB.Close saveChanges:=False

    stFile = Dir()  ' next file

  Loop

End Sub

Answer 2

Hi,

Have a look to Power Query (I don't know if this is available in Office 365).

Answer 3

I tried this but i get a compile error.

Please note that I am very unfamiliar with both visual basic and scripting.

Answer 4

That's because you didn't copy the final line which says

End Sub

(!)

Answer 5

Ok, I got past the error and the macro seemed to run, i saw it opened systematically all csvs in the folder but at the end I got was one excel sheet with only the data from the first csv file. Also that one data set from the csv which is normally separated in two columns was now merged in one column.