Solved

I want to let an external source (excel-sheet) determine which attributes to write.

G

Though both the schemamapper and a pythoncaller can accomplish to create, map and 'remove' attributes, the newly mapped attributes are hidden and the removed attributes are still exposed (with only missing values). When I write this to excel I get empty columns but the columns are still created. I don't want to use the hide attributes option in the pythoncaller; I want the attributes to be written and not written to be dependent on a list I read from another file.

icon

Best answer by todd_davis 17 May 2022, 11:04

View original

11 replies

ebygomm
Rank
Userlevel 1
Badge +10

If you have your excel writer set to dynamic, only the attributes that have been set in the schema should be written. Can you share your workflow?

G

If you have your excel writer set to dynamic, only the attributes that have been set in the schema should be written. Can you share your workflow?

Hi Ebygomm,

 

I have attached a sample workspace to illustrate my case. Though multiple threads on this forum state that exposing attributes does not change the dataset itself inherently, I will still write the column called to_remove in the excel and the column to_create is not written. My original dataset does not have the same schema as the one i write so i don't think a dynamic schema writer will do the job. Also, i don't want to state for specific columns/attributes if i want to expose them or not, neither in the script nor in the writer. For the sake of clarity, imagine that i would have a csv or txt file that contains all the attributes that i want to delete or add and that this file would be loaded in the python script normally.

ebygomm
Rank
Userlevel 1
Badge +10

Hi Ebygomm,

 

I have attached a sample workspace to illustrate my case. Though multiple threads on this forum state that exposing attributes does not change the dataset itself inherently, I will still write the column called to_remove in the excel and the column to_create is not written. My original dataset does not have the same schema as the one i write so i don't think a dynamic schema writer will do the job. Also, i don't want to state for specific columns/attributes if i want to expose them or not, neither in the script nor in the writer. For the sake of clarity, imagine that i would have a csv or txt file that contains all the attributes that i want to delete or add and that this file would be loaded in the python script normally.

You will need your workspace to edit the schema based on the csv file with a list of attributes to keep/remove, and then you can use this schema with a dynamic setup on the writer. I may be able to provide a quick example later

todd_davis
Rank
Userlevel 1
Badge +12

Hi,

 

Whatever the case, this certainly can be done. In your heading you say "I want to let an external source (excel-sheet) determine which attributes to write". If it is that simple, then you would use a "Reader as A Resource" and set your output featuretype as Dynamic, schema Source as the reference excel spreadsheet, etc

 

If you instead are creating the schema dynamically based on what is the existing feature, you could create your schema utilising the workflows described to create a destination schema.

 

 

image.png

G

I think I get what you are saying but am not sure yet about how to work it out. In this case I want to write to an ArcSDE database using an excel sheet as a template. But is it possible to use the excel sheet as a resource to set the writer schema concerning data types and field lengths?

todd_davis
Rank
Userlevel 1
Badge +12

I think I get what you are saying but am not sure yet about how to work it out. In this case I want to write to an ArcSDE database using an excel sheet as a template. But is it possible to use the excel sheet as a resource to set the writer schema concerning data types and field lengths?

You are right. Excel is pretty terrible as setting data types and field lengths and you would probably just get all character fields in SDE. It would work but you date, integers, doubles probably wouldn't work.

 

I am assuming that you excel reference just is a standard excel table where column 1 hold the first field name, column 2 holds the second field name etc. Rather than column 1 contains all the field names, column 2 holds data type., column 3 holds the length.

 

 

G

You are right. Excel is pretty terrible as setting data types and field lengths and you would probably just get all character fields in SDE. It would work but you date, integers, doubles probably wouldn't work.

 

I am assuming that you excel reference just is a standard excel table where column 1 hold the first field name, column 2 holds the second field name etc. Rather than column 1 contains all the field names, column 2 holds data type., column 3 holds the length.

 

 

Well I actually wanted to have the following:

column 1: name of column in source, if any

column 2: action to take (remove or create or change name)

column 3: new name if applicable

column 4: data type

column 5: field length

 

So basically what the attributemanager looks like but with a bit extra for defining columns 4 and 5. Do you reckon this is doable?

todd_davis
Rank
Userlevel 1
Badge +12

You are right. Excel is pretty terrible as setting data types and field lengths and you would probably just get all character fields in SDE. It would work but you date, integers, doubles probably wouldn't work.

 

I am assuming that you excel reference just is a standard excel table where column 1 hold the first field name, column 2 holds the second field name etc. Rather than column 1 contains all the field names, column 2 holds data type., column 3 holds the length.

 

 

oh yes it is.

Here is an example of utilising a spreadsheet containing name, data type and field length. You will notice I am using fme data type. There are also native data types (native_data_type) like that for SDE, the provide further clarification as an integer. Hint: Use a featurereader to read a SDE table and look at the info coming out of the schema port to see how this is all structure and it will inform you of what you might need when writing a schema out dynamically.

 

But here is the example, and you should be able to build in some of the other logic you mentioned.

1 Attachment
G

You are right. Excel is pretty terrible as setting data types and field lengths and you would probably just get all character fields in SDE. It would work but you date, integers, doubles probably wouldn't work.

 

I am assuming that you excel reference just is a standard excel table where column 1 hold the first field name, column 2 holds the second field name etc. Rather than column 1 contains all the field names, column 2 holds data type., column 3 holds the length.

 

 

Hi Todd,

 

excellent, thank you so much! This solved my issues indeed and I did not know you could simply set the schema like this.

 

A follow-up question perhaps: How is it possible that though the schema is defined dynamically that I can still change the schema in the User Attributes tab in the writer? It seems this overwrites the schema that I have set and does not automatically inherit when Ichange the used excel file. In my case specifically I had a capital in the column name first and later changed this to lowercase. When writing to an ArcSDE database it gave an error because it saw two of the same columns.

todd_davis
Rank
Userlevel 1
Badge +12

You are right. Excel is pretty terrible as setting data types and field lengths and you would probably just get all character fields in SDE. It would work but you date, integers, doubles probably wouldn't work.

 

I am assuming that you excel reference just is a standard excel table where column 1 hold the first field name, column 2 holds the second field name etc. Rather than column 1 contains all the field names, column 2 holds data type., column 3 holds the length.

 

 

In most cases, you will want to remove any attributes in the User Attributes section when you have set it as dynamic schema. Any attributes in the User Attributes will also be created.

 

But there are times when you will be so glad it has that extra capability. So for instance, in your case you are have the output schema defined in an external schema, but you may want to add a couple of extra attributes to your SDE table. Maybe you want to set a "initialdate" so you know when the table was created initially (let says you are just using the timestamp transformer in FME to get the datetime it was run). You could just set that in User Attribute and that field would be created in your SDE table without it having to be set in the external excel file.

 

There are also a couple of scenarios that I have previously used it for other needs, but they are complex to explain. But if you come across them, you'll be thankful that Safe included that possibility.

 

G

You are right. Excel is pretty terrible as setting data types and field lengths and you would probably just get all character fields in SDE. It would work but you date, integers, doubles probably wouldn't work.

 

I am assuming that you excel reference just is a standard excel table where column 1 hold the first field name, column 2 holds the second field name etc. Rather than column 1 contains all the field names, column 2 holds data type., column 3 holds the length.

 

 

Thank you, that's a great tip and I will indeed start removing the user attributes first.

Reply


Community Stats

28,650
Posts
109,262
Replies
36,523
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.