Skip to main content
  • EN
Hello,

 

 

I have five feature classes: wPump, wControlValve, wMainLine, wUtilityArea, wServiceArea. The wPumps, wControlValves, wMainLines and wUtilityAreas all reside w/in various wServiceAreas. I am attempting to create an excel file that has one row for each wServiceArea, with the Service Area Name being the first column. Each successive column would provide the following information for each wServiceArea:

 

 

1) number of wPumps in each wServiceArea

 

2) number of wControlValves in each wServiceArea

 

3) length in feet of wMainLine in each wServiceArea

 

4) square footage of wUtilityArea in each wServiceArea

 

 

So far, I’ve only been able to successfully get one of these bits of information into the excel file using the SpatialRelator & StatisticsCalculator transformers.

 

 

 

When I attempt to add a second item, the excel file that gets generated now contains 2 rows for each ServiceArea.

 

 

 

What’s the best method for getting the excel file to have just one row for each ServiceArea and have the correct values in each column?

 

 

FWIW, I'm using the Data Interoperability Extension in ArcGIS 10.1. Here is some additional info:

 

Edition: Data Interoperability (node locked-crc)

 

Version: FME(R) 2012 SP1 (20120318 - Build 12229 - WIN32)

 

Locale: en_US

 

Codepage: 1252 (ANSI - Latin I)

 

Home Directory: D:\\Programs\\ArcGIS\\Data Interoperability (x86)\\

 

 

Thanks in advance!

 

 
1. Place the writer mode as update_cell_contents

 

2. Enable xlsx_row_id in format attributes and maintain the row number (in excel) with each record

 

3. In format Parameter, select the row number attribute which you are maintaining (else rename the attribute as xlsx_row_id)

 

4. Maintain the colums as as User Attributes
Further information to be maintained...

 

1. xlsx_row_id should be in numeric i.e. for data of ServiceArea1 = 1 and  ServiceArea2 = 2 and so on

 

2. Data from pumps should not have data of control values or column C (it will be updated if it is exists)
Thanks for the feedback Pratap. I have a few follow up questions/comments:

 

 

1) The writer mode in the version I'm using doesn't have update_cell_contents. It only has "UPDATE". Does this accomplish the same thing as "update_cell_contents"? is this a non-issue?

 

 

2) I'm stuck on step 2 in your first response. Perhaps I'm not looking in the right location, but I cannot seem to find xlsx_row_id to enable it. Here's a screenshot of my writer's Format Attributes:

 

 

 

3) In step 3, when you say "select the row number attribute which you are maintaining", does this mean selecting them in the "SQL Key Column" section as seen below?

 

 

 

Thanks again for your help!
Hi,

 

 

In Format Parameters it says "SQL Key Columns" and in  format attributes" it has db_type, It looks like the writer is not excel. Are you sure this is excel writer in 2012 format? I have used this version long back so I dont remember.

 

 

Pratap

 

 

 
It si an old excel writer.

 

It does not have the row-id etc. parameters.

 

 

You need to solve the problem differently.

 

 

Anyway, by the look of your output it seems you are connecting the 3 outputs to a excellwriter. This results in them being appended.

 

You should however first merge the outputs.

 

You have 3 outputs so you need to use 2 featuremergers with mergeattributes "ServiceAreaName" (both the requestor and the Supplier mergeattribute).

 

 

The final output you can connect to the Excellwriter.
addendum.

 

I dont htink it will merge on "ServiceAreaName" = null

 

Bu tyou can fill this row using a tester, testing for a "null" (wich is not a value...)

 

Though by now i forgot how it was called in the old testers.
I guess it will merge becasue "null" is text value in his excel.
Thanks everyone for the feedback. I was able to get the desired results by using the AttributeAccumulator transformer just before the writer. This even merged for the Null values in the ServiceAreaName attribute.

Reply


Community Stats

30,108
Posts
114,295
Replies
37,744
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.