How to import text files in MS Excel with each line in a separate column?

Question

I have > 5000 World files which are basically text files with 6 lines of data. I want to merge them all in one MS Excel file in the following format :

• 1st column : the World file name
• 2nd column : 1st line in the World file
• 3rd column : 2nd line in the World file
• 4th column : 3rd line in the World file
• 5th column : 4th line in the World file
• 6th column : 5th line in the World file
• 7th column : 6th line in the World file

With each line being one of the World file.

Is there a script that can do this?

Answer 1

First place your filespecs in Sheet1 column A like:

and then with Sheet1 selected, run this macro:

Sub WideWideWorld()
    Dim N As Long, i As Long, k As Long
    Dim s As String, j As Long, kk As Long

    kk = 1
    N = Cells(Rows.Count, "A").End(xlUp).Row
    Close #1
    For i = 1 To N
        k = 1
        s = Cells(i, 1).Value
        Open s For Input As #1
        For j = 1 To 6
            Line Input #1, TextLine
            Sheets("Sheet2").Cells(kk, k).Value = TextLine
            k = k + 1
        Next j
        Close #1
        kk = kk + 1
    Next i
End Sub

The output will be stored in Sheet2

I suggest starting with a small subset in Sheet1 to see if the format meets your needs.

The routines pulls the first lines out of each file..........so be sure each file contains at least 6 lines.