Consulting

Results 1 to 9 of 9

Thread: How to convert pipe delimited table data back into proper tables?

  1. #1
    VBAX Regular
    Joined
    Feb 2020
    Location
    Auckland, NZ
    Posts
    29
    Location

    How to convert pipe delimited table data back into proper tables?

    We produce accessible format files for blind folks. We strip out all formatting from source files (from OCR etc) in order to start from scratch. I have a procedure/Sub which copies the contents of a file and pastes them into a new document as text, but this also removes tables. I've now added a Sub which turns any tables in the source file into pipe delimited data before pasting as text. But I need help writing VBA to convert these lines of pipe delimited data back into proper tables. Any help would be very much appreciated.

  2. #2
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    Unless it is a state secret, it would help if you should an example of what this pipe delimited text looks like.


    But in the absence of that, assume the following selected text represented a table:
    Title||Apples|Blueberries|Cherries|Dates||Eggplant|Figs|Ginger|Hops

    where "||" delimits a row and "|" delimits a column.


    Sub ScratchMacro()
    'A basic Word macro coded by Greg Maxey
    Dim arrMajor() As String, arrMinor() As String
    Dim strText As String
    Dim oRng As Range
    Dim oTbl As Table
    Dim lngIndex As Long, lngRows As Long
    Dim lngEval As Long, lngCol As Long
      Set oRng = Selection.Range
      strText = oRng.Text
      arrMajor = Split(strText, "||")
      lngRows = UBound(arrMajor) + 1
      Set oTbl = oRng.Tables.Add(oRng, lngRows, 1)
      For lngIndex = 0 To UBound(arrMajor)
        arrMinor = (Split(arrMajor(lngIndex), "|"))
        oTbl.Rows(lngIndex + 1).Cells(1).Split 1, UBound(arrMinor) + 1
        For lngCol = 0 To UBound(arrMinor)
          oTbl.Cell(lngIndex + 1, lngCol + 1).Range.Text = arrMinor(lngCol)
        Next lngCol
      Next lngIndex
    lbl_Exit:
      Exit Sub
    End Sub
    Last edited by gmaxey; 02-13-2020 at 07:13 PM.
    Greg

    Visit my website: http://gregmaxey.com

  3. #3
    VBAX Regular
    Joined
    Feb 2020
    Location
    Auckland, NZ
    Posts
    29
    Location
    Well, not quite a state secret , but here is an example from a test file:

    Column 1 | Column 2 | Column 3 | Column 4 | Column 5
    Row 1 | Table cell | Table cell | Table cell | Table cell
    Row 2 | Table cell | Table cell | Table cell | Table cell

    This is generated from a procedure whose guts look like this:

    [vba] For Each myTable In ActiveDocument.Tables myTable.Select
    Selection.Style = ActiveDocument.Styles("Normal")

    Application.DefaultTableSeparator = "|"
    Selection.Rows.ConvertToText Separator:=wdSeparateByDefaultListSeparator, _
    NestedTables:=True

    Next myTable
    [\VBA]

  4. #4
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    Okay, in your example a Chr(13) (Paragraph mark) would be used to delimit the rows and you would select everything but the last paragraph mark

    Text.jpg
    Greg

    Visit my website: http://gregmaxey.com

  5. #5
    VBAX Regular
    Joined
    Feb 2020
    Location
    Auckland, NZ
    Posts
    29
    Location
    Thanks so much for that code Greg, not something I could've done as a relative beginner. Will pick this thread up next week when back at work and have had a chance to install and test.

  6. #6
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Try:
    Sub Demo()
    Application.ScreenUpdating = False
    With ActiveDocument.Range
      With .Find
        .ClearFormatting
        .Replacement.ClearFormatting
        .Text = "[!^13]@|*^13"
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindStop
        .Format = False
        .MatchWildcards = True
        .Execute
      End With
      Do While .Find.Found
        .ConvertToTable "|"
        .Collapse wdCollapseEnd
        .Find.Execute
      Loop
    End With
    Application.ScreenUpdating = True
    End Sub
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  7. #7
    VBAX Regular
    Joined
    Feb 2020
    Location
    Auckland, NZ
    Posts
    29
    Location

    SOLVED

    Thanks so much Paul. That works perfectly.
    I've added a bit of table formatting to put the cell borders back in, as well as removed unwanted spaces left over from the pipe delimiters. So now the cleanup process is now seamless, all at a click of a button from the ribbon!
    Thanks again.

  8. #8
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Quote Originally Posted by Walentyn View Post
    removed unwanted spaces left over from the pipe delimiters.
    I wasn't sure whether those extra spaces were just part of your representation here, since the code you posted doesn't include them. That part can probably be achieved by changing:
    .ConvertToTable "|"
    to:
    .ConvertToTable " | "
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  9. #9
    VBAX Regular
    Joined
    Feb 2020
    Location
    Auckland, NZ
    Posts
    29
    Location
    That part can probably be achieved by changing:
    .ConvertToTable "|"
    to:
    .ConvertToTable " | "

    After a bit of testing I've concluded VBA doesn't like any spaces here. It throws up an error every time: "Invalid procedure call or argument."

    So I'm calling a find and replace function to remove the extra spaces around the pipe character, which are indeed inserted by a procedure that turns tables into pipe delimited data. That procedure is doing double duty here, where the spaces are undesirable.

    In any case, your code is simple and elegant, and right down to the wildcard search term, teaches me a lot.

    So, most grateful to you, thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •