0

Actually I have a list of customers from all the countries in one sheet name "ALL".

Problem: I have to crate separate sheets for group of countries like for USA sheet name will be USA and for Australia,Germany and Switzerland sheet name will be Central_Region output will be like below image.

What I have tried till now :- I used tFilterRow component and I have got all the separate excel files group by countries . now trying to combine in one file.

For Example : I have 5 excel workbook files each has one sheet like excel1.xls has sheet "USA" other excel2.xls has sheet "Canada" and same other 3 are in same way. Now I want to generate a single excel workbook which will have all the sheets like "USA", "Canada" and all other sheets from other excels.

I tried using iUnite but it did not help it just append all the sheets data into one sheet.Like below image

enter image description here

Sharad
  • 3,562
  • 6
  • 37
  • 59
  • is every excel has only one sheet which you want to process? and are those having same structure like in terms of number of columns their data type, sequence? please confirm. – UmeshR Jan 14 '15 at 12:15
  • yes each sheet has one sheet with same structure. – Sharad Jan 14 '15 at 12:18
  • you can mention sheetname in excel output component..so try with mentioning different sheetnames or using a globalmap.get varaible to get sheetname and keep appending the data to same excel file. – garpitmzn Jan 14 '15 at 13:04
  • possible duplicate of [Outputting a single Excel file with multiple worksheets](http://stackoverflow.com/questions/27221674/outputting-a-single-excel-file-with-multiple-worksheets) – ydaetskcoR Jan 15 '15 at 20:13

3 Answers3

1

Download this add-ins Open one your excel file and then open this add-ins file (also you can install that) when you open this file, select Enable Macro. Go to DATA tab on excel file and select RDB Merge add-in. set properties and push Merge Button. With this, your excel fills will merge in one sheet.

system32
  • 119
  • 1
  • 6
0

If you can't know in which order row will appear, you could store your data in csv files for each country.

Then you can add each csv file into a separate sheet on the Excel file using Write after.

If rows are coming in the rigth order, like all USA then Canada etc . . . you can directly use Write after in your ExcelOutput behind your tFilter but i highly suppose this is not the case.

Julien Breuil
  • 165
  • 1
  • 2
  • 15
0

If you have same structure excel file with different in sheet name then you have to make job like this.

tFileList---tFileInputExcel-----tMap---tFileOuputExcel

  1. Set source directory where you get all the files to the tFileList component.
  2. Use global varibale which hold "file with path" information and assign to the file name text box of tFileInputExcel component.
  3. In select Sheet box assign index instead sheet name.
  4. check Append property of tFileOuputExcel component you can merge all files in single one.

Note: in tMap you can add transformation or make changes in column sequence of output.

UmeshR
  • 803
  • 1
  • 9
  • 16
  • Thanks for reply I will try this solution, but is there any other solution to get without using tFilter row as fist i used tFilter to get separate files for each county and then combining in one excel sheet as per you solution. – Sharad Jan 14 '15 at 14:01
  • do you mean to say tFileList? instead tFilter? – UmeshR Jan 14 '15 at 14:05
  • I mean suppose you have one sheet "All" with all customers from all 10 countries , how can I get a excel with sheets "All" and other sheets for every country as my image which i have added. – Sharad Jan 14 '15 at 17:29