Excel date data being read as date type or double type

Help Studio
, ,
1

Hi everyone,

I have a excel file full of data with different formatting.
For the “Date” column, some formatting are date, some are general, some maybe others

When I use “Read range workbook” activity, some date data been assigned as date type data, and some been assigned as double

What I needed to do is to filter the DataTable by a date type variable
e.g. Using “Filter Data Table” activity, keep only datarow that “Date” >= date type variable (31 Oct 2023)

Q1: These sometime returns 0 result, why is these happening?

Q2: The goal is to read multiple excel (there are different formatting to date column), and filter the DT “Date” column if datarow “Date” value is >= the date type variable

I also tried to check “PreserveFormat” from “Read Range Workbook” activity properties, seems like a data column read as string? but it also added space to the column name, e.g. from “Amount” to " Amount ", that cost extra issues

2

@mlellison

Instead of filter try the below

Dt.AsEnumerable.Where(function(x) If(DateTime.TryParseExact(x("DateColumn").ToString,"dd MMM yyyy",System.Globalization.CultureInfo.Invariant Culture,System.Globalization.DateTimeStyles.None,Nothing), DateTime.ParseExact(x("DateColumn").ToString,"dd MMM yyyy",System.Globalization.CultureInfo.Invariant Culture) > Datevariable,False)).CopyToDatatable

Cheers

3

Thanks @Anil_G

It result in below error
“Assign: The source contains no DataRows.”

Checked dt.rowcount is > 0

Any idea?

4

@mlellison

as per error looks like there are no rows matching the criteria…can you please show the dateformat that you are getting in datatable and use that format instead of dd MMM yyyy

You can check the dateformat in the locals panel by opening the datatable variable from locals panel when run in debug mode

and in future to avoid this error when there are no rows matching first we can check if there are any rows and then use .CopyToDatatable

so first use if conditon with Dt.AsEnumerable.Where(function(x) If(DateTime.TryParseExact(x("DateColumn").ToString,"dd MMM yyyy",System.Globalization.CultureInfo.Invariant Culture,System.Globalization.DateTimeStyles.None,Nothing), DateTime.ParseExact(x("DateColumn").ToString,"dd MMM yyyy",System.Globalization.CultureInfo.Invariant Culture) > Datevariable,False)).Count>0

on then side use the formula given previously and on else side use finaldt = dt.Clone()

cheers

5

@Anil_G
The source have lots of different format, sometimes its “MM/dd/yyyy hh:mm:ss”, sometimes its “dd MMM yyyy”, sometimes its double (45230), etc

6

@mlellison

it supports multiple formats …can try using them.

or can use presert format which will get in single format mostly

if the column name containing spaces is an issue…after readiing first we can trim the columnnames and reassign

usign for loop with dt.columns

and assign curretnItem.ColumnName = curretnItem.ColumnName.Trim

cheers

7

When within the datatable are represented as

  • as string within one or more formats
  • as double representing an OADate value

then we can handle by a defensive check e.g. for a cleansing in advance or within a LINQ

LINQ Starter help:

(From d In dtData.AsEnumerable
Let dv = d("YourColName").toString.Trim
Let oachk = Double.TryParse(dv, Nothing)
Let dchk = DateTime.TryParseExact(dv,arrFormats,CultureInfo.InvariantCulture, DateTimeStyles.None, Nothing)
Let dp1 = If(oachk, DateTime.FromOADate(CDbl(dv)),New DateTime() )
Let dp2 = If(dchk, DateTime.ParseExact(dv,arrFormats,CultureInfo.InvariantCulture,DateTimeStyles.None), New DateTime())
Let dp = New DateTime(){dp1,dp2}.OrderBy(Function (x) x).Last()
Where dp.Date >= CDate("10/31/2023").Date
Select r = d).CopyToDataTable

Kindly note:

Also we can decopose the LINQ to essential activities

8

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.