Reading large csv file and need to change the date formats on output

Help Studio
1

Hi all,

I’m currently reading a csv file which has a title page in the first two rows. I’m using the assign and AsEnumerable function to skip the title page and read in the data. The data contains 57 columns where four of the columns are string dates in (“MM/dd/yyyy hh:mm:ss”) format. I need to change the format on output to (“yyyy-MM-dd hh:mm:ss”). I’ve gone through previous posts and most seem to use a for each row in combination with the Globalization.CultureInfo.InvariantCulture to change the format. This works but is extremely slow. Is there a more efficient way to Convert four data columns. My file is 47,669 records once I’ve removed the title page.

My current test

  • Excel Application Scope - Read Range
  • Assign - Using the AsEnumerable function and skip drop the title rows (created a table with 57 columns)
  • For Each Row - Replace the dates with the correct format.
  • Write a Caret delimited file

Thank you in advance.

2

@jose.a.silvestre - You can invoke code, as shown in this post…this would be efficient and does not use For each…

Note: you just have to tweak the date formats as per you need…

3

Thank you so much!! This worked perfectly.

1 Like
4

That’s great !! Don’t forget to mark your topic as solved so we don’t run into it while focusing on unresolved topics :grin:

1 Like
5

Prasath17,

Follow up question. I’m getting an error when the data row is blank. Can the code be modified to ignore blanks?

dtSample.AsEnumerable().ToList().ForEach(Sub(row) row(“DatefffZ”)=DateTime.ParseExact(row(“DatefffZ”).ToString,“yyyyMMddHHmmss.fff”,System.Globalization.CultureInfo.InvariantCulture).ToString(“yyyy-MM-ddTHH:mm:ss.fffZ”))

Thank you

6

@jose.a.silvestre - In that case, you have to handle the empty rows first…by removing it…

Once datatable in cleaned up you can use the above invoke code…

7

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