Error on Write Excel data

[Shyam_Pragash]

Hi Team,

Try get data from SQL Server using Sql query… while write to xlsx file i am getting this error

Sequence: Unable to set cell value to 10/28/2021 17:54:59 +05:30

i Notice the error is Indian standard timing stamp +5:30. so that i want remove the +5:30 to write the data in excel.

can you tell linq query for that …

Thanks
Shyam

[Anil_G (Anil Gorthi)]

@Shyam_Pragash

Please try this in invoke code with dt as in/out argument

dt.AsEnumerable.ToList.ForEach(sub(x) x("DateColumn")=x("DateColumn").ToString.Replace(" +5:30",""))

cheers

[Shiva_Nikhil]

@Shyam_Pragash

try this in invoke code

For Each row As datarow In dt.AsEnumerable
row(“ColumnName”)=row(“Columname”).ToString.Replace(“+5:30”,“”).Trim
Next

cheers

[Shyam_Pragash]

Hi @Anil_G

Thanks

I have 220 columns in sql data so that i didnt mention columns name individually

i want to find all the columns if +5:30 replace to Null…

Thanks
Shyam

[Shiva_Nikhil]

@Shyam_Pragash

try this

For Each row As DataRow In dt.Rows
For Each col As DataColumn In dt.Columns
Dim cellValue As String = row(col).ToString
If cellValue.Contains(“+5:30”) Then
row(col) = cellValue.Replace(“+5:30”, “”).Trim()
End If
Next
Next

[Anil_G (Anil Gorthi)]

@Shyam_Pragash

Please try this

dt.AsEnumerable.ToList.ForEach(sub(x) x.itemArray = x.itemArray.Select(function(y) y.ToString.Replace(" +5:30","")).ToArray)

cheers

[Shyam_Pragash]

Hi @Anil_G @Shiva_Nikhil

Thanks