Extract contents from multiple .txt files into a single worksheet

[richardlit]

Hi there,

I have over 100 .txt files and need to extract the contents from each file into a single excel 2007 worksheet.

I dont know if there is an easy way to do this other than manually opening each file and copying the contents?

I would appreciate your help or any suggestions! I have not used VBA before so would prefer to avoid this (unless you could please explain what I need to do).

Thank you, Richard

 

[texasalynn]

Welcome to MrExcel board. . .


The add-in asap utility has a function that will do that

www.asap-utilities.com

or if that isn't an option for you there are several posts that have solutions for that. Try doing a search on this board.

 

[bertie]

I find the simplest way of merging text files is to write a batch file.

Save all your text files into the same folder.
Launch notepad and type in: copy *.txt myfile.txt
Save the notpad file to the same folder giving a .bat extension, select file type: all files
Navigate to the folder and double click on the batch file.
All files in the folder with a .txt extension will be merged into the file called myfile.txt

NB this wee trick also works for .csv files, change to copy *.csv myfile.csv

 

[richardlit]

thank you for your help Bertie.

My text files do not have a hard return at the end of the last line which means that the first line of the 2nd file is merging with the last line of the 1st file. Is there a way to prevent this? i.e.

File 1 has: "File 1
Line 1 of File 1"

File 2 has: "File 2
Line 1 of File 2"

Output in myfile is

File 1
Line 1 of File 1File 2
Line 1 of File 2

Thanks, Richard

 

[Richard Schollar]

I don't know of a clever way to append hard returns to text files using DOS, ut the following is a very quick way of doing so and opening up the resultant consolidated file in Excel.:

Sub testing()
Const MYPATH = "C:\Documents and Settings\Richard\My Documents\Spreadsheets\TextImport\"
Dim i As Integer
Dim myFile As String
myFile = Dir(MYPATH & "*.txt")
If Len(myFile) > 0 Then
    Do
        i = FreeFile
        Open MYPATH & myFile For Binary Access Write As #i
            Put #i, FileLen(MYPATH & myFile) + 1, vbCrLf
        Close #i
        myFile = Dir
    Loop While Len(myFile) > 0
    Shell "cmd.exe /c copy """ & MYPATH & "*.txt"" """ & MYPATH & "Consolidated.txt""", vbNormalFocus
End If
Workbooks.Open MYPATH & "Consolidated.txt"
End Sub

You need to amend the MYPATH constant to suit.

 

[wigi]

Hi there,

I would use:

Sub ImportTextFiles()
    sfolder = "C:\"
    For Each fl In CreateObject("scripting.filesystemobject").Getfolder(sfolder).Files
        If Right(fl.Name, 4) = ".txt" Then
            Open sfolder & fl.Name For Input As #1
            c0 = c0 & Input(LOF(1), #1) & vbCrLf
            Close #1
        End If
    Next
    [A1].Resize(UBound(Split(c0, vbCrLf))) = Application.Transpose(Split(c0, vbCrLf))
End Sub

Wigi

 

[duktuur]

I registered to this website just to tell you THANK YOUUUUU!!! This is the simplest solution.