Drag & Drop csv file into Excel

I need to import numerous .csv files into a single Excel spreadsheet.

Currently I open the .csv, press Alt & A to select all data and then copy / paste the date into the spreadsheet

Is there a method to drag & drop .csv files into a spreadsheet?

Thanks

Perhaps a VBA help...

'CSVs To Sheets
I have a macro that may be "ready to use" for importing all CSV files in a folder into a single Excel sheet, each CSV filename will be listed on the sheet next to the data that it came from.

CSVs to 1 Sheet

How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Edit the parts in red as needed for your workbook/CSVs
6. Get out of VBA (Press Alt+Q)
7. Save as a macro-enabled workbook

The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.

Hi Jerry

I have looked at your macro and it is great.

However I want to place the new file to the right, rather than below.
So the new file will always be placed in row 2, with the file name in row 1 (one cell only)
Can you assist?

I've added a "Part 2" to the published page for your scenario. Try that version.

'CSVs To One Sheet (Side By Side)
I have a macro that may be "ready to use" for importing all CSV files in a folder into a single Excel sheet, each CSV filename will be listed above the data, the data imported into adjacent columns.

CSVs to 1 Sheet - Part 2

Thanks for the feedback!

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

You must religiously take OUT the selecting and activating you get from recorded code, merging all the rows down into fully addressed direct commands.

Try this:

    Do While Len(fCSV) > 0
      'open a CSV file
        Set wbCSV = Workbooks.Open(fPath & fCSV)
      'insert row 1 and add CSV name
        Rows(1).Insert xlShiftDown
        Range("E1") = ActiveSheet.Name
               
        KountRows = Application.CountA(Range("A:A")) + 1
        
        Range("D:D").Insert xlShiftToRight
        With Range("D3:D" & KountRows)
            .FormulaR1C1 = "=RC[-2]+RC[-1]"
            .Value = .Value
            .NumberFormat = "dd/mm/yyyy hh:mm:ss"
        End With
        
        Range("A:C").Delete xlShiftToLeft
        Range("A2") = "DateTime"
      'copy date into master sheet and close source file
        Range("A1:A" & KountRows).EntireRow.Copy wsMstr.Cells(1, NextCol)
        wbCSV.Close False
      'ready next CSV
        fCSV = Dir
        NextCol = wsMstr.Cells(3, Columns.Count).End(xlToLeft).Column + 3
    Loop

When you DEBUG and that line is highlighted, hover your mouse over KountRows and NextCol, what the values?

You can't paste data into column 0. Doesn't exist.

The original full macro set the "NextCol" earlier the code than what you shared above. What did you change/omit? That variable must be set prior to entering the the DO LOOP section. Let me see your full edited version.

Originally Posted by JBeaucaire

Let me see your full edited version.

Hmmm..... still waiting.

Sub ImportCSVsWithReference()
'Author:    Jerry Beaucaire
'Date:      11/3/2011
'Summary:   Import all CSV files from a folder into a single sheet
'           adding a field in row 1 for listing the CSV filenames

Dim wbCSV   As Workbook
Dim wsMstr  As Worksheet:   Set wsMstr = ThisWorkbook.Sheets("Sheet1")
Dim fPath   As String:      fPath = "C:\Users\Tim\Documents\20111026\"    'path to CSV files, include the final \
Dim fCSV    As String
Dim NextCol As Long
Dim KountRows As Integer

If MsgBox("Clear the existing MasterCSV sheet before importing?", _
    vbYesNo, "Clear?") = vbYes Then
    wsMstr.UsedRange.Clear
    NextCol = 1
    Else
    extCol = wsMstr.Cells(3, Columns.Count).End(xlToLeft).Column + 3
End If

Application.ScreenUpdating = False  'speed up macro

fCSV = Dir(fPath & "*.csv")         'start the CSV file listing

Do While Len(fCSV) > 0
      'open a CSV file
        Set wbCSV = Workbooks.Open(fPath & fCSV)
      'insert row 1 and add CSV name
        Rows(1).Insert xlShiftDown
        Range("D1") = ActiveSheet.Name
               
        KountRows = Application.CountA(Range("A:A")) + 1
        
        Range("D:D").Insert xlShiftToRight
        With Range("D3:D" & KountRows)
            .FormulaR1C1 = "=RC[-2]+RC[-1]"
            .Value = .Value
            .NumberFormat = "dd/mm/yyyy hh:mm:ss"
        End With
        
        Range("A:C").Delete xlShiftToLeft
        Range("A2") = "DateTime"
      'copy date into master sheet and close source file
        Range("A1:A" & KountRows).EntireRow.Copy wsMstr.Cells(1, NextCol)
        wbCSV.Close False
      'ready next CSV
        fCSV = Dir
        NextCol = wsMstr.Cells(3, Columns.Count).End(xlToLeft).Column + 3
    Loop

Application.ScreenUpdating = True
End Sub

Try:

Dim KountRows As Long
.
.
.
KountRows = Range("A" & Rows.Count).End(xlup).Row + 1

If KountRows = 0 then this will never work. Why is your data in the opened workbook showing nothing in column A?

You had messed up the copy/paste of the "else" part of the first section. Also, I changed that copy/paste command to only copy the number of columns needed instead of trying to copy the entire row, that seems to have resolved it.

Option Explicit

Sub ImportCSVsWithReference()
'Author:    Jerry Beaucaire
'Date:      11/3/2011
'Summary:   Import all CSV files from a folder into a single sheet
'           adding a field in row 1 for listing the CSV filenames

Dim wbCSV   As Workbook
Dim wsMstr  As Worksheet
Dim fPath   As String
Dim fCSV    As String
Dim NextCol As Long
Dim LastRow As Long

Set wsMstr = ThisWorkbook.Sheets("Sheet1")
fPath = "C:\Users\Tim\Documents\20111026\"      'path to CSV files, include the final \

If MsgBox("Clear the existing MasterCSV sheet before importing?", _
    vbYesNo, "Clear?") = vbYes Then
    wsMstr.UsedRange.Clear
    NextCol = 1
Else
    NextCol = wsMstr.Cells(3, Columns.Count).End(xlToLeft).Column + 3
End If

Application.ScreenUpdating = False  'speed up macro

fCSV = Dir(fPath & "*.csv")         'start the CSV file listing

Do While Len(fCSV) > 0
    Set wbCSV = Workbooks.Open(fPath & fCSV)    'open a CSV file
    Rows(1).Insert xlShiftDown                  'insert row 1 and add CSV name
    Range("D1") = ActiveSheet.Name
           
    LastRow = Range("A" & Rows.Count).End(xlUp).Row + 1
    Range("D:D").Insert xlShiftToRight
    With Range("D3:D" & LastRow)                'merge date/time into a single column
        .FormulaR1C1 = "=RC[-2]+RC[-1]"
        .Value = .Value
        .NumberFormat = "dd/mm/yyyy hh:mm:ss"
    End With
    
    Range("A:C").Delete xlShiftToLeft           'delete old date/time columns
    Range("A2") = "DateTime"
    
    Range("A1:F" & LastRow).Copy wsMstr.Cells(1, NextCol)     'copy date into master sheet
    wbCSV.Close False                           'close resource file
    fCSV = Dir                                  'ready next CSV
    NextCol = wsMstr.Cells(3, Columns.Count).End(xlToLeft).Column + 3
Loop

Application.ScreenUpdating = True
End Sub

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.