Skip to main content
Solved

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

  • September 11, 2018
  • 5 replies
  • 490 views
S
Forum|alt.badge.img

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

Best answer by hollyatsafe

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

View original
Did this help you find an answer to your question?

5 replies

chrisatsafe
Contributor
Forum|alt.badge.img+2
  • chrisatsafeContributor
  • Contributor
  • September 11, 2018

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
Forum|alt.badge.img+2
  • hollyatsafe
  • Best Answer
  • September 11, 2018

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
Forum|alt.badge.img
  • sunhenry617
  • Author
  • September 11, 2018
hollyatsafe wrote:

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
Forum|alt.badge.img
  • sunhenry617
  • Author
  • September 11, 2018
chrisatsafe wrote:

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
Forum|alt.badge.img
  • sunhenry617
  • Author
  • September 11, 2018
hollyatsafe wrote:

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


Helpful Members This Week

Community Stats

31,140
Posts
118,062
Replies
38,758
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.