Skip to main content
  • EN

Hey all!

You all have helped me out a lot last time and today I again came across a problem which is why I'm posting again.

The situation

Basically there is a report that has to be created through exporting data from a database and putting it into an excel sheet. I want to automate this through FME.

So I added in two 'sqlcreators' which grab the neccesary data from the database. In the excel writer I want to write the data into specific columns. The issue is that again its going to stack the data on top of each other. I tried to use a featurmerger but obviously it does duplicating checks and thats not

necessary.

The only thing FME has to do is putting data from 'SQLCreator' in collumns A, B and C and putting the data from 'SQLCreator' into the columns E, F and G without stacking the data.

So how fme does it like this now:

How I want the result

Now I have a solution which is creating a workspace for each sql creator and using the output files of the previous sqlcreator output as a template in the excel writer. I however don't find that a really elegant solution and I'm pretty sure it can be made easier within FME itself.

Is this possible and if so how? I find it hard to find out what function within excel writer does and especially the relation between some functions ( truncate, named range and writer/update/delete functions). I have a feeling the magic is somewhere within the writer functions but where and how?

Hi @adriaan, this might work.

Use the Excel column names (A, B, ...) as user attribute names, and specify the line number (1-based integer) by a feature attribute. e.g. named "row_id".


Then set the writer feature type parameters like this.

Hi,

Additionally use counter transformer to generate the row number in sequence based on requirement ( I mean row number in Excel in your example you want from row number 3). If the headers are constant then use excel template option.

Hoi @adriaan,

Do you really need to put all the results in one sheet? it would be a lot easier to map each sql result into a separate xls sheet.

Hi @adriaan, this might work.

Use the Excel column names (A, B, ...) as user attribute names, and specify the line number (1-based integer) by a feature attribute. e.g. named "row_id".


Then set the writer feature type parameters like this.

Supplement. The features from the first SQLCreator should have only A, B, C (i.e. E, F, G, I, J, K should be missing). As well, the features from the second should have only E, F, G, and the features from third one should have only I, J, K. You can then send them to the writer feature type all together.

 

 

Reply


Community Stats

30,102
Posts
114,260
Replies
37,733
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.