How to convert datatype in CSV

There is date column(J) in csv with General format and i need to change that to Date format. Also another column(A) to integer format. how it can be done?

Help is much appreciated.

@siddhi.kamble

Read the CSV file into a DataTable (dtCsv).

For Each Row: Iterate through each row in dtCsv.

Use Assign: Convert the data type of the desired column (eg:-row(“Price”)).

Use Write Range: Save the modified DataTable (dtCsv) to a new CSV file.

OR

row(“DateColumn”) = DateTime.ParseExact(row(“DateColumn”).ToString(), “MM/dd/yyyy”, System.Globalization.CultureInfo.InvariantCulture)

@siddhi.kamble

to convert a date column (J) to Date format and another column (A) to Integer format

dataTable.AsEnumerable().ToList().ForEach(Sub(row) row("J") = DateTime.ParseExact(row("J").ToString, "M/d/yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture))
dataTable.AsEnumerable().ToList().ForEach(Sub(row) row("A") = If(Integer.TryParse(row("A").ToString, 0), Integer.Parse(row("A")), 0))
  1. It converts the values in column J from the “M/d/yyyy” format to the “yyyy-MM-dd” format for each row.
  2. It converts the values in column A to integers, handling non-integer values by setting them to 0.

cheerss.!

Hi @siddhi.kamble

' Load your CSV data into a DataTable (dt) using Read CSV activity

' Change the format of column J to Date format
dt.AsEnumerable().ToList().ForEach(Sub(row) row.SetField(Of Date)("J", DateTime.ParseExact(row.Field(Of String)("J"), "M/d/yyyy", System.Globalization.CultureInfo.InvariantCulture)))

' Change the format of column A to Integer format
dt.AsEnumerable().ToList().ForEach(Sub(row) row.SetField(Of Integer)("A", Integer.Parse(row.Field(Of String)("A"))))

' Now, the columns J and A are in the desired formats (Date and Integer, respectively).

Hope this helps!!

HI,

Although it depends on content of each column, AutoDetectTypes property of GenerateDataTable might work. Can you try as the following?

Regards,

I’m getting error as : Expression does not produce value

@siddhi.kamble

try this one

this is my solution

Just to put it into context:

A .csv file is a text file (mime type). Contents/values in it have no type.

If you read a .csv into either excel or a datatable object the type is determined runtime based on various default settings.
So to change the data types, read the csv first, then convert your datatable afterwards. (Some of the methods mentioned above might help depending on your case.)

But it is important to understand what you are changing where and when.