Retrieving Formatted Data from Extraction Excel

[melanie.p (Melanie)]

Hi there,
is it possible to extract specific content from the Simple Fields - Formatted Table as a variable?
Like I have:
“Key,Value
“Address Line 1",“1234 Hickory Hollon Ln”
“City”,"Raleigh”
“Country”,“United States”
“State / County / Province”,"North Carolina”
“Zip Postal Code”,“27610”

I need the variable which is in Address Line 1 for a sql query, so how is it possible to only extract the street from address line 1 instead of the whole key value fields? I previously worked with RegEx-Matches but I struggle with the diversity of streets our customers provide, so it’s possible that the RegEx-match just provide one part of the street I need for the query.

Thank you.

[supermanPunch (Arpan)]

Hi @melanie.p ,

Let’s say we have the Output in the form of a Dataset, So we loop through the Datatables in it.

While Looping through the Datatables, we can use an Assign Activity and Assign the value of Address to the variable in the below way :

addrVar = DT.Rows(0)("Address Line 1")

Assuming that the Address Line 1 will be your Column Name in the Datatable.

Let us know if you want to make further operations using Regex and How or What is the Exact Output required.

[Rajesh_Manikandan]

Try this,

addVar = DT.Select("Key='Address Line 1'").First("Value")

[melanie.p (Melanie)]

I always get “Option Strict on disallows late binding”

I tried DT.Rows(0).Item(6)(“Address Line 1”) & DT.Rows(0).Item(6).Select(Key=‘Address Line 1’").First(“Value”)

The row is called client-address the item of it is in the initial comment (Key,Value…)

Is there another way?

Thanks to both of you.

[supermanPunch (Arpan)]

@melanie.p

DT.Rows(0).Item(6)(“Address Line 1”)

@melanie.p ,

We already are using the Row Index as 0. We don’t need to call the row Index from Item again.

@supermanPunch

addrVar = DT.Rows(0)("Address Line 1")

This was the Approach suggested above.

We could a .ToString at the end

[melanie.p (Melanie)]

Isn’t it relevant to get the item of the data table?

With your approach I get: Assign: Column ‘Address Line 1’ does not belong to table DataTable.

I think because the client-address is provided as a CSV-string of Key,Value? I’m not sure though…

[ashwin.ashok ((TheCodingTheory))]

Hi @melanie.p ,

I’m not exactly sure if this is what you were looking for, but if you were to read the data as a text file and then generate DataTable out of it, you can use this query to retrieve the corresponding value →

Note: I removed the quotations, we can include them if needed

dt_sampleData.AsEnumerable().Where(Function(w) w.ItemArray.Contains("Address Line 1")).Select(Function(s) dt_sampleData.Rows(dt_sampleData.Rows.IndexOf(s))("Value").ToString).FirstOrDefault()

KeyValueCSV.xaml (5.7 KB)

keyvalues.txt (150 Bytes)

Kind Regards,
Ashwin A.K

[melanie.p (Melanie)]

Hi there,

after some more searching I found How could i seperate the pair "key,value" if i extract Document Understanding Dataset this. That’s actually the same I need. But I didn’t find the GetSimpleFieldValues like he did.

Also I get this from the DU extraction, like there are quotations. Isn’t there a possibility to get the street “1234 Hicckory Hollon Ln” without the Address Line 1?

That’s how I get the key,value pair:

I assigned this key,value pair as a string with DT.Rows(0).Item(6).toString. But I’m struggling to separate the Key from the Value…

Hope I could explain my situation a little bit better

[ashwin.ashok ((TheCodingTheory))]

Hi @melanie.p ,

Ideally, it would be best to sort them out while extracting the items using Document Understanding itself, but if that doesn’t work then the solution provided below should help you out.

Assuming that the quotations are present, you can simply assign the DT.Rows(0).Item(6).ToString to a variable and generate a CSV datatable from it which can be fed into the workflow shared below →

dt_sampleData.AsEnumerable().Where(Function(w) w.ItemArray.Any(Function(a) a.ToString.ToLower.Contains(str_address.ToLower))).Select(Function(s) dt_sampleData.Rows(dt_sampleData.Rows.IndexOf(s))("Value").ToString).FirstOrDefault()

KeyValueCSV_v1.xaml (6.6 KB)

Kind Regards,
Ashwin A.K

[supermanPunch (Arpan)]

@melanie.p

@melanie.p , Since the Data is Clubbed together and present in the Cell, We could however alternately use a Regex to just the address data.

Below is the Regex :

(?<=Address Line 1",).*

In your case, We could use the below Expression to get the Matching value :

System.Text.RegularExpressions.Regex.Match(DT.Rows(0).Item(6).ToString,"(?<=Address Line 1"",).*",RegexOptions.IgnoreCase).Value.ToString.Replace("""","")

Apologies for not understanding the Data was of the Type Address earlier.