Uso de tabla en Extract Document Data

[mively]

tengo un for each in file en donde busca el pdf en una carpeta, extraigo la informacion con extract document data, tengo mas field name y al final un tableItem(lo que necesito es: Part#, Description, Qty, Lot, Price ) adjunto imagen de tabla en pdf original.
luego tengo un excel aplication scope en donde veo el excel que se escribiran los datos.
tengo un assig
arrTableItem = ExtractionOutput.Data.TableItem.Value.Split(Environment.NewLine.ToCharArray(), StringSplitOptions.RemoveEmptyEntries)

luego voy escribiendo con write cell los valores que no estan en la tabla, como invoice, date, country.

despues de eso tengo un for each que recorre arrTableItem
y adentro escribo lo que esta en la tabla (item.ToString.Split(","c)(4))

adjunto img de como me trae los datos de la tabla.

y escribo en descripcion item.ToString.Split(","c)(3) y aparece

mis preguntas son dos, COMO ESCRIBO TODA LA TABLA EN EL EXCEL, EN ESTE CASO LOS TRES ITEMS y COMO EXTRAIGO DE BUENA FORMA LA DESCRIPCION DEL PRODUCTO?

agradeceria demasiado su ayuda, para seguir aprendiendo de UiPath y avanzar!

[prashant1603765 (Prashant Kumar)]

Hi @mively

Try this:
To write the entire table in Excel, use a For Each loop with Write Cell to insert each row’s data from arrTableItem, splitting with Split(","c) for each column. For the product description, extract it using item.ToString.Split(","c)(2) (or the correct index based on your data structure).

[mively]

@prashant1603765

To write the entire table in Excel, use a For Each loop with Write Cell to insert each row’s data from arrTableItem, splitting with Split(","c) for each column. For the product description, extract it using item.ToString.Split(","c)(2) (or the correct index based on your data structure).

como escribo todos los datos del pdf ?me podrias ayudar en eso

[prashant1603765 (Prashant Kumar)]

@mively

como escribo todos los datos del pdf ?me podrias ayudar en eso

To write all the data from the PDF, extract the data using Extract Data from PDF (or Read PDF Text), then loop through the extracted data (e.g., arrTableItem). Use Excel Application Scope and Write Cell inside a For Each loop to write the data (like Part#, Description, Qty, Lot, Price) into the Excel sheet.

[mively]

se puede hacer con extract document data?
eso quisiera saber, para usarlo de ese modo

[prashant1603765 (Prashant Kumar)]

Great @mively

Yes, you can use Extract Document Data to extract data from the PDF. Store the output in ExtractionOutput, then loop through ExtractionOutput.Data.TableItem.Value to write the data (e.g., Part#, Description, Qty, Lot, Price) to Excel using Excel Application Scope and Write Cell.

Por favor, marca como solución si tu problema se resolvió.

[mively]

lo tengo escrito asi, pero solo escribe la primera fila de la informacion, help

[prashant1603765 (Prashant Kumar)]

@mively

Try this.

currentRow = 2
For Each row In ExtractionOutput.Data.TableItem.Value
Write Cell (“A” & currentRow) = row(“Part#”).ToString
Write Cell (“B” & currentRow) = row(“Description”).ToString
Write Cell (“C” & currentRow) = row(“Qty”).ToString
Write Cell (“D” & currentRow) = row(“Lot”).ToString
Write Cell (“E” & currentRow) = row(“Price”).ToString
currentRow = currentRow + 1
Next

[V_Roboto_V]

Hi @mively

Can you try this method and see:

The Workflow:

Step 1:
Convert the extracted text to a list of Rows based on semi colon:

listOfRows = inputData.Split(";"c).ToList()

Step 2:
Use For Each to loop through each item of List.

Step 3:
Use Assign to split the currentText based the comma:

listRow = currentText.Split(","c).ToList()

Step 4:
Use the Invoke as shown Below:
Code:

Dim partNo As String = ""
Dim description As String = ""
Dim quantity As String = ""
Dim lot As String = ""
Dim price As String = ""

partNo = listRow(0)
listRow.Remove(partNo)

price = listRow(listRow.Count - 1)
listRow.RemoveAt(listRow.Count - 1)

lot = listRow(listRow.Count - 1)
listRow.RemoveAt(listRow.Count - 1)

quantity = listRow(listRow.Count - 1)
listRow.RemoveAt(listRow.Count - 1)

description = String.Join(" ", listRow)

Dim currentRow As String  = partNo.Trim & "," &  description.Trim & "," & quantity.Trim & "," & lot.Trim & "," & price.Trim

listRow = currentRow.Split(","c).ToList()

Argument:

Step 5:
Replace the Log message that i have put, Use the activities to insert this list as a row into your Excel.

THE OUTPUT during each For-Each iteration will be:

As you can see, The Description is perfectly extracted. It will work for any description (even if there is more than 2 commas in description) provided that the last 3 columns are Qty, Lot and Price respectively.

Apologies for the delay,

I hope this solves the issue, Do mark it as a solution.
Happy Automation

[mively]

tengo esos alerta amigo

[V_Roboto_V]

Observe the for each. In your activity it is item instead of currentText.

and dont forget to set the Data Type of listOfRows and listRow

[mively]

tengo lo msimo que tu, pero no m muestra nada listRow

[V_Roboto_V]

Hi @mively
The input data you showed in the beginning is: (I developed the code with this input in mind)

The input data you showed now is: (i guess you are using the Output Excel data as Input by mistake. Please check)

It is a different format.

[mively]

@V_Roboto_V

he input data you showed in the beginning is: (I developed the code with this input in mind)

viene en ese formato, ya que lo extraigo con extract document data.
para poder ver la informacion de la tabla uso ExtractionOutput.Data.TableItem.Value

lo cual me da

apartir de esa informacion tengo que extraer los datos y escribirlos en un excel, como son tres datos, los tres debo escribirlos

[V_Roboto_V]

Esto requeriría un enfoque completamente diferente. Dame un poco de tiempo

[mively]

(Part#)	(Description)	(Qty)	(Lot#)	(Price)
6067.1041	Modular Screwdriver, 1/4 inch Quick-connect, Short	1	FAX051AG	608.00
6067.1041	Modular Screwdriver, 1/4 inch Quick-connect, Short	1	FAC340AH	608.00
6067.1041	Modular Screwdriver, 1/4 inch Quick-connect, Short	2	FAC346AH	608.00

esos datos necesito, y escribirlos en un excel, se me a complicado

[V_Roboto_V]

Could you copy and paste that input here or message it to me?

[mively]

@V_Roboto_V

Could you copy and paste that input here ?

Tabla: 1 6067.1041 Modular Screwdriver, 1/4 inch Quick-connect, Short 1 FAX051AG 608.00 608.00
2 6067.1041 Modular Screwdriver, 1/4 inch Quick-connect, Short 1 FAC340AH 608.00 608.00
3 6067.1041 Modular Screwdriver, 1/4 inch Quick-connect, Short 2 FAC346AH 608.00 1,216.00

[V_Roboto_V]

Hi @mively
Do these small changes, and it should work.

The input:

The Changes to done:
1. Put an extra Assign before listOfRows:

inputData = System.Text.RegularExpressions.Regex.Match(inputData, "(?<=Tabla: )[\s\S]*").Value.Replace(" ",",")

2. Change the Code in the Assign of listOfRows

listOfRows = System.Text.RegularExpressions.Regex.Matches(inputData, "\s*\S+").Cast(Of System.Text.RegularExpressions.Match)().Select(Function(m) m.Value).ToList()

3. Add these 2 extra lines of code in Invoke Code:

listRow.RemoveAt(0)
listRow.RemoveAt(listRow.Count - 1)

The Output:

Note: If the Regular expression fails in step 1 or 2, take the input value of ExtractionOutput.Data.TableItem.Value, and put it in Regex 101 website regex101: build, test, and debug regex.
Place the regular expression and test:

[mively]

@V_Roboto_V

ystem.Text

por alguna razon no entra al for each