KAPOW

Welcome to the Kapow forum. Here you can get help, use your skills to help others and enjoy hanging out in the company of other Kapow Robot Developers.


Excel Extraction and insertion from several Excel files to one Excel with several Sheets ...

Share

Zoel

Posts : 7
Points : 83
Join date : 2018-10-16

Excel Extraction and insertion from several Excel files to one Excel with several Sheets ...

Post by Zoel on Mon Oct 22, 2018 6:37 pm

Hi all, 
 
As described above, I’m trying to Extract from several Excel files in a local/Network directory .to merge these to only one Excel file that contains several sheets with the original names of these files.
 
I attached a page to show how i started my process, I’m struggling though to get the loop over the Excel files fr4om the directory and get them to write data to the new file. 
 
Let me know if you guys can’t see the screenshot i attached. 
 
R/
Zoel
avatar
jking

Posts : 57
Points : 1829
Join date : 2014-03-01
Location : USA

Re: Excel Extraction and insertion from several Excel files to one Excel with several Sheets ...

Post by jking on Tue Oct 23, 2018 12:37 am

One possible solution:

Create a Type with enough Attributes (variables)  for the Maximum Number of Columns that appear in the spreadsheets you are extracting.  I would suggest these be Short Text.
(For example, I have 3 spreadsheets to consolidate.  One has 8 columns of data, one has 12 columns, another has 10 columns.   I need a Type with 12 variables).
Add an Attribute (variable) to the Type, one for each Column (Column A, Column B, Column C, etc. until you have enough Attributes (variables) for the maximum number of columns that appear on any of the spreadsheets you will be looping.
Include an Additional Attribute FileName and another Attribute Row Number.
Save Type and Create a Database Table.

Open your 1st spreadsheet.  Bring in the Type you created above using the Add Variable Step.  Assign FileName  to FileName  variable.  Loop Rows, extracting Column A to Variable A, Column B to Variable B, etc. until you have extracted all data from the 1st row.  Extract RowNumber to Variable RowNumber. Insert a Store In Database step selecting the Database where you created you Table in the step above and Select the Type as the Variable after the row has been extracted.
Loop through all rows in 1st spreadsheet, extracting and storing the Data.

When the 1st spreadsheet is completed, perform the same steps for the remainder of your spreadsheets, extracting and storing all information. You could use separate branches, one for each Spreadsheet to consolidate or use Repeat Next step with a Loop For Each File step to process all spreadsheets to consolidate,

Once all the data has been extracted, open your blank spreadsheet and insert a Query Database step, selecting * from the Database table you created. 
Order by FileName and Row Number.
Map the Variables to your Type (Column A to Type Column A, etc).
Use a Set Content of Cell Step to Transfer data from your Database into the blank spreadsheet.  Content would be either Variable (Column A, Column B, etc) or Converter if you need to change Short Text to another Format (number, integer, date, time, etc).
Once all rows from the 1st FileName, insert a new Sheet and repeat the process until all of the Files have been inserted into their own Sheet.
Use a Write File step or Send Email step to output the new consolidated spreadsheet.

Important things to consider:
Excel is a memory hog.  Depending on the number of spreadsheets you are processing, it may be a good idea to break the steps up:
Create 1 robot to extract each of the individual spreadsheets.
Create an additional robot to create the new consolidated spreadsheet.
Run in sequence such that 1 robot must finish before the next robot starts.

hth.

chrismemo

Posts : 13
Points : 255
Join date : 2018-04-24
Location : Singapore

Re: Excel Extraction and insertion from several Excel files to one Excel with several Sheets ...

Post by chrismemo on Fri Nov 02, 2018 9:52 am

hi Zoel,

Other option is trying to create a small Excel Macro or any custom code (any language that you familiar) that monitor any excel in a monitored folder and generated the combined output.

Kapow will run the DA that call the small Excel Macro or any custom code either through scheduling or Kapplet. 
You will need to handle the exceptions part when file open or failed to combine.

Regards,
Chris

Sponsored content

Re: Excel Extraction and insertion from several Excel files to one Excel with several Sheets ...

Post by Sponsored content


    Current date/time is Tue Dec 18, 2018 11:07 pm