Parsing text file after defined number of symbol "|"

Question

I have a text file that looks like this:

442342|442342|213123|5345233|5432543|5|5435|345345|345345|345345 etc.

I want to parse it to input a line break after every 5th symbol "|" and and import to Excel with | as delimiter.

So the output would be Excel file with 5 cells in each row containing the data from txt file.

My idea was to parse it in macro in VB in Word or as batch file and then use import function of Excel to get it into Excel. Unfortunately - I failed miserably in both cases.

Any ideas how this could be done fast and efficiently?

score 1 · Answer 1 · edited May 23 '17 at 12:12

Transforming the text file into multiple lines with 5 columns each can be easily done using a hybrid JScript/batch utility called REPL.BAT. It performs a regex find/replace on stdin and writes the results to stdout. It is pure script that will run natively on any modern Windows machine from XP onward.

Assuming your text contains only one line:

<test.txt repl "((.*?\|){4}.*?)\|" "$1\r\n" x >new.txt

If your text file already contains multiple lines, then you can use one more REPL to first remove the newlines and replace them with |.

<test.txt repl "\|?\r?\n" "|" m | repl "((.*?\|){4}.*?)\|" "$1\r\n" x >new.txt

Thanks for posting - a bit too complex for me but seems completly viable! — xixi, Sep 22 '14 at 17:25

score 0 · Accepted Answer · answered Sep 22 '14 at 15:10

This assumes that your input data is in a single long text string:

Sub GetAndparse()
    Dim TextLine As String, I As Long, J As Long
    Close #1
    Open "C:\TestFolder\TestFile.txt" For Input As #1
    Line Input #1, TextLine
    ary = Split(TextLine, "|")
    I = 1
    J = 1
    For Each a In ary
        Cells(I, J).Value = a
        J = J + 1
        If J = 6 Then
            J = 1
            I = I + 1
        End If
    Next a
    Close #1
End Sub

worked like a charm - thanks a lot! looks so easy and I couldnt figure it out by myself! — xixi, Sep 22 '14 at 17:24

Parsing text file after defined number of symbol "|"

2 Answers2