Skip to main content
Solved

Is there any solution to remove fields with emply attribues in excel

mehar
Forum|alt.badge.img

Paid_Services11.xlsx( my problem is i have an excel which is about the survey of stations, in some stations there are 3 restaurents and some had 4 restaurents and some only 1, but in survey form we gave questions for maximum 10 restaurents in mdc giscloud.when we export the data in excel it shows all 10 restaurents fields even if they filled for less . i want to remove the fields with no attributes..

sample file is attached .. thanks

Best answer by jovitaatsafe

Hi @mehar,

I was able to get a successful result in removing attributes/fields with empty values using @takashi's answer where he has also attached a workspace template. Here are the changes that you need to make to his provided workspace to get the result you are looking for:

  1. Replace the reader with an Excel reader pointed at your file
  2. Open the Aggregator and replace 'Attributes to Concatenate' by deselecting all and select all again. Make sure not to select the old attributes highlighted in red.
  3. Next, open the PythonCaller and where it's highlighted red under 'Attributes to Hide', deselect the existing ones and select all on your attributes.
  4. Go into the FeatureReader and re-read the excel file you provided. Remember to change the Format and the file path
  5. Replace the shapefile writer with an Excel writer (if excel is your output). Remember to set the Sheet Definition to 'Dynamic (Advanced)'. I also like to set the Drop Existing sheets and Truncate to yes in case you run the workspace multiple times for testing
  6. Open up the writer feature parameters after you've added the writer. In Schema Sources, set it to 'Schema from Schema Feature'

6-schema-sources

 

Press OK and set the 'Schema Definition Name' to fme_feature_type.

 

It should look something like this:

7-writer-params

 

Note that in the output the X and Y column appear to be reordered last in the Inspector (not sure why), but the actual excel output looks ordered correctly.

While the workspace is from some time ago, I was able to get my result without upgrading any of the workspace, I just opened it up in FME 2019.1. Hope that works for you as well (and thank you Takashi for a wonderful solution)!

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

6 replies

jovitaatsafe
Safer
Forum|alt.badge.img+11

Hi @mehar,

 

 

It looks like there are some great ideas for removing empty attributes from these two previous Q&A;s.

 

 

Xiaomeng's answer suggests using the AttributeExploder and and filtering out only values that are not exmpty (ex. Tester to see if an attribute value exists).

 

 

And takashi's answers in this question offers a few options including the NullAttributeMapper, and a script for the PythonCaller. In another Q&A taskashi also has a workspace template for a different solution.
mehar
Forum|alt.badge.img
  • mehar
  • Author
  • July 11, 2019

@jovitaatsafe thanks for the reply but attribute exploder and nullattributemapper didnot work with me,

i want to delete fields with empty attributes .. i hope if you check the attached excel..

thanks

gio
Contributor
Forum|alt.badge.img+15
  • gioContributor
  • Contributor
  • July 11, 2019

@mehar

Like @jovitaatsafe said, this has been asked and answered many a time.

 

I assume you mean empty row and not considering the first five columns.

 

Here is a tiny script (which is using attribute exploder)

Excel count non empty cols per row forumMehar.fmwt

 

 

added image

Greets.

 

mehar
Forum|alt.badge.img
  • mehar
  • Author
  • July 11, 2019
gio wrote:

@mehar

Like @jovitaatsafe said, this has been asked and answered many a time.

 

I assume you mean empty row and not considering the first five columns.

 

Here is a tiny script (which is using attribute exploder)

Excel count non empty cols per row forumMehar.fmwt

 

 

added image

Greets.

 

hi @gio

thanks for script but the columns with no attributes are not deleted i mean i need to delete the columns which have no attributes according to every row there is information about the station

by running the script it didnot deleted the columns with no attributes

jovitaatsafe
Safer
Forum|alt.badge.img+11
  • jovitaatsafeSafer
  • Safer
  • Best Answer
  • July 12, 2019

Hi @mehar,

I was able to get a successful result in removing attributes/fields with empty values using @takashi's answer where he has also attached a workspace template. Here are the changes that you need to make to his provided workspace to get the result you are looking for:

  1. Replace the reader with an Excel reader pointed at your file
  2. Open the Aggregator and replace 'Attributes to Concatenate' by deselecting all and select all again. Make sure not to select the old attributes highlighted in red.
  3. Next, open the PythonCaller and where it's highlighted red under 'Attributes to Hide', deselect the existing ones and select all on your attributes.
  4. Go into the FeatureReader and re-read the excel file you provided. Remember to change the Format and the file path
  5. Replace the shapefile writer with an Excel writer (if excel is your output). Remember to set the Sheet Definition to 'Dynamic (Advanced)'. I also like to set the Drop Existing sheets and Truncate to yes in case you run the workspace multiple times for testing
  6. Open up the writer feature parameters after you've added the writer. In Schema Sources, set it to 'Schema from Schema Feature'

6-schema-sources

 

Press OK and set the 'Schema Definition Name' to fme_feature_type.

 

It should look something like this:

7-writer-params

 

Note that in the output the X and Y column appear to be reordered last in the Inspector (not sure why), but the actual excel output looks ordered correctly.

While the workspace is from some time ago, I was able to get my result without upgrading any of the workspace, I just opened it up in FME 2019.1. Hope that works for you as well (and thank you Takashi for a wonderful solution)!

mehar
Forum|alt.badge.img
  • mehar
  • Author
  • July 14, 2019
jovitaatsafe wrote:

Hi @mehar,

I was able to get a successful result in removing attributes/fields with empty values using @takashi's answer where he has also attached a workspace template. Here are the changes that you need to make to his provided workspace to get the result you are looking for:

  1. Replace the reader with an Excel reader pointed at your file
  2. Open the Aggregator and replace 'Attributes to Concatenate' by deselecting all and select all again. Make sure not to select the old attributes highlighted in red.
  3. Next, open the PythonCaller and where it's highlighted red under 'Attributes to Hide', deselect the existing ones and select all on your attributes.
  4. Go into the FeatureReader and re-read the excel file you provided. Remember to change the Format and the file path
  5. Replace the shapefile writer with an Excel writer (if excel is your output). Remember to set the Sheet Definition to 'Dynamic (Advanced)'. I also like to set the Drop Existing sheets and Truncate to yes in case you run the workspace multiple times for testing
  6. Open up the writer feature parameters after you've added the writer. In Schema Sources, set it to 'Schema from Schema Feature'

6-schema-sources

 

Press OK and set the 'Schema Definition Name' to fme_feature_type.

 

It should look something like this:

7-writer-params

 

Note that in the output the X and Y column appear to be reordered last in the Inspector (not sure why), but the actual excel output looks ordered correctly.

While the workspace is from some time ago, I was able to get my result without upgrading any of the workspace, I just opened it up in FME 2019.1. Hope that works for you as well (and thank you Takashi for a wonderful solution)!

hi @jovitaatsafe

hope you are well and good in health..

thanks for the solution and guidelines

it work for me this time ..

 

thankyou very much for your precious time

Reply


Helpful Members This Week

Community Stats

31,000
Posts
117,492
Replies
38,778
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.