Merge 2 Excel files

Hi all, 

Is there any built-in function in Kapow that can merge 2 Excel files together? 
Otherwise what are the actions to be done? Thanks for the help in advance

There are several methods to merge excel files that do not consume Kapow KCU resources (VBA Script is one option), but if Kapow is the only option, here is the approach I would take:

• Build a Type that matches your desired output, adding one "Attribute" for each Column you require.  As you are building the Type, keep in mind any Primary Key(s) you may need.  As you are adding Attributes, Check "Part of Database Key" Option for each Primary Key you need.
  
• Use the built-in Create Database Table to create a database that contains your Type.
  

• Create a new Robot to extract data from your Spreadsheet.
  
• Be sure to add the Type you created in your variable list.
  
• Add one branch for each spreadsheet that contains the data you need to extract, and one additional branch to create a new spreadsheet that contains the merged data.
  
• One Each Spreadsheet Branch, open your spreadsheet and add a Loop Rows step.
  
• Loop each row extracting required data to your Type.
  
• After you have extracted what you need from the Row, add a Store In Database step.  The Variable to Store In Database will be the Type you built.  
  
• Complete extracting each row and storing in Database.  When all rows have been looped, your robot will go down the next branch.
  

Repeat the extract from Spreadsheet steps for each spreadsheet you need to process.  Note, that on subsequent branches, depending on your Database design, you may need to use an Execute SQL step to Update a record in the Database rather than using Kapow's built-in Store In Database step.

Once all spreadsheets have been completed, you can add a final branch to create a new spreadsheet that contains the merged data.  Query the Database you created and populate a new spreadsheet.  Once all output has been added you can distribute your output to meet your needs.

hth, jk

Hi Jking,

I guess I would need your help with this. I have created 2 variables of the Excel files that I am working with, details are gotten through the file action.
After that, what is the action that I can take? Thanks for the help in advance.

You have 2 excel files.  You want some (or all) information from the 1st spreadsheet and you want to add some (or all) of the information from the second spreadsheet.
You created 2 excel variables, one for each spreadsheet.
You extracted some or all data from both spreadsheets into their respective variable and used a store in database set (or an equivalent step) to save the extracted data into a database.
Now you want to merge data from the 2 excel variables into one new spreadsheet.

Create a blank spreadsheet.  Make sure your excel variable is Global.
Add a Query Database to Select the data you need from your 2 database files and complete your Variables Map.
Set Content of the spreadsheet using the variables you used in your Query Database step.
Once the Query Database step has looped through all iterations you can write out your new spreadsheet that contains the merged data.

Here is how I would most likely set up my robot branches:

Thanks for sharing the approach! Looks like Kapow alone is much more complicated to work with Excel built-in functions. However, what other actions are included in:

1) Open Spreadsheet #1
2) Open Spreadsheet #2
3) Extract Data (For both 1 and 2)
4) Create New Spreadsheet
5) Update Excel
6) Output Report

Also, how can I save the variables in the database? I am new to Kapow and meanwhile still trying to get myself familiarise with the tools. Appreciate your help.

Have you looked through the tutorials?  The Help file has information and tutorials with videos on Branches, Robot States, Execution, Flow, Looping, Excel (extracting from and writing to), etc., etc., etc.  I understand that you are new, but IMHO the best way to learn to use Design Studio to build your robot is to start using Design Studio.  When I started 5+ years ago (9.2x) there were no videos or forums.  I'm happy to help to an extent, but please, give it a go:

Learn how to load your spreadsheet.  
Adding a loop step is really pretty easy - just try it.
Extracting is a simple as Clicking on a cell.
Store in Database is created just like it sounds.... add a Store In Database step.

Once  you have your 1st spreadsheet branch created, duplicating those steps will be a cinch...

We already discussed outputting your newly created spreadsheet with the merged data.

I'm sure if you looked at the Help files you will be successful in building out this robot.  

Let the forum know how you progress.

Yes, I have reviewed the video tutorials and have tried working out the approach many times. However, I am still stuck with the steps. Currently, I am facing issue with the database where I am not able to select any variable created (Not sure if I have to create a variable in the database? How can I do that?)

In order to Store In Database, you need to create a database table from your Type (note I am using "Generic" as my Database in the screenshots below:

Once you have created your Type, Click Tools → Create Database → Select a Database (drop-down box).
Generate SQL Statement dialogue box will appear.  Check "Drop table if exists" → Click Execute. (Note, Design Studio will write the SQL Statement.  Do NOT write the statement yourself).

Go to Management Console → Data Tab and check that your database appears in the list of databases.

In your Robot, you need to Add the Type you created.

In your Store In Database Step, Click the Drop Down Box and select the Database where you created the Database above.  Check Execute in Design Mode if you want the action to to store while in Design Studio.


The Variable will be the Type you created.

In you Loop Spreadsheet branch you will extract data from each row/column into the Type Variable.  Once all data has been extracted from each row, the last step will be the Store In Database step.

Step through extracting your 1st row, Go to Management Console → Data Tab → your Database and the data extracted should appear in your database.  

Thanks for the detailed information. I actually store the Excel file into the type I created (Named as Excel). Not sure can this work?
My steps are shown below, not sure if I am on the right track?

While you do need an excel variable, I have never used the method you outlined...  

I have always created a Type that contains 1 attribute for each Column I need to extract from excel.  Typically I name each attribute the same name as the column heading, or if there are no column headings in the excel spreadsheet, a name identifies the type of data I am extracting.

Add Excel Variable
Create Type that contains attributes you need to extract
Create Database
Add Type as Variable to your robot
Load File, Extract Excel, Open Excel
Loop Rows → Extract data → Store in Database

Which extract activity did you use for the data extraction in this case? I tried using Extract alone and I am facing this issue.
Alternatively, is the VBA method easier for this? How can I link Kapow to the merged excel files using VBA? Thanks

As far as whether VBA is easier, that depends on if you know how to write VBA.   You need to determine that for yourself.

For the Kapow process, the 3 steps you outlined above are not correct and do not match the steps I have outlined. 

Your last message contains a screenshot with 3 steps:  Load Page → Extract Excel 1 → Store in Database.
My screenshot showed Load Excel Spreadsheet #1 → Extract Excel → Open Excel.

In order to clarify more, here are the correct steps you need:

#1: Load Excel Spreadsheet #1.  This is a Load File Step.  
#2: Extract Excel.  This is an Extract→Extract Source Step.  Source is Binary.  Extract Data To is an excel variable.
#3: Open Excel.  This is an Open Variable Step.  Variable will be your excel variable.

Once you step past these 3 steps, you should see your excel spreadsheet in Window View Panel. Once excel is open you need to Extract data... you do not simply put in a Store in Database step... there is nothing yet to store...

#4 Insert a Loop → Loop in Excel and choose what to Loop (Sheets/Columns/Rows/Cells).

* Here I am making an assumption that you want to extract some or all columns in the spreadsheet for each row in the spreadsheet AND you have created a Type and a Database as I have outlined in my earlier response  *

For each Column in your excel spreadsheet row that you wish to extract, Right-Click that cell → Extract to your variable.  Note that you need to repeat this step for each column of data in the row that you want to extract.  If you need to extract data from every column in the row you are looping, you need an extract step for every column.

After you have extracted whatever you need from the row - THEN you can use the store in database step.

Thanks for the help, just wonder if Kapow can also remove the unwanted columns from the merged files? Then lastly remove duplicates like the one in Excel. Thanks in advance

If you are extracting the data / columns you need from Spreadsheet #1 into Type#1 and Storing in Database #1 and
If you are extracting the data / columns you need from Spreadsheet #2 into Type#2 and Storing in Database #2 and
If you are querying Database #1 and Database #2 for the data points you need and creating a new Spreadsheet then
There should be no "unwanted" columns since your query would select only the columns you need.
You can control for duplicates in your Select Statement