Convert pipe delimited txt file to xlsx

Question

I have to convert 40+ pipe delimited .txt files to excel sheet(.xlsx). Also all the fields should be treated as text while converting it. Below is what I found best efficient manual way of doing it. Just assume I have files as sample1.txt to ... sampleN.txt.

1. Drag sample1.txt file to empty workbook. Then it creates new workbook with single sheet with sheet name same as sample1 copying all lines in file under column A.
2. Select column A and use text to column wizard.
3. Choose delimited file with pipe (|) delimiter and select text data type after selecting all column in next wizard tab. Then Finish.
4. File 'Save as' sample1.xlsx

After recording macro, I am able to do step 2 to 4 with single key combination. However "sample1" gets hard coded in macro. Hence every time I have to rename the saved file appropriately. Is there any way the file name can be taken based upon current sheet name during "save as"? Is there any better and easier way of doing all this? May be like a batch mode etc..

[related](https://superuser.com/a/1222081/217811) and possibly helpful. — Stephan, Dec 01 '20 at 11:36

score 0 · Answer 1 · answered Aug 21 '14 at 23:46

I've go a macro I often use for looping through a folder of files. Note if will try and use all files (not just text ones) so I usually create a special folder just for importing.

The folder path is hard coded in the macro (this could be changed to bring up a folder dialogue. It then checks to make sure the path is valid and then loops through each file does something, closes the file and at the end it prints the number of files opened.

Sub process_folder()
Dim book_counter As Integer
Dim folder_path As String
Dim pWB As Workbook, sWB As Workbook, sWB_name As String
Dim pWS As Worksheet

    book_counter = 0

    Set pWB = ActiveWorkbook
    Set pWS = pWB.ActiveSheet

    folder_path = "C:\a"

    folder_path = verify_folder(folder_path)
    If folder_path = "NULL" Then
        Exit Sub
    End If

    'Get first file to open
    sWB_name = Dir(folder_path, vbNormal)

    'Loop through files
    Do While sWB_name <> ""

        'Open each file
        Workbooks.Open Filename:=folder_path & sWB_name
        Set sWB = Workbooks(sWB_name)

        '''''''''''''''''''''''''
        'Do stuff here (this just prints the file name)
        MsgBox (sWB.Sheets(1).Name)
        '''''''''''''''''''''''''

        'close file increment counter
        sWB.Close (False)
        sWB_name = Dir()
        book_counter = book_counter + 1
    Loop

    'Number of files processed
    MsgBox ("Number of Fragment Files processed: " & book_counter)


End Sub


Function verify_folder(path As String) As String

    If path = "" Then
        MsgBox ("Enter a Directory to process")
        verify_folder = "NULL"
        Exit Function
    End If

    If Not PathExists(path) Then
        MsgBox ("Directory does not exist")
        verify_folder = "NULL"
        Exit Function

    End If

    If Right(path, 1) <> "\" Then
            verify_folder = path & "\"
    End If

End Function

Function PathExists(pName) As Boolean
On Error Resume Next
    PathExists = (GetAttr(pName) And vbDirectory) = vbDirectory
End Function

Thanks for the response! How can I add the portion to open pipe delimited .txt file, convert it to xlsx and save with the same name? Sorry I'm complete novice... — user3965989, Dec 19 '14 at 21:25

Convert pipe delimited txt file to xlsx

1 Answers1