Copy from one Excel file to another one with a table

excel
activities

#1

Hi, Im trying to copy data from one excel file to another.

Excelfile1: .xls file, 3 columns with data, all in string format
Excelfile2: .xlsx file, contains an empty table

What I wanna do is copy the data from Excelfile1 to Excelfile2 to filter my data in the table. Column three in Excelfile1 contains both integers and floats, but as strings originally. Example of values in the column: “2000” and “199,44444”. When I try to read the whole sheet with “Read Range” activity from “Excel Application Scope” it somehow converts “2000” to 2000 (an integer) and “199,44444” to “199,44444”. This executes problems in my table and I get wierd values. How do I solve this problem? What method can I use to convert “199,44444” to 199,44444? Or even better, “199,44444” to 199.


#2

Hey @Felix

To convert a number from string to integer you can use something like this :slight_smile:

Int32.Parse("2000",CultureInfo.InvariantCulture)

and similarly to convert float values i guess it is 199.44444 or may be 199,44444 because of your locals 1?

Double.Parse("199.44444",CultureInfo.InvariantCulture)

For round value

Math.Round(Double.Parse("199.44444",CultureInfo.InvariantCulture)) //199

but you should use this for cases like 199.5

if you want it like 199

Math.Round(Double.Parse("199.44444",CultureInfo.InvariantCulture)) //199

but if you want 199.5 to 200 then use this

Math.Round(Double.Parse("199.44444",CultureInfo.InvariantCulture),MidpointRounding.AwayFromZero) //200

Regards…!!
AKsh


#3

So what I try to do is to loop through my rows in my DT and change the values that still are strings and not integers:

For each row in DT:
Try catch:
Try:
Assign:
row(2) = Int32.Parse(row(2),CultureInfo.InvariantCulture)

This gives an error and it says:
CultureInfo is not declared. It may be innaccessible due to its protection level.

Regards Felix


#4

Import System.Globalization namespace.


#5

You have to import System.Globalization namespace for this.

image

Regards…!!
Aksh


#6

Okay, thanks a lot for good and fast answers! :slight_smile:

/F