So real data start at No_CLIENT
How can i remove all text before this?
I managed it by reading csv and writing as excel file and remove the rows. But i have formatting problem then. Is there any way to remove top three rows of a CSV Data?
Hi
once after reading csv and having a datatable we can skip the first three rows in the datatable like this datatable = datatable.Asenumerable().Skip(3).CopyToDatatable()
@Palaniyappan
Yes, i solved it that way. I wonder if its possible to ro delete till NO_CLIENT row. As it is possible that its not always 3 rows a NO_CLIENT. It can 4 or different in next file.
@Robott - yes you can create an integer variable that will determine how many rows you want to skip. I would do this by searching the first column for the string “NO_CLIENT”. Once you’ve found it, get the row index (and add 1 since the index starts at 0). Then pass in that integer variable into the .Skip() statement instead of the hardcoded 3. So amending my above solution it would like this:
Read CSV // output to dt1
Assign RowsToSkip (this is an integer variable) = EntireWorkbook.Rows.IndexOf(EntireWorkbook.Select("[Column1] = 'NO_CLIENT'")(0)) // Change Column1 to whichever column you want to search
Use the code given by @Palaniyappan above to skip the first 3 rows. Assign dt1 = dt1.AsEnumerable().Skip(RowsToSkip).CopyToDatatable()
Write CSV // Input = dt1 // AddHeaders should NOT be checked
Read CSV // output to dt1 // This should be the file you just created in step #4 // IncludeColumnNames needs to be checked
Use the ‘Delete file’ activity to delete the CSV you created in step #4
Please let me know if you have any questions. It is also important to note that this will throw in error at Step #2 if there are no rows containing the string “NO_CLIENT”, so you should add proper error handling for that scenario