How to move columns in a datatable?

studio

#1

Hello! I’m merging several CSV files which all have almost the same form, except that in some files there’s a date in 3rd column, in others it’s in the seventh column. I was wondering if there’s any way to check if a cell contains a date type value, and if so to move it in the table? Help would be really welcome!

Here’s what I was thinking:

  1. Read CSV
  2. For each row -> If statement (to check if cell with index 6 contains a date)
  3. If so: move it to index 2
  4. Append datatable to Excel

I’m pretty sure this is the way to go, I just don’t know if there’s any activities or code I can use for this. Thanks in advance!


#2

Hi,

You could reformat the datables before merging using SetOrdinal on the datacolumn.

Datacolumn.SetOrdinal(2) should do the trick

Cheers


#3

I don’t know if this helps, but the Merge Data Table Activity will combine the 2 tables based on the columns, so even if the Date column is in column 7 or 2 it still joins them together. However, this requires that the column names are the same for both tables, but you could find the date column then rename it with dt1.Columns(0).ColumnName = “string”… otherwise, it adds the column that doesn’t exist in the other table, since it merges the 2 tables


#4

Thanks guys, got it working :slight_smile: