Import multiple text files, each to a new column on one worksheet?

[melpa]

I have multiple text files in one folder.
I want to import them all onto one worksheet, with the entire contents of each text file in its own column.
(So, text file 1 goes in Column A, text file 2 goes in Column B, etc.)

If it could be updated each time I add a new text file, all the better, but that is not essential.

Please bear in mind, I am a relative beginner.

Thanks in advance.

 

[melpa]

Having read the Guidelines for Forum Use, perhaps I should add:
1/ I am Using Excel 2010.
2/ I have tried using Power Query, but I can't make it do what I am trying to achieve. Instead, all the text files end up in one column.

I have seen the magic that can be done by VBA code, but I cannot write my own yet.
Ideally I was hoping someone could provide me with code I could use for this purpose.
I'd be grateful for any help.

If I have left anything out, please let me know.

Thanks again.

 

[melpa]

I'm a little further along in my search for a solution...

I found a code elsewhere, posted by someone who found it somewhere else, so I can't credit the author.

The code copies all the text files in one folder into a single column, so it's still not what I want.

However I'm sure there's someone out there who will know how to add a line or two to it, so that at the end of each text file it starts in a new column rather than on the next row. (I've played with it a little, but I really don't know what I'm doing.)

Here is the code:

<code>Sub ReadFilesIntoActiveSheet()
    Dim fso As FileSystemObject
    Dim folder As folder
    Dim file As file
    Dim FileText As TextStream
    Dim TextLine As String
    Dim Items() As String
    Dim i As Long
    Dim cl As Range

    ' Get a FileSystem object
    Set fso = New FileSystemObject

    ' get the directory you want
    Set folder = fso.GetFolder("D:\YourDirectory\")  

    ' set the starting point to write the data to
    Set cl = ActiveSheet.Cells(1, 1)

    ' Loop thru all files in the folder
    For Each file In folder.Files
        ' Open the file
        Set FileText = file.OpenAsTextStream(ForReading)

        ' Read the file one line at a time
        Do While Not FileText.AtEndOfStream
            TextLine = FileText.ReadLine

            ' Parse the line into | delimited pieces
            Items = Split(TextLine, "|")

            ' Put data on one row in active sheet
            For i = 0 To UBound(Items)
                cl.Offset(0, i).Value = Items(i)
            Next

            ' Move to next row
            Set cl = cl.Offset(1, 0)
        Loop

        ' Clean up
        FileText.Close
    Next file

    Set FileText = Nothing
    Set file = Nothing
    Set folder = Nothing
    Set fso = Nothing

End Sub

Cheers
</code>

 

[Fishboy]

I'm a little further along in my search for a solution...

I found a code elsewhere, posted by someone who found it somewhere else, so I can't credit the author.

The code copies all the text files in one folder into a single column, so it's still not what I want.

However I'm sure there's someone out there who will know how to add a line or two to it, so that at the end of each text file it starts in a new column rather than on the next row. (I've played with it a little, but I really don't know what I'm doing.)

Here is the code:

<code>Sub ReadFilesIntoActiveSheet()
    Dim fso As FileSystemObject
    Dim folder As folder
    Dim file As file
    Dim FileText As TextStream
    Dim TextLine As String
    Dim Items() As String
    Dim i As Long
    Dim cl As Range

    ' Get a FileSystem object
    Set fso = New FileSystemObject

    ' get the directory you want
    Set folder = fso.GetFolder("D:\YourDirectory\")  

    ' set the starting point to write the data to
    Set cl = ActiveSheet.Cells(1, 1)

    ' Loop thru all files in the folder
    For Each file In folder.Files
        ' Open the file
        Set FileText = file.OpenAsTextStream(ForReading)

        ' Read the file one line at a time
        Do While Not FileText.AtEndOfStream
            TextLine = FileText.ReadLine

            ' Parse the line into | delimited pieces
            Items = Split(TextLine, "|")

            ' Put data on one row in active sheet
            For i = 0 To UBound(Items)
                cl.Offset(0, i).Value = Items(i)
            Next

            ' Move to next row
            Set cl = cl.Offset(1, 0)
        Loop

        ' Clean up
        FileText.Close
    Next file

    Set FileText = Nothing
    Set file = Nothing
    Set folder = Nothing
    Set fso = Nothing

End Sub

Cheers
</code>

I could not get that to even work, let alone amend it as described. In the end I wrote something entirely new instead. How does this suit you? You will need to amend the bold red part to correct reflect the folder containing the text files:

Sub LoopThroughTextFiles()
' Defines variables
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim Text As String
Dim Textline As String
Dim LastCol As Long


' Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False


' Defines LastCol as the last column of data based on row 1
LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
' Sets the folder containing the text files
myPath = "C:\TestFolder\Texts" & "\"


' Target File Extension (must include wildcard "*")
myExtension = "*.txt"


' Target Path with Ending Extention
myFile = Dir(myPath & myExtension)


' Loop through each text file in folder
Do While myFile <> ""
    ' Sets variable "Text" as blank
    Text = ""
    ' Set variable equal to opened text file
    Open myPath & myFile For Input As #1
    ' Do until the last line of the text file
    Do Until EOF(1)
        ' Add each line of the text file to variable "Text"
        Line Input #1, Textline
        Text = Text & Textline
    Loop
    ' Close the text file
    Close #1
    ' Update row 1 of the current last column with the content of variable "Text"
    Cells(1, LastCol).Value = Text
    ' Increase LastCol by 1 to account for the new data
    LastCol = LastCol + 1
    ' Get next text file name
    myFile = Dir
Loop


' Message Box when tasks are completed
MsgBox "Task Complete!"


ResetSettings:
' Reset Macro Optimization Settings
Application.EnableEvents = True
Application.ScreenUpdating = True


End Sub

 

[melpa]

Thanks Fishboy,

I'm grateful you've taken the time to help me out.

I don't know what happened to the code I posted. It was working for me. Perhaps I copied it in badly. Anyway, I was just grasping at straws there...

I tried out the code you wrote for me. It needs some tweaking, if you would be so kind.

Firstly, all that is getting imported is the first line of each text file. I'm wondering if that could be because the next line is blank. In fact, there are many blank lines in all the text files.

Secondly, but less importantly, when the message box "Task complete!" pops up, no data is visible on the worksheet yet. After I click OK in the message box, that's when it appears.
If it's not too big an ask, could you make it so that the message box appears after the data has been imported?

As far as I'm concerned you're working magic for me. I wouldn't have a clue how to fix these things for myself... (yet)

So, thanks!
Cheers

 

[melpa]

Correction: Two blank lines after the first line. Not sure how consistent that's going to be. There are hundreds of them!

 

[Fishboy]

Thanks Fishboy,

I'm grateful you've taken the time to help me out.

I don't know what happened to the code I posted. It was working for me. Perhaps I copied it in badly. Anyway, I was just grasping at straws there...

I tried out the code you wrote for me. It needs some tweaking, if you would be so kind.

Firstly, all that is getting imported is the first line of each text file. I'm wondering if that could be because the next line is blank. In fact, there are many blank lines in all the text files.

Secondly, but less importantly, when the message box "Task complete!" pops up, no data is visible on the worksheet yet. After I click OK in the message box, that's when it appears.
If it's not too big an ask, could you make it so that the message box appears after the data has been imported?

As far as I'm concerned you're working magic for me. I wouldn't have a clue how to fix these things for myself... (yet)

So, thanks!
Cheers

What it was actually doing was combining every line from the text file into a single cell, so A1 was the first text file in its entirety, B1 was the second text file in its entirety and so on.

If you need it so each line from the text file shows on it's own row (so a 10 line text file would go from row 1 to row 10), then I believe the tweak I have just made will resolve that issue. My changes have been highlighted in bold red.

I have also moved the message box til after the screen updates, although for the record it had "imported" the data already but screen updating was set to FALSE meaning the screen doesn't flicker. Either way, the message box now appears when you want it to instead:

Sub LoopThroughTextFiles()
' Defines variables
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim Text As String
Dim Textline As String
Dim LastCol As Long
Dim RowCount As Long


' Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False


' Defines LastCol as the last column of data based on row 1
LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
' Sets the folder containing the text files
myPath = "C:\TestFolder\Texts" & "\"


' Target File Extension (must include wildcard "*")
myExtension = "*.txt"


' Target Path with Ending Extention
myFile = Dir(myPath & myExtension)


' Loop through each text file in folder
Do While myFile <> ""
    ' Sets variable "RowCount" To 1
    RowCount = 1
    ' Sets variable "Text" as blank
    Text = ""
    ' Set variable equal to opened text file
    Open myPath & myFile For Input As #1
    ' Do until the last line of the text file
    Do Until EOF(1)
        ' Add each line of the text file to variable "Text"
        Line Input #1, Textline
        Text = Textline
        ' Update RowCount row of the current last column with the content of variable "Text"
        Cells(RowCount, LastCol).Value = Text
        ' Increase RowCount by 1
        RowCount = RowCount + 1
    Loop
    ' Close the text file
    Close #1
    ' Increase LastCol by 1 to account for the new data
    LastCol = LastCol + 1
    ' Get next text file name
    myFile = Dir
Loop


ResetSettings:
' Reset Macro Optimization Settings
Application.EnableEvents = True
Application.ScreenUpdating = True

' Message Box when tasks are completed
MsgBox "Task Complete!"


End Sub

 

[melpa]

That's brilliant! It works perfectly.


I see what you mean now about the whole file being in one cell in the first version - I didn't think to expand the cell.

And I appreciate that you showed your alterations in red, it will help me in my attempt to learn from the code you've made to meet my goals.

I'm curious - and a bit confused - about what you said about "screen updating was set to FALSE meaning the screen doesn't flicker";
a) it looks to me like screen updating is still set to FALSE and
b) no screen flicker sounds like a good thing, I'll be looking to see if I can witness the difference when I run the two codes.

Final (non vital) query: Why do I get that blue wheel until I click OK? Is that normal? (It's not affecting anything)


Anyway, you've already completely resolved my impasse. I'm really very grateful. This stuff is so exciting! Thank you very much.

Cheers


P.S. Regarding the code I originally posted, just in case it's useful to someone else - I did copy it in correctly. There was an instruction in the post to - "select the Tools\References menu. In the References dialog, select 'Microsoft Scripting Runtime'" (inside the Visual Basic editor). I found the original author (Chris Neilsen). Here's the link: Can I import multiple text files into one excel sheet? - Stack Overflow

 

[Fishboy]

That's brilliant! It works perfectly.


I see what you mean now about the whole file being in one cell in the first version - I didn't think to expand the cell.

And I appreciate that you showed your alterations in red, it will help me in my attempt to learn from the code you've made to meet my goals.

Happy to help mate. I prefer adding in comments so that anyone reading can at least try to get to grips with what the code is doing. Highlighting my changes in red also allows me to point what has been amended to try and fix something.

I'm curious - and a bit confused - about what you said about "screen updating was set to FALSE meaning the screen doesn't flicker";
a) it looks to me like screen updating is still set to FALSE and
b) no screen flicker sounds like a good thing, I'll be looking to see if I can witness the difference when I run the two codes

Screen "flicker" is just a term used to describe what happens if VBA is making a lot of changes on the screen quickly. Also in code where multiple workbooks or sheets are being opened or selected the screen would normally be flicking between them as the code pans out. Setting Application.ScreenUpdating = False means that all the changes occur "behind the scenes" and your screen updates with the final values once Application.ScreenUpdating = True once again. Bear in mind though, if you plan on disabling screen updating at the start of a macro, remember to turn it back on again at the end or sometimes Excel looks like it is not responding when in fact it is just your screen not updating.

Final (non vital) query: Why do I get that blue wheel until I click OK? Is that normal? (It's not affecting anything)

That is the "Windows is thinking" cursor icon, and until you hit the OK button then technically the macro is still running.

P.S. Regarding the code I originally posted, just in case it's useful to someone else - I did copy it in correctly. There was an instruction in the post to - "select the Tools\References menu. In the References dialog, select 'Microsoft Scripting Runtime'" (inside the Visual Basic editor). I found the original author (Chris Neilsen). Here's the link: Can I import multiple text files into one excel sheet? - Stack Overflow

Aha! I suspect it didn't work for me because of an unticked reference. That makes a lot more sense. At least by me not having that though it meant we managed to get you some working code and some additional knowledge learnt along the way

 

[ftlab]

What it was actually doing was combining every line from the text file into a single cell, so A1 was the first text file in its entirety, B1 was the second text file in its entirety and so on.

If you need it so each line from the text file shows on it's own row (so a 10 line text file would go from row 1 to row 10), then I believe the tweak I have just made will resolve that issue. My changes have been highlighted in bold red.

I have also moved the message box til after the screen updates, although for the record it had "imported" the data already but screen updating was set to FALSE meaning the screen doesn't flicker. Either way, the message box now appears when you want it to instead:

Sub LoopThroughTextFiles()
' Defines variables
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim Text As String
Dim Textline As String
Dim LastCol As Long
Dim RowCount As Long


' Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False


' Defines LastCol as the last column of data based on row 1
LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
' Sets the folder containing the text files
myPath = "C:\TestFolder\Texts" & "\"


' Target File Extension (must include wildcard "*")
myExtension = "*.txt"


' Target Path with Ending Extention
myFile = Dir(myPath & myExtension)


' Loop through each text file in folder
Do While myFile <> ""
    ' Sets variable "RowCount" To 1
    RowCount = 1
    ' Sets variable "Text" as blank
    Text = ""
    ' Set variable equal to opened text file
    Open myPath & myFile For Input As #1
    ' Do until the last line of the text file
    Do Until EOF(1)
        ' Add each line of the text file to variable "Text"
        Line Input #1, Textline
        Text = Textline
        ' Update RowCount row of the current last column with the content of variable "Text"
        Cells(RowCount, LastCol).Value = Text
        ' Increase RowCount by 1
        RowCount = RowCount + 1
    Loop
    ' Close the text file
    Close #1
    ' Increase LastCol by 1 to account for the new data
    LastCol = LastCol + 1
    ' Get next text file name
    myFile = Dir
Loop


ResetSettings:
' Reset Macro Optimization Settings
Application.EnableEvents = True
Application.ScreenUpdating = True

' Message Box when tasks are completed
MsgBox "Task Complete!"


End Sub

Hi Fishboy, I see this code takes all the data from each text file and puts it into it's own a individual column. If I have text files which contain comma delimited data how do I split that out so it's all in it's own column? E.g. I have 3 text files, each containing 10 columns of comma delimited data, so the end result in Excel will be 30 columns of data.
Many thanks for your help.