• Home
  • Forums
  • FME Form
  • Data
  • Combine two Excel files with multiple worksheets into a single Excel file and preserve the formatting on each worksheet
Solved

Combine two Excel files with multiple worksheets into a single Excel file and preserve the formatting on each worksheet

  • 11 September 2018
  • 5 replies
  • 78 views
S
Badge

Hi FME experts,

I have two Excel files. One has 4 worksheets and the other one has just 1 worksheet. I want to combine the two Excel files into a single file, basically copying the 1 worksheet in my second Excel file and paste it into the first Excel file while preserving the formatting on each worksheet (I can combine the two Excel files into one, but just couldn't preserve the formatting on the worksheet from the second Excel file). The desired result should be a single Excel file with 5 worksheets. And each of the worksheet should have its original sheet name and formatting.

Here are the two sample Excel files if anybody wants to give it a shot.

Thanks in advance!

Henry

icon

Best answer by hollyatsafe 11 September 2018, 18:02

View original
1 Attachment

5 replies

chrisatsafe
Rank
Badge +2

Hi @sunhenry617,

One way to accomplish this is to read in both Excel datasets as Single Merged Feature Types then add an Excel Writer to the workspace.

Connect the Readers to the Writer and set the Sheet Name to fme_feature_type in the Excel Writer Parameters then run the workspace.

I've attached a sample workspace using your data.

Hope this helps.

- Chris

xlsx-example.fmwt

H
Badge +2

Hi @sunhenry617,

This can also be done using a FeatureWriter transformer, by having each sheet as an individual input, the transformer should pick up the sheet name but if not you can open the parameter editor to set this up. See image below for what this looks like:

Also please see the final file here - mergeexcel.xlsx

S
Badge

Hi @sunhenry617,

This can also be done using a FeatureWriter transformer, by having each sheet as an individual input, the transformer should pick up the sheet name but if not you can open the parameter editor to set this up. See image below for what this looks like:

Also please see the final file here - mergeexcel.xlsx

Thanks @hollyatsafe for your quick response. This is still not quite what I wanted. I want the field names to be bold as in the original Excel files. I was able to keep that for the 4 sheets from file a.xlsx in the result, but just couldn't do that for the 1 sheet from file b.xlsx.

 

 

Henry

 

S
Badge

Hi @sunhenry617,

One way to accomplish this is to read in both Excel datasets as Single Merged Feature Types then add an Excel Writer to the workspace.

Connect the Readers to the Writer and set the Sheet Name to fme_feature_type in the Excel Writer Parameters then run the workspace.

I've attached a sample workspace using your data.

Hope this helps.

- Chris

xlsx-example.fmwt

Thanks @ChrisAtSafe for your quick response. I am using FME 2016, so the workspace you sent to me is not quite compatible with my version. Earlier I had tried to use "fme_feature_type" as sheet name, but could not preserve all the formatting from the original sheets such as bold field names. If you can do that, please send me a copy of the result you got.

 

 

Thanks,

 

 

Henry

 

S
Badge

Hi @sunhenry617,

This can also be done using a FeatureWriter transformer, by having each sheet as an individual input, the transformer should pick up the sheet name but if not you can open the parameter editor to set this up. See image below for what this looks like:

Also please see the final file here - mergeexcel.xlsx

Thank you so much @hollyatsafe! This is what I needed and it turns out that FME 2016 (the version I am using) doesn't have the "Read formatting" option. But still, it is good to know the function is there for me when I get the new version of FME.

 

 

Thanks again for your help!

 

 

Henry

 

Reply


Community Stats

28,710
Posts
109,485
Replies
36,558
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.