Remove top 3 rows in csv / Remove text till

Help
1

Hi,
my CSV starts as

;;;;;;;;;
Monthly Report;;;;;;;;;
;;;;;;;;;
NO_CLIENT

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?

1 Like
2

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()

Cheers @Robott

2 Likes
3

You may need Read Text File and Write Text File, before Read CSV.

4

Thanks, but it is adding
followings at top of the file

Column1,Column2,Column3,Column4,Column5,Column6,Column7,Column8,Column9,Column10

5

Hi ,
Here is the package called “BalaReva.EasyText”. It has an activity called “Delete At” activity to remove the line of text(csv) file.

Regards
Balamurugan.S

6

Kindly disable INCLUDECOLUMNNAME in READ CSV activity

@Robott

1 Like
7

It add a new row at the top of Datatable which is very strange. Somehow i managed to solve it by write CSV without header and then read CSV again.

Is there anyway to remove the last row of datatable?

8

yah we can
with REMOVE DATAROW activity where in the datarow index mention as datatable.Rows.Count-1
@Robott

1 Like
9

From the looks of it, the delimiter is the semicolon, is that correct? If so, make sure you choose the correct delimiter in the ‘read CSV’ activity

If so, you need to:

  1. Read CSV // output to dt1
  2. Use the code given by @Palaniyappan above to skip the first 3 rows. Assign dt1 = dt1.AsEnumerable().Skip(3).CoypToDatatable()
  3. Write CSV // Input = dt1 // AddHeaders should NOT be checked
  4. Read CSV // output to dt1 // This should be the file you just created in step #3 // IncludeColumnNames needs to be checked
  5. Use the ‘Delete file’ activity to delete the CSV you created in step #3

Now you have a datatable with the first 3 rows removed with the correct column headers.

1 Like
10

@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.

11

@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:

  1. Read CSV // output to dt1
  2. 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
  3. Use the code given by @Palaniyappan above to skip the first 3 rows. Assign dt1 = dt1.AsEnumerable().Skip(RowsToSkip).CopyToDatatable()
  4. Write CSV // Input = dt1 // AddHeaders should NOT be checked
  5. Read CSV // output to dt1 // This should be the file you just created in step #4 // IncludeColumnNames needs to be checked
  6. 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

1 Like
12

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