Change data type of imported Excel data?

[Terry_Marr]

When using Read Range or Read Workbook activities to import Excel data into a data table variable, is there a way to convert or force the data type of the column? Perhaps setting up the table before I read the excel? Or is this an extra step that I must perform after the data is imported, say by transferring the data to another data table? If I have a date field, for example, but it is stored in Excel as string, can I “read” it and convert it to the proper date format? What about numerical fields, such as currency?
Or when working with data in Excel, do you just resign yourself to everything being in a string?

Or is there a different activity to use if you want to massage the data types before loading into a data table variable?

[Erimateia (Erimateia Lima)]

Hi, @Terry_Marr

Follow the steps below. If I helped you, please mark it as solved.

Step 1
All columns are of text type and then:

Name Columns: Will be of text type
Age Columns: Will be of number type without decimal places
Salary Columns: Will be of number type with two decimal places
Date Columns: Will be of date type

before
All columns are of the type text

after

Step 2

Step 3

Step 4

Step 5

Step 6

Here is the code:
Main.xaml (14.4 KB)

dados.xlsx (9.4 KB)

[Yoichi]

Hi,

Basically, Column type of DataTable is Object and the data is proper type which based Cell format in the worksheet OR String type.

For example, data stored in excel as Date type, it will be DateTime when ReadRangeWorkbook without PreserveFormat option.
data stored in excel as Date type, it will be String when ReadRangeWorkbook with PreserveFormat option.

If I have a date field, for example, but it is stored in Excel as string, can I “read” it and convert it to the proper date format?

It will be String type data in Object type column. It’s necessary to convert data using LINQ etc.

The following sample code may help you.

Sample20241021-1.zip (9.3 KB)

Regards,

[Terry_Marr]

That was very helpful, thank you!