Read Excel-data and converting variable-types

[OKlink (Oliver Klink)]

Hi everyone!
I try to read an Excel-table, perform tests on the data of each row (using if and switch on the values of specific colomns) and then perform an action depending on the values of the row. Finally I want to write a result in a specific column of each row.
For instance if the value of column “year” is < 2000 my result would be “Before 2000”.

I tried Read Range and then iterate through the results with For Each Row.
Then get the data with Get Row Item an write it in my predefined variables.
My problem now is the type conversion of the data. I would like to get some data as integer or Int32 and others as string values, so I can perform comparisons like mentioned above. But what I get is an object an errors, when I try to store it in an interger variable.

So, how do I store results from Get Row Item in variables of a specific type?
Or should I use a completely different strategy like Read Cell / Read Row instead of Read Range?

Extra Question: for now I use a counter variable that I increase with each itration of For Each Row to determine in which row I have to write my result. Is there a better way?
I don’t want to store my result in the data table. I want it written to the Excel file in each iteration, so I am sure I won’t lose it, if there is a problem anywhere in the workflow.

Thanks for helping!

Oli

Also I had a counter-variable that I increased with each

[khangnv (Khang Nguyen)]

Hi @OKlink

@OKlink

So, how do I store results from Get Row Item in variables of a specific type?
Or should I use a completely different strategy like Read Cell / Read Row instead of Read Range?

• I think you can check type of object before assign it in a variable. Use this: row(column name/ column index).GetType
  If all characters are number, the type of object will return System.Double
  Ex: row(column name/ column index).GetType.ToString.Equals(“System.Double”)
  => True: Convert it to Integer
  => False: Convert it to String

@OKlink

Extra Question: for now I use a counter variable that I increase with each itration of For Each Row to determine in which row I have to write my result. Is there a better way?

I think it’s OK OR:

• You can add an integer variable into Index in Properties panel of For Each Row. It’s index of your row (Start from 0)

@OKlink

I don’t want to store my result in the data table. I want it written to the Excel file in each iteration, so I am sure I won’t lose it, if there is a problem anywhere in the workflow.

No problem. Just use Write Cell to write and save your result.

I hope this will help you
Regards,
Khang

[OKlink (Oliver Klink)]

Hi Khang,

thank you for your answer.

@khangnv

@OKlink

So, how do I store results from Get Row Item in variables of a specific type?
Or should I use a completely different strategy like Read Cell / Read Row instead of Read Range?

• I think you can check type of object before assign it in a variable. Use this: row(column name/ column index).GetType
  If all characters are number, the type of object will return System.Double
  Ex: row(column name/ column index).GetType.ToString.Equals(“System.Double”)
  => True: Convert it to Integer
  => False: Convert it to String

That’s what I tried to do. I had a number that was read into a SystemDouble. And I wanted to store it into an integer variable. But my Problem was, how to convert it.

As I found out it is quite easy: you don’t need to convert it.
I use Get.row.item to store the value in a variable of the type GenericValue.
Then I use assign to store (and convert) the value in a variable that I declared to fit my needs (string, integer …).

@khangnv

• You can add an integer variable into Index in Properties panel of For Each Row. It’s index of your row

Hmm. Is this new? I only have DataTable and Private in the properties.
I still have to work with Studio 2018.1.5

Regards,
OKlink