How to get data out of CSV based on an adjacent cell

Help
1

Hi guys

I am trying to read a number in a CSV file.

The number will always be directly to the right of the field saying “Lodgement”, but where that pair of text appears in the rows will differ every time I need to read this file. It could be 2 rows down, it could be 10 rows down, etc.

Essentially, I want to grab the “123456” below.

Any ideas on how I can read the CSV, search for the phrase “Lodgement” and then only grab the number directly to the right of it?

image

2

@Sheri

  1. Read CSV file activity - output is datatable
    so you can read by column name and you can do your actions.

if the columns are not fixed then

  1. Read CSV file activity - output is datatable
  2. outputdatatable - output is String
  3. if you know the row then you can find the index of the “Lodgement” using Datatable.Rows.indexof(“Lodgement”) then you can go cell by cell
3

For step 3, what activity do I use? Thx

4
  1. Read CSV into DataTable (make sure Add Headers is included in the properties)
  2. Filter the data table using DataTable.Select

If you are sure that always Lodgement is there in the CSV,

lodgementValue = myDataTable.Select("columnName1 ='Lodgement'")(0)(1).ToString

(0)(1) → 0 is the found row and 1 is the column index

If not,

Array of Data Rows, dr[] = myDataTable.Select("columnName1 ='Lodgement'")

If dr.Count > 0 Then
lodgementvalue = dr(0)(1).ToString
End If

Regards,
Karthik Byggari

1 Like
5

if you have the data like in latest image then please follow

6

What sort of activity should I be using?

7

Assign Activity.
Create a variable and assign.

8

you use assign activity

left hand side "lodgementValue "
right hand side = myDataTable.Select(“columnName1 =‘Lodgement’”)(0)(1).ToString

9

Thanks @KarthikByggari and @venkat4u I’ll give that a go!

10

Hi guys, I’m getting this error:

image

Here’s the assign I made (I need it to search for the full phrase, not just the word “Lodgement”:

Left: getDocumentNumber
Right: MyDataTable.Select(“columnName1 =‘Our Bank Document Lodgement’”)(0)(1).ToString

Would appreciate any further help!

11

Is your column name is “columnName1” ? in CSV File

12

No, the CSV looks like this:

image

13

So give like this : MyDataTable.Select(“Column-0 =‘Our Bank Document Lodgement’”)(0)(1).ToString

and make sure if it’s small c you have to give

MyDataTable.Select(“column-0 =‘Our Bank Document Lodgement’”)(0)(1).ToString
Try this

14

Same error I am afraid. Here’s my exact sequence:

image
image1229×901 20.9 KB

image

15

Hello @Sheri
try to surround column name with “” square brackets like: [Column-0]

16

Try this : MyDataTable.Select(“[column-0] =‘Our Bank Document Lodgement’”)(0)(1).ToString

17

Thanks everyone.

18

@Sheri,

please find the attached workflow.Main.xaml (9.7 KB)

Regards,
Lakshman Ganta.

19

Hello @Sheri
did surrounding column name with square brackets worked for you…?
if yes then please mark you ques as resolved.
if no then let us know…

20

Hi @Sheri Please find the attached workflow.I have used here “Filter data table” activity and result has been stored in a resulting data table.
Main.xaml (8.1 KB)

Regards