How to convert pdf string data to Data table

Help Activities
, ,
1

hi ,
i have invoice data include table data in string format.

InvoiceNumber: 10280
InvoiceDate: 22 Aug 2018
PurchaseOrder: 26537
ShippingAdrress: Ultron Inc. - Receiving Dock 6395 N Russel Rd Oak Harbor, OH 43449
InvoiceAmount: 62.86
Table_items: Quantity: 1, Item no.: 1001, Description: Widget Type A, Unit Price: 22.00, Amount: 22.00; Quantity: 1, Item no.: 1007, Description: Bracket - Widget Type C, Unit Price: 36.75, Amount: 36.75

how to convert this in datatable ? can anyone help ?

2

Hi @anand_kumar4 ,

  1. Use Build Data Table with columns: InvoiceNumber, InvoiceDate, PurchaseOrder, ShippingAddress, InvoiceAmount, Quantity, ItemNo, Description, UnitPrice, Amount

  2. Extract the invoice header values using Regex or Split.

  3. Split Table_items by ";":

items = tableItems.Split(";"c)

This will give one row per invoice item, while repeating the invoice details for each row. UiPath’s Add Data Row supports passing an Object array whose values map to the DataTable columns.

Thanks

3

Hi @anand_kumar4

There are a few ways to do this, but I’m a fan of using vb.net code. So you can just pop this into an “Invoke Code” activity:

' Build the table structure
out_dt = New DataTable("InvoiceItems")
For Each col As String In {"InvoiceNumber", "InvoiceDate", "PurchaseOrder", "Quantity", "ItemNo", "Description", "UnitPrice", "Amount"}
    out_dt.Columns.Add(col, GetType(String))
Next

' Read the header fields (Key: Value per line)
Dim header As New Dictionary(Of String, String)
For Each m As System.Text.RegularExpressions.Match In System.Text.RegularExpressions.Regex.Matches(in_Text, "^\s*(\w+):\s*(.+?)\s*$", System.Text.RegularExpressions.RegexOptions.Multiline)
    header(m.Groups(1).Value) = m.Groups(2).Value
Next

' Parse each line item from Table_items
Dim itemsText As String = If(header.ContainsKey("Table_items"), header("Table_items"), "")
Dim pattern As String = "Quantity:\s*(?<Qty>[^,]+),\s*Item no\.:\s*(?<Item>[^,]+),\s*Description:\s*(?<Desc>.+?),\s*Unit Price:\s*(?<Price>[^,]+),\s*Amount:\s*(?<Amt>[^;]+)"

For Each m As System.Text.RegularExpressions.Match In System.Text.RegularExpressions.Regex.Matches(itemsText, pattern)
    out_dt.Rows.Add(
        If(header.ContainsKey("InvoiceNumber"), header("InvoiceNumber"), ""),
        If(header.ContainsKey("InvoiceDate"), header("InvoiceDate"), ""),
        If(header.ContainsKey("PurchaseOrder"), header("PurchaseOrder"), ""),
        m.Groups("Qty").Value.Trim(),
        m.Groups("Item").Value.Trim(),
        m.Groups("Desc").Value.Trim(),
        m.Groups("Price").Value.Trim(),
        m.Groups("Amt").Value.Trim())
Next

Should work - uses your current field names as column headers.

remember to pass “out_dt” as your dt going out as an argument.

Let me know.

4

Hi @anand_kumar4,

You can do this easily using Split + Add Data Row.

  1. Create a DataTable using Build Data Table with your required columns.
  2. First extract the invoice details from the string.
  3. For Table_items, use:

TableItems.Split(";"c)

This will separate each item into a different record.
4. For each item, split the values using “,” and extract Quantity, Item No., Description, Unit Price, and Amount.
5. Finally, use Add Data Row to add the values into the DataTable.

So, in your example, the 2 items will become 2 rows in the DataTable.

This approach worked for me as well and is simple to implement in UiPath.

5

@anand_kumar4

Why not using Document Understanding for invoice processing. That is a recommended approach instead of RegEx or string manipulation.

7

Hi @anand_kumar4,

If your files have the same structure, you can extract the details using string manipulation and regex. However, if the format varies, you should consider using UiPath Document Understanding, which supports semi-structured and unstructured data. You can also use UiPath GenAI activities.

I also noticed that you tried using Document Understanding but are still not getting the expected outcome. Based on my experience, you may be missing something in the configuration. I would suggest completing the UiPath Document Understanding training on Academy (https://academy.uipath.com/courses/uipath-document-understanding-overview-v2024-10) or checking the related YouTube playlist.

Thanks!

8

Hi @anand_kumar4,

If you have been getting this output always in the above provided format, you can use this workflow to extract the data.
RetrieveInvoiceData.xaml (13.4 KB)

If you have not automated the initial part (data extraction) itself then I would recommend using Document Understanding for Invoice data extraction.

Cheers!

9

Build Data Table → dt with columns: Quantity, Item no., Description, Unit Price, Amount

For Eachrow in:

vb

strInvoice.Split({"Table_items:"}, StringSplitOptions.None)(1).Split(";"c)

Inside the loop, add Add Data Row → DataTable: dt, ArrayRow:

vb

row.Split(","c).Select(Function(x) x.Split(":"c)(1).Trim).ToArray
10

@anand_kumar4 Since the invoice data is already available as a structured string, I don’t think Document Understanding is required just for converting it to a DataTable.

If the format is consistent, Regex would be a little safer than splitting everything by commas, because fields such as Description may themselves contain commas.

You can use Build Data Table → Matches → For Each Match → Add Data Row, capturing Quantity, Item No., Description, Unit Price and Amount based on their labels.

If the actual PDF layouts vary between vendors, then Document Understanding would make more sense at the extraction stage.