Import Multiple Text Files into Excel - beginner advice

I need some hand holding please. Zero experiences with VBA - step by step appreciated by me and the hordes of newbies...

I need to import multiple text files into an Excel spreadsheet.

the files are simple text, single line, single column no parsing needed.

I want to import multiple files into a column in a worksheet

folder = /filefolder/
files = textfile-01.txt
textfile-02.txt through textfile-250.txt

I want textfile-01.txt to be in C1
I want textfile-02.txt to be in C2 etc.

I got as far as ALT+F11 opens VBA editor....

thanks in advance

A Code Variation

Hi Manny,

Many thanks for the code. I have a little variation and wonder if you could help:

In my case, I would like for the title of the excel file to be on a Cell C1 while the content of that text file in in the adjoining Cell C2 (ie not spread across the sheet)

Thanks for your anticipated support

A Code Variation

Hi,

What exactly you want to achieve?

Something like this:

C1 -> file1.txt
C2 -> line1
C3 -> line2
C4 -> file2.txt
C5 -> line1
C6 -> line2
...

Make the following changes into the subroutine (bolded lines):

...

For Each oFilePath In oLoopFolder.Files
  Set oFile = oFileSystem.OpenTextFile(oFilePath)

  ActiveSheet.Cells(RowN, ColN).Value = oFilePath.Name
  RowN = RowN + 1

  With oFile
    Do Until .AtEndOfStream
      ActiveSheet.Cells(RowN, ColN).Value = .ReadLine
      RowN = RowN + 1
    Loop

    .Close
  End With
Next oFilePath

...

Or you need something like this one:

C1 -> file1.txt
C2 -> line1
          line2
C3 -> file2.txt
C4 -> line1
          line2
...

Make the following changes into the subroutine (bolded lines):

...

For Each oFilePath In oLoopFolder.Files
  Set oFile = oFileSystem.OpenTextFile(oFilePath)

  ActiveSheet.Cells(RowN, ColN).Value = oFilePath.Name
  RowN = RowN + 1

  With oFile
    ActiveSheet.Cells(RowN, ColN).Value = .ReadAll
    RowN = RowN + 1
    .Close

  End With
Next oFilePath

...

If you need something different, please share more details.

Best regards.

Nick's picture

you owe Manny one massive

RapidAsia... you owe Manny one massive thanks !

RE: Import Multiple Text Files into Excel

Hi, rapidasia,

Well, you found the VBE. Next step is to insert a module into your project. Paste the following macro into that module:

 

' ************************* ' ************************* '

Sub ImportDataFromTxtFiles()

Application.ScreenUpdating = False

Dim oFileDialog As FileDialog
Dim LoopFolderPath As String
Dim oFileSystem As FileSystemObject
Dim oLoopFolder As Folder
Dim oFilePath As File
Dim oFile As TextStream
Dim RowN As Long
Dim ColN As Long

On Error GoTo ERROR_HANDLER

Set oFileDialog = Application.FileDialog(msoFileDialogFolderPicker)

RowN = 1
ColN = 3 ' Column C

With oFileDialog
    If .Show Then
        ActiveSheet.Columns(ColN).Cells.Clear
       
        LoopFolderPath = .SelectedItems(1) & "\"
       
        Set oFileSystem = CreateObject("Scripting.FileSystemObject")
        Set oLoopFolder = oFileSystem.GetFolder(LoopFolderPath)
       
        For Each oFilePath In oLoopFolder.Files
            Set oFile = oFileSystem.OpenTextFile(oFilePath)
           
            With oFile
                Do Until .AtEndOfStream
                    ActiveSheet.Cells(RowN, ColN).Value = .ReadLine
                    RowN = RowN + 1
                Loop
               
                .Close
            End With
        Next oFilePath
    End If
End With

EXIT_SUB:
    Set oFilePath = Nothing
    Set oLoopFolder = Nothing
    Set oFileSystem = Nothing
    Set oFileDialog = Nothing
   
    Application.ScreenUpdating = True
   
    Exit Sub

ERROR_HANDLER:
    ' Some code for error handling
    Err.Clear
    GoTo EXIT_SUB

End Sub

' ************************* ' ************************* '

 

To run a macro go to:

 - in Excel 2003: Tools -> Macro -> Macros...

 - in Excel 2007: Ribbon -> View -> Macros

Select ImportDataFromTxtFiles macro if it is not and press Run.

What macro does after is run:

 - A window to browse your folder with the text files is opened.

 - Clear all data in active sheet column C.

 - Reads each file and insert data into active sheet column C. If there is more than one line in the file, it will be inserted too in the next cell of column C.

 

Best regards.

Hi Manny - I'm getting a

Hi Manny -

I'm getting a compile error
User-defined type not defined

and it is highlighting
oFileSystem As FileSystemObject

Compile error

Hi, rapidasia,

Please excuse me, I forget to mention something. Go to VBE and from Tools -> References... find Microsoft Scripting Runtime and select it, then press OK button.

 

Best regards.

Thanks - like the forum title

Thanks - like the forum title says... excel experts !

I have a related/addendum to this question:
if I have multiple lines in the text file, how can I get each new line to go into a column? line 1 = C, line 2 = D etc

Modifications to meet new requirements

Remove this line of code:

ColN = 3 ' Column C

 

Then replace this part of code:

With oFileDialog

 ...

End With

 

with this:

With oFileDialog
    If .Show Then
        ActiveSheet.Cells.Clear
       
        LoopFolderPath = .SelectedItems(1) & "\"
       
        Set oFileSystem = CreateObject("Scripting.FileSystemObject")
        Set oLoopFolder = oFileSystem.GetFolder(LoopFolderPath)
       
        For Each oFilePath In oLoopFolder.Files
            Set oFile = oFileSystem.OpenTextFile(oFilePath)
           
            With oFile
                ColN = 3 ' To start from column C
                Do Until .AtEndOfStream
                    ActiveSheet.Cells(RowN, ColN).Value = .ReadLine
                    ColN = ColN + 1
                Loop
               
                RowN = RowN + 1
               
                .Close
            End With
        Next oFilePath
    End If
End With

awesome ! really, I mean

awesome !

really, I mean it

great power comes with knowledge, thank you for sharing

thanks

Thanks for the great response.

But I should also say, that your response is clearly not just for my benefit, but for all the folks out there searching for solutions like/similar to this. Then even more thanks to you are due.