Skip to main content
  • EN

I'm trying to read multiple spreadsheets and write one worksheet from each of them into a single output spreadsheet but am going round in circles...

Input data: I have a parent directory with an unknown number( and names) of subfolders. Each subfolder has a spreadsheet, results.xls. Each spreadsheet has, say, 10 worksheets, ws1, ws2...ws10.

Output: fme needs to search the parent folder to discover the subfolders. Then, for each subfolder, it needs to read the results.xls spreadsheet. A single spreadsheet needs to be written to disk containing worksheet ws5 from each discovered spreadsheet with each worksheet given the name of the subfolder that they came from.

The FME excel reader seems to need to know the name of a spreadsheet in advance, so I used a Path reader to discover the subfolders but passing these to a featureReader doesn't seem to read in the spreadsheets properly.

Any help would be appreciated.

Thanks

Macsmith

Do all the input spreadsheets have the same structure, i.e. sheet names, column names etc.?

Hi Ebygomm,

thanks for the reply. Yes, the speareadsheets all have the same structure, sheetnames and columns, etc.

macsmith

@macsmith You can combine two FME operations to do this.

Select Multiple Files/Folders: this browser allows you to select a folder and then optionally read all subfolders:

Then select Single Merged Feature Type.

thanks Markatsafe.

That looks very interesting. I'll try it out in the morning.

Many thanks

macsmith

@macsmith You can combine two FME operations to do this.

Select Multiple Files/Folders: this browser allows you to select a folder and then optionally read all subfolders:

Then select Single Merged Feature Type.

Hi Markatsafe,

I've tried your suggestion with mixed results. It does discover and read the various spreadsheets, creating one data set. I tried to figure out how to unscramble the data set as I would like to write out a new spreadsheet with one worksheet from each input spreadsheet.

Using my actual data spreadsheets was too cluttered, so I created 3 simple 2 worksheet example spreadsheets and read them in instead. However, the resulting dataset, as displ[ayed in Visual Preview, still showed collumns from the original data set as well as the new, even when I created a new workspace and tried again.

I then completely exited FME and restarted with a fresh workspace. That worked and all the data from the 3 simple spreadsheets was visible in Visual Preview. However, in re-running the workspace, Visual Preview now only displays data from the first of the three spreadsheets. Re-running makes no difference.

So I'm a bit puzzled.

macsmith

I've tried the suggestion with mixed results. It does discover and read the various spreadsheets, creating one data set. I tried to figure out how to unscramble the data set as I would like to write out a new spreadsheet with one worksheet from each input spreadsheet.

Using my actual data spreadsheets was too cluttered, so I created 3 simple 2 worksheet example spreadsheets and read them in instead. However, the resulting dataset, as displ[ayed in Visual Preview, still showed collumns from the original data set as well as the new, even when I created a new workspace and tried again.

I then completely exited FME and restarted with a fresh workspace. That worked and all the data from the 3 simple spreadsheets was visible in Visual Preview. However, in re-running the workspace, Visual Preview now only displays data from the first of the three spreadsheets. Re-running makes no difference.

So I'm a bit puzzled.

macsmith

Reply


Community Stats

30,112
Posts
114,305
Replies
37,745
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.