Read multiple text files into a Spreadsheet

**fatti** · 09-12-2012, 04:09 AM

I need to read the contents of 100 or more log files into a worksheet.

Each file is named differently (7 digit number). All are contained in the same folder.

Each file contains a set of information in a single column.

I would like to copy that information into a speadsheet so that the contents of the first file are placed in the first column, second sheet into the second column.

Now I can read a single file into the first column and assume I just need a loop to increment there the data starts but its the reading the second file name onwards im having an issue with.

I could probably use the "dir /B /O:N >list.text" function at the DOS prompt to generate the files to read but not sure if this is the way forward.

Has anyone done somethng like this before? I cant imagine this is anything out of the ordinary but have Googled until my fingers bled and found nothing that fits.

Any assistance you can offer would be much appreciated.

**Tinbendr** · 09-12-2012, 10:57 AM

Sorry, I didn't have time to finish this. You can record a macro converting the text to columns.

Sub Main()

    'Declare a variable as a FileDialog object.
    Dim fd As FileDialog

    'Create a FileDialog object as a File Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    'Declare a variable to contain the path
    'of each selected item. Even though the path is aString,
    'the variable must be a Variant because For Each...Next
    'routines only work with Variants and Objects.
    Dim vrtSelectedItem As Variant

    'Use a With...End With block to reference the FileDialog object.
    With fd

        'Add a filter that includes GIF and JPEG images and make it the first item in the list.
        .Filters.Add "Log Files", "*.log; *.txt; *.csv", 1

        'Sets the initial file filter to number 1.
        .FilterIndex = 1

        'Use the Show method to display the File Picker dialog box and return the user's action.
        'If the user presses the button...
        If .Show = -1 Then

            'Step through each string in the FileDialogSelectedItems collection.
            For Each vrtSelectedItem In .SelectedItems

            FF = FreeFile
            Open vrtSelectedItem For Input As #FF
                'LoadTextFile = Input$(LOF(iFile), iFile)
                Temp = Input$(LOF(FF), FF)
                aCol = aCol + 1
                ActiveSheet.Cells(1, aCol).Value = Temp
            Close FF

            Next vrtSelectedItem
        'If the user presses Cancel...
        Else
        End If
    End With

    'Set the object variable to Nothing.
    Set fd = Nothing

End Sub

**fatti** · 09-13-2012, 10:31 PM

Mate this is awesome and worked straight off the bat with the exception of like you said to convert the text to columns as its placed the contents of each log file into the first cell on each column so its a big chunk of the way there.

Now converting that data to seperate cells in the same column is giving me issues as im not sure Excel recognises the delimiting character. You see the log files were just one big column of data anyway seperated by line breaks so they are easily readable.

The issue is Excel doesnt recognise this line break and has replaced that with a box with a ? in it. If i could replace this with a comma I would be set but it wont let me paste one of those characters into the Replace box. If I use the CODE function to determine what the character is it says its number 13 but when I use number 13 and do a replace the character is still there and inserts the comma beforehand as well.

Any ideas?

**Tinbendr** · 09-14-2012, 10:13 AM

Try this.

Sub ReplaceCR()
Dim C As Range
Dim FirstAddress As String

With ActiveSheet.Range("A1").CurrentRegion
    Set C = .Find(Chr(13), , xlValues, xlPart)
    If Not C Is Nothing Then
        FirstAddress = C.Address
        Do
            C.Value = Replace(C.Value, Chr(13), ",")
            Set C = .FindNext(C)
        Loop While Not C Is Nothing And C.Address <> FirstAddress

    End If
End With

End Sub