Skip to main content
Solved

CSV content from SQL field to Features

mlupien
Contributor
Forum|alt.badge.img+7

Hi Community,

I need to extract a CSV Style content from a SQL field of an SQL table.

It is stored into a single field.

The content looks like this:

Classification,Fonction
03-1,300
03-2,301
AA-10,C-1000
AA-12,C-1000
AA-15,C-1000
AA-17,C-1000
AA-20,C-1000
AA-22,C-1000
AA-25,C-1000
AA-27,C-1000
AA-30,C-1000

As you can see, the first line gives me the Attributes names and the following lines gives me the values

What would be the best way to turn this into multiple features using the first line as the Attributes names ?

Thank you

Best answer by nielsgerrits

One way to do this:

  • Explode lines to features by newline.
  • Explode the attributes to features by comma.
  • Split header and data.
  • Merge headers to data.
  • Create attributes from headers and data.
  • Merge back into lines.
  • Expose created attributes.

Yes, you need to expose the attributes. No this can not be done dynamic. :-)

The only dynamic thing you can do is to create a schema feature using the SchemaScanner to write the result with dynamic generated attributenames.

View original
danilo_fme
1 person upvoted this
  • danilo_fme
    danilo_fme
Did this help you find an answer to your question?

6 replies

nielsgerrits
VIP
  • nielsgerritsVIP
  • Best Answer
  • May 2, 2024

One way to do this:

  • Explode lines to features by newline.
  • Explode the attributes to features by comma.
  • Split header and data.
  • Merge headers to data.
  • Create attributes from headers and data.
  • Merge back into lines.
  • Expose created attributes.

Yes, you need to expose the attributes. No this can not be done dynamic. :-)

The only dynamic thing you can do is to create a schema feature using the SchemaScanner to write the result with dynamic generated attributenames.

1 Attachments
geomancer
danilo_fme
2 people upvoted this
  • geomancer
    geomancer
  • danilo_fme
    danilo_fme
jkr_wrk
Influencer
Forum|alt.badge.img+28
  • jkr_wrkInfluencer
  • May 2, 2024

Solution without a featureMerger:

💡 Did you know... The FeatureWriter is more intuitive. 😏
nielsgerrits
geomancer
danilo_fme
3 people upvoted this
  • nielsgerrits
    nielsgerrits
  • geomancer
    geomancer
  • danilo_fme
    danilo_fme
mlupien
Contributor
Forum|alt.badge.img+7
  • mlupienContributor
  • Author
  • Contributor
  • May 2, 2024
nielsgerrits wrote:

One way to do this:

  • Explode lines to features by newline.
  • Explode the attributes to features by comma.
  • Split header and data.
  • Merge headers to data.
  • Create attributes from headers and data.
  • Merge back into lines.
  • Expose created attributes.

Yes, you need to expose the attributes. No this can not be done dynamic. :-)

The only dynamic thing you can do is to create a schema feature using the SchemaScanner to write the result with dynamic generated attributenames.

Great ! Thank you ! 

For now, I can manually expose the attributes. I will give a try with the SchemaScanner for sure !

danilo_fme
1 person upvoted this
  • danilo_fme
    danilo_fme
mlupien
Contributor
Forum|alt.badge.img+7
  • mlupienContributor
  • Author
  • Contributor
  • May 2, 2024
jkr_wrk wrote:

Solution without a featureMerger:

Hi, @jkr_wrk , 

The concept looks interesting, but I can’t figure out what your “AttributeSplitter_3” is doing. What would be the parameters ?

and how do you link the link the header with the data after ?

Thank you

danilo_fme
1 person upvoted this
  • danilo_fme
    danilo_fme
jkr_wrk
Influencer
Forum|alt.badge.img+28
  • jkr_wrkInfluencer
  • May 4, 2024

I thought AttributeSplitter_3 was simple but after a second look I see I had to puzzle that myself as well.

It creates the _list_headers{} from the _list{0} . This _list_headers{} is used in AttributeCreator_3 to make it the AttributeName. This is a bit tricky because you put @Value(_list_headers{@Value(_element_index}}) in the AttributeName field.

_element_index is the index of the AttributeSplitter_2 which splitted the value.

 

💡 Did you know... The FeatureWriter is more intuitive. 😏
danilo_fme
1 person upvoted this
  • danilo_fme
    danilo_fme
mlupien
Contributor
Forum|alt.badge.img+7
  • mlupienContributor
  • Author
  • Contributor
  • May 5, 2024
jkr_wrk wrote:

I thought AttributeSplitter_3 was simple but after a second look I see I had to puzzle that myself as well.

It creates the _list_headers{} from the _list{0} . This _list_headers{} is used in AttributeCreator_3 to make it the AttributeName. This is a bit tricky because you put @Value(_list_headers{@Value(_element_index}}) in the AttributeName field.

_element_index is the index of the AttributeSplitter_2 which splitted the value.

 

OMG ! thank you @jkr_wrk for the explantation, I was not able to figure out by myself !

danilo_fme
jkr_wrk
2 people upvoted this
  • danilo_fme
    danilo_fme
  • jkr_wrk
    jkr_wrk

Reply


Helpful Members This Week

Community Stats

30,942
Posts
117,253
Replies
38,742
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.