Ho to copy excel rows

Help Activities
,
1

Hi,
I have incapacity= 10, name= ram
1 Excel having columns A B C

I want to copy here 10 rows of columns A, B and write Ram in D column 10 rows
Can anyone help me here…
Thanks in advance…
@ppr
@Palaniyappan

1 Like
2

@panguluri_saritha

Can you please show some sample input and output files…if you can attach some samples that would help

cheers

3

Here
I have incapacity=2 name = ram
Sample.xlsx
A B. C.

Gh. Fbhg. Cycvh.

Fg. Ty. Ghj.

… … …

. … …

… … …
… … …

I want to just copy the 2 rows of A. B column data and also write respected 2 rows of D column ram

Just copying the required rows and write the name in d column , if we copy 5 rows then we need to write name in 5 rows
@Anil_G

4

@panguluri_saritha

try these steps

  1. say data is there in datatable dt,integer cap and string name
  2. Use assign with dt = dt.AsEnumerable.Take(cap).CopyToDataTable
  3. Use add data Column with dt as datatable …column as D and datatype string
  4. Use assign with dt.Columns("D").Expression = "' + name + '"
5

Hi @panguluri_saritha

Can you try this-

  1. Read Range (SourceFile.xlsx, “A1:B10”) → DataTable1
  2. Build DataTable (Name)
    • Add Data Row (“Ram”)
    • Add Data Row (“Ram”)
    • … (repeat 10 times)
  3. Merge DataTable (DataTable1 and Name columns) → MergedDataTable
  4. Write Range (OutputFile.xlsx, “A1”) → MergedDataTable
6

Hi @Anil_G
thank you so much for the code
its working absolutely fine…

here the next time how can we copy from 11th row onwards
it means

leave already copied ones every time and copy from next row onwards and same like above we can assign to newly copied rows
thanks in advance

@Anil_G

7

@panguluri_saritha

You have to keep a track of what is already copied in a variable and add that as skip…in the take and use it…

eg: dt.AsEnumerable.Skip(NumberAlreadyUsed).Take(cap).CopyToDataTable

cheers

8

Hi @Anil_G
thank you,

but here how can we track already used ones because here capacity is coming from queue
and we are not doing any loop for the table, for every new queue item it will be cleared …

9

@panguluri_saritha

Save the value into an asset…and keep on incrementing based on capacity value

Cheers

10

oh okay!

so you are trying to say 1st we copy 10 rows then keep that 10 in asset

2nd time to copy row we have to give that asset value 10 in above skip variable

right…

dt.AsEnumerable.Skip(10).Take(cap).CopyToDataTable
@Anil_G

1 Like
11

@panguluri_saritha

yes even first time you cna give that…just in your init set value of asset to 0…so skip(0) will not skip anything…and you cna keep on incrementing the asset for each loop

cheers

12

@Anil_G
Thank you very much for your time…

1 Like
13

Hi @Anil_G ,

here the name is need to enter in orginal data table but adding in temporary table,

  1. Use assign with dt.Columns("D").Expression = "' + name + '"

I have eg intcap=5; 1st time, the name should write in D2 onwards and d2+5 rows we have to give name in D column

2nd time eg intcap=8; the name should append to the next row( after d2_5 rows)

any modification to above assign or any other linq query to insert the data dynamic range to the d column
Thanks in advance

14

@panguluri_saritha

First add in a temp table…and then perform a merge datatable

cheers

15

Hi @Anil_G ,
Thank you but I am doing in middle so many filtering operations, if there is no other way I will do merge,
can we do dynamic write range, but I am not sure how to take range

16

@panguluri_saritha

There are may other ways…even if you are doing filtering I am not sure why is it a problem to merge?

cheers

17

Hi @Anil_G ,
actually when using readrange some column0, col1… etc are adding which are not there in sheet, so it can mislead msay be I am thinking

18

@panguluri_saritha

If they are not needed use a filter datatable and filter those columns

cheers

19

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