Workbook excel activities

Learn Academy Feedback
, , , ,
#1

Hello everyone,

Scenario: I have a workbook say W1 which has 6 columns A,B,C,D,E,F. Now, i want to fetch the data for column A and F and write it another workbook say W2. I am working with excel 2003 and Excel Application scope does not support it so I do not want to use it.
Is it possible to achieve in a ’ not so complicated ’ way using workbook activities and data table ? Workbooks are in different folders.

#2

@Tarun_Bajpai Yes, You can try following these steps to get what you need :

  1. Read the Excel File using Workbook Read Range Activity, get Datatable as Output, say DT.

  2. Use Filter Datatable Activity, In Filter Wizard Section, in Output Columns Section, Specify the Column Names taht you want to keep. Note that you have to use the Column Names of the Column, and not the Range (Not A,B,C,D…)

  3. Get the Output from Filter Datatable, It should contain only the Columns which you need.

#3

thank you @supermanPunch.
I believe the solution you proposed when i have just one data table.
Suppose, i have used 2 read range activities and fetched the data in two separate data tables.
workbook activity
read the data
first read range activity gives me a datatable fname
second read range activity gives me a datatable comp
Now I want to write the data of these two data tables in another workbook in such a way that data for first name column goes to column A of the workbook and company goes to column C of the workbook

Could you please help on this.

#4

@Tarun_Bajpai I didn’t fully understand the Output you need :sweat_smile: . Can you Show us both Input files and the Expected Output File.

#5

@supermanPunch
i need to have the below output in another workbook.
output i need

#6

@Tarun_Bajpai Can you provide your Input File, I’ll try to do it in a way without using two Datatables for the Same Data. But you do need to note that, you just want to map First Name to the First value in other Columns. If there is a Mapping to map each of the values then it would be better.

#7

@supermanPunch
I need to do it using two data tables. There could be a scenario where data could be in 2 different workbooks and it needs to be fetched and put in 3rd workbook, then we will have to use 2 data tables, isnt it?

#8

@supermanPunch
I am not allowed to attach the input excel file.

not able to upload attachment
not able to upload attachment1005×276 16.9 KB

#9

@Tarun_Bajpai Will the number of records in the 3 Inputs be the same?

#10

@supermanPunch: They may change

#11

@Tarun_Bajpai What will be the Expected output in that case?

#12

@supermanPunch: new columns may get added

#13

@supermanPunch: as of now, if you could provide a solution for the scenario i have mentioned with screenshots, that’d be more than enough :slight_smile:

#14

@Tarun_Bajpai Check this Workflow :
CombineColumnValues.zip (10.7 KB)

This will give the output you need when you have the same number of records in each Sheet. Also this is not a very good approach, but we can work on the other approach if you can give me an expected Output for other type of cases involved.

#15

Hi @supermanPunch,
thank you very much for the xampl file. I am going to look into it.
Let me frame my question again.
Scenario 1:

  1. I have a workbook ( .xls file ) and it has only one sheet.
  2. sheet has 6 columns named firstname, lastname, Age, Email, City,company
  3. number of record may change may change but number of columns won’t change.
  4. I have another workbook in which there is one column i.e. Coulmn B which is not available for editing and hence you can not use it. I want to add data from firstname and company columns in this new workbook in such a way that data for firstname coumns gets placed at Column A of the sheet1 and data for company coumn gets placed at Coulmn C of the sheet1.
  5. I can not use .xlsx file, i can not use excel application scope.

Scenario2:

  1. I have 2 workbooks WB1 and WB2.
  2. both workbooks have only one sheet, the default one i.e. sheet1
  3. WB1 has one column in sheet1 which is named has firstname
  4. WB2 has one column in sheet1 which is named has lastname
  5. I have another workbook say WB3 -> This is the output file
  6. I want to place the data from WB1 and WB2 to WB3.
  7. Firstname in WB3 should be at 1st column of the sheet
  8. Lastname in WB3 should be at 3rd column of the sheet
#16

@Tarun_Bajpai The Column B not editable matters here :sweat_smile:, Have you checked using Read Range If it is able to get that Column Data, also can you tell me why it isn’t editable, we can make process simpler if it is editable. Also Read Range Can even read xls files.

#17

@supermanPunch: Well, Column B has already data available in it which is not supposed to be changed and this column is in output file. why do i need to use read range on output file.

#18

@Tarun_Bajpai Since you cannot use Excel Application Scope, you need to read the Output data, since you need to merge 3 Column Data. Otherwise it is not possible. Using Excel Scope we could have just appended the Values to the Columns in the Output File.

#19

@supermanPunch:
Coulumn B data can be read but what if column B is blank. Point it, i have to use Coulumn A and C to place the data. Is it possible?

#20

@Tarun_Bajpai So the Output will be Column A with Values, Column B being Empty and Column C with Values, is that what you need ?