Add prefix to whole row

Help Studio
, ,
1

Hi Everyone,
i have one Excel usecase in which i have to add prefix 0000000 to whole row which already contains some data.rows can be more than 4K.
ex:- input:- output:-
column0 column0
2345 00000002345
5435 00000005435

2

@neha.kumarief868

Please use this formula =TEXT(A1,"0000000000")

you can use write cell with "=TEXT(A1,""0000000000"")" in the first cell

then use auto fill range activity

cheers

3

HI @neha.kumarief868

You can try this

  • Read range and save them in the dt variable
  • then build a datatable with only the headers as same as the master data and create another variable
  • Use a Assign
    Build dt variable =below expression
(From d In dt.AsEnumerable
Let f=d(0).ToString
Select dt2.LoadDataRow({f.ToString.PadLeft(11,CChar("0"))},True)).CopyToDataTable

image
image1488Ă—664 36.3 KB

Where dt is the master data , dt2 is build datatable variable

I have created new variable dt called variable1 and used that in assign you can do this or use the same build datatable variable in the assign

Regards
Sudharsan

1 Like
4

What is this a1
Already populated data is not same at each row

5

@neha.kumarief868

A1 is the excel cell number…change it to any cel number where your data starts…

And auto fill range activity will take care of changing them sequencially

What the formula does is say in A1 you have 123 and you gave the formula with 00000 5 zeros then 00123 is what gets populated…if next row has 12 then 00012 will be populated, basically 0 are to say how many numbers should be present in the field

image

Hope this helps

Cheers

6

Hi Anil,
its not working

Screenshot (66)
Screenshot (66)1172Ă—685 72.8 KB

7

Have you tried with the above expression @neha.kumarief868 ?

8

@neha.kumarief868

If you data is in A2 then use write cell with cell value as B2 not A2 in the formula it would be A2…else it will over write your data

Please check my acreenshot

Cheers

9

i have to write on the same cell.

10

can you help me how to do it in excel without using build datatable

11

How many columns you will have in the main excel @neha.kumarief868 ?

12

more than 4K rows
column :-5-7

13

Can you send a sample excel file with exact headers and dummy data in it @neha.kumarief868 ?

With only 2 or 3 rows

14

Test.xlsx (60.2 KB)
Material is the column where i have to add 5 zeros

15

Hi Sudharan,
im not getting the prefix in the excel wheras in the dt & dt2 variable i can see 00000 prefix.
Datatable

16

Can you elaborate please ,It is quite not understandable @neha.kumarief868 ?

17

while debugging i can see 0000prefix in every row of variable dt2 ,but it is not added in the excel.

18

Give the variable which is in the Build datable to write in the excel and check again @neha.kumarief868

19

I did the same

dt error
dt error1225Ă—552 57.1 KB

20

@neha.kumarief868

You can follow thw above method of excel cormula and then use copy range to copyt the data to required column

Cheers