How to Import multiple csv files into excel?

Question

Hello all,

in former excel versions, it was possible to select "Data-Import-Folder". I miss this option in excel 365. How can I import several csv into one sheet?

With the import csv option, it's not possible to multi select files. I've a lot files to import and i want import them all in one. Not 50 times going through the import process. Has anyone a idea?

Thanks and best regards

Andreas

Answer 1

Hello Davis,

thank you very much. I've found it in the meantime. It was a bit hided....

Answer 2

Hi.

My Name is David B.

Independent Advisor I'd like to help.

In this case, to import all csv files from a folder to a single sheet, you can use the following VBA code.

1. Enable a blank worksheet and press Alt + F11 keys to open Microsoft Visual Basic for Applications window.
2. Click box> Module and paste under VBA in the new Module window.

VBA: Import csv files from a folder into a single worksheet:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

Sub ImportCSVsWithReference()

'UpdatebyKutoolsforExcel20151214

Dim xSht  As Worksheet

Dim xWb As Workbook

Dim xStrPath As String

Dim xFileDialog As FileDialog

Dim xFile As String

On Error GoTo ErrHandler

Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)

xFileDialog.AllowMultiSelect = False

xFileDialog.Title = "Select a folder [Kutools for Excel]"

If xFileDialog.Show = -1 Then

    xStrPath = xFileDialog.SelectedItems(1)

End If

If xStrPath = "" Then Exit Sub

Set xSht = ThisWorkbook.ActiveSheet

If MsgBox("Clear the existing sheet before importing?", vbYesNo, "Kutools for Excel") = vbYes Then xSht.UsedRange.Clear

Application.ScreenUpdating = False

xFile = Dir(xStrPath & "\" & "\*.csv")

Do While xFile <> ""

    Set xWb = Workbooks.Open(xStrPath & "\" & xFile)

    Columns(1).Insert xlShiftToRight

    Columns(1).SpecialCells(xlBlanks).Value = ActiveSheet.Name

    ActiveSheet.UsedRange.Copy xSht.Range("A" & Rows.Count).End(xlUp).Offset(1)

    xWb.Close False

    xFile = Dir

Loop

Application.ScreenUpdating = True

Exit Sub

ErrHandler:

MsgBox "no files csv", , "Kutools for Excel"

End Sub

3. Press F5 key or click run to run the VBA, and a dialog box appears to select a folder from which you want to import all the csv files.
4. Click OK, and a dialog appears to remind you if you delete the contents of the active worksheet before importing, here I click Yes.

After clicking Yes, all csv files in the selected folder are imported to the current sheet and place data from Column A to the right.

Tips: If you want to place csv files horizontally on a worksheet, you can then use VBA.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

Sub ImportCSVsWithReferenceI()

'UpdatebyKutoolsforExcel20151214

Dim xSht  As Worksheet

Dim xWb As Workbook

Dim xStrPath As String

Dim xFileDialog As FileDialog

Dim xFile As String

Dim xCount As Long

On Error GoTo ErrHandler

Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)

xFileDialog.AllowMultiSelect = False

xFileDialog.Title = "Select a folder [Kutools for Excel]"

If xFileDialog.Show = -1 Then

    xStrPath = xFileDialog.SelectedItems(1)

End If

If xStrPath = "" Then Exit Sub

Set xSht = ThisWorkbook.ActiveSheet

If MsgBox("Clear the existing sheet before importing?", vbYesNo, "Kutools for Excel") = vbYes Then

    xSht.UsedRange.Clear

    xCount = 1

Else

    xCount = xSht.Cells(3, Columns.Count).End(xlToLeft).Column + 1

End If

Application.ScreenUpdating = False

xFile = Dir(xStrPath & "\" & "\*.csv")

Do While xFile <> ""

    Set xWb = Workbooks.Open(xStrPath & "\" & xFile)

    Rows(1).Insert xlShiftDown

    Range("A1") = ActiveSheet.Name

    ActiveSheet.UsedRange.Copy xSht.Cells(1, xCount)

    xWb.Close False

    xFile = Dir

    xCount = xSht.Cells(3, Columns.Count).End(xlToLeft).Column + 1

Loop

Application.ScreenUpdating = True

Exit Sub

ErrHandler:

MsgBox "no files csv", , "Kutools for Excel"

End Sub

Regards...

Answer 3

I'm glad your problem has been resolved, we are here to help.