Excel -list of files to do modification

Help
1

Hi All ,

i’m having list of excel sheets as below :
Legacy_Shipment_Matching_TE_1505201907
Legacy_Shipment_Matching_TE_1505201908

Now I need to read each excel file and do some modification and then save it, then need to mail the modified excel sheets .

2

Hi @vikumars1
Is the list in an excel file?

1 Like
3

Hi Jan ,

Legacy_Shipment_Matching_TE_1505201909.xlsx
Legacy_Shipment_Matching_TE_1505201910.xlsx
Legacy_Shipment_Matching_TE_1505201911.xlsx
Legacy_Shipment_Matching_TE_1605201901.xlsx

Above is the sample list of files …
1)i need to open it and spilt the cell value based on the comma and save it back.
2)After that need to mail using outlook

1 Like
4

Kindly follow this procedure buddy @vikumars1

  1. lets take like you have the above mentioned files in a folder
    so to get those files in that folder of xlsx type alone we can get using this in an assign activity
    out_file_path_array = Directory.Getfiles(“yourfolderpath”,"*.xlsx)
    where out_file_path_array is of type array of string
  2. Then using a for each loop by passing the above variable as input and type argument of for each loop should be of type string
  3. inside the for each loop use a excel application scope with input as item from for each loop
  4. inside the excel application scope use a read range activity with input of sheetname and output of type datatable named out_dt
  5. with that next you can use a for each row loop all still being inside the very first for each loop, where in the for each row loop pass this out_dt as input and you can get the value of each cell in each row of a column using a assign activity like this
    out_cell_value = row(“columnname”).ToString
  6. Then you can make the split like this split(out_cell_value,“,”)(0).ToString to a variable in assign activity, where 0 is the index that you want buddy you can change as per your wish
  7. FInally you can use send outllook mail activity to send the mail through outlook and to attach the file you can mention the path of the newly saved file as string argument

Thats all buddy you are done
Kindly let know whether this works or not buddy
Cheers @vikumars1

5

Hi Palani ,

1)I 'm facing few challenges in the 4th point
Depending upon the excel file names, sheet name also varies .

Legacy_Shipment_Matching_TE_1505201911.xlsx(For this files sheet name is Legacy_Shipment_Matching_TE_150)

Legacy_Shipment_Matching_TE_1605201901.xlsx(for this Legacy_Shipment_Matching_TE_160)

Depending upon the date, after TE_ number varies.

image
I have used like this ,but read range is not working .

2)IN the 5th point my excel sheet doesn’t having column names, so how could i used this assign activity
out_cell_value = row(“columnname”).ToString

1 Like
6

Hi @vikumars1

How many sheets does you input file can have ?? will it be always one sheet or more than that ??

Can you please share some sample/dummy input files ??

Regards,
Vijay.

7

Buddy @vikumars1

Great… for

  1. if you are not sure about the sheet names, let the bot itself identify the sheet names from the excel and you can by this
    –when you us a excel application scope, that activity has a output variable of type workbook
    get that variable named out_workboook
    –use a assign activity to get the sheetname of that workbook like this
    out_sheetname = out_workbook.GetSheets(0),
    well you can use 0 if you have only one sheet
    or if there are many sheets inside and you want to iterate with each sheet and get data from sheet you can pass the Out_Workbook.GetSheets to a for each loop with type argument as string , and inside the for each loop use a assign actiVity to get the sheet name like this
    out_sheetname = item.ToString
    -now pass this out_sheetname to the read range activity for the input sheet name buddy @vikumars1

Then for
2. If you dont have the column name you can use the column index buddy and index starts from 0 for the first column like this
out_cell_value = row(Columnindex).ToString
like
out_cell_value = row(1).ToString
where 1 implies the second column

Thats all buddy you are done
Kindly try this ad let know whether this works or not buddy
Cheers Buddy @vikumars1

8

Hi Palani,

Now I need to clear all the data’s present inside an excel sheet .
Suggest me any activity

9

Hi @vikumars1
1 use start process with your excel file path as your input
2. use a send hot key activity with keys ctrl+A and select the excel as element
3. use again send hot key activity with keys ctrl+D
4 finally a send hot key activity with keys ctrl+s
thats all buddy
Cheers @vikumars1

10

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