Hello everyone, I am extremely new to FME and I am trying to do a basic task and take a column in excel and turn it into an inline csv document. Any help would be appreciated, even links to know forum answers I can't find yet.
Best answer by jovitaatsafe
Ok I have gotten some progress, thank you both. I am going to attach an image of the work flow and also the csv file. I want to have the numbers in a single comma separated line for an end user and I am stumped right now.
Hi @tim_t, glad to see that you've made some progress! While FME allows for many different ways of doing things (no single correct method), I think it'll be a bit easier to use the AttributeTransposer for this one. Here's what the workflow could like:
- Read in excel data, make sure you check the starting value or row that FME reads so you don't lose the first serial number value as an attribute name (ex. data starts on row 1 so 'Field Names Row' is 0).
- Use an AttributeTransposer to transpose the column of values into a single row of attributes. Check the Feature Information Window at this step (the icon with a note and a yellow information icon on the visual preview) to see that the attributes are there but just not exposed yet
- Expose attributes so they are visible in the table. Quick tip: Run the transformation to (and including) the AttributteTransposer so you can hit Import > Import From Feature Caches and select all in your AttributeExposer
- Remove the original colA value to keep from writing it
- Write to CSV ensuring the 'Write Field Names Row' is set to No, otherwise it will write out two sets serial numbers: the field name and the value
Below is a screenshot that shows the described workflow. I only used a small subset of the serial number data to show it. Output is a CSV that is one line separated by commas.
I think the tricky part is the AttributeTransposer. It looks like nothing has happened immediately in the output, but the information window should look something like this:
+11- Safer
Hi @tim_t, welcome to FME and the Community! I just wanted to add another resource here for the reader/writer side. We have an article that shows an example of reading in Excel and writing out to CSV that might be helpful to check out in addition to the transformer Mark recommended: Converting Tabular (Non-Spatial) and Spatial Data to CSV
Hi @tim_t, welcome to FME and the Community! I just wanted to add another resource here for the reader/writer side. We have an article that shows an example of reading in Excel and writing out to CSV that might be helpful to check out in addition to the transformer Mark recommended: Converting Tabular (Non-Spatial) and Spatial Data to CSV
Thank you, I will check that out.
Ok I have gotten some progress, thank you both. I am going to attach an image of the work flow and also the csv file. I want to have the numbers in a single comma separated line for an end user and I am stumped right now.
+11- Safer
- Best Answer
Ok I have gotten some progress, thank you both. I am going to attach an image of the work flow and also the csv file. I want to have the numbers in a single comma separated line for an end user and I am stumped right now.
Hi @tim_t, glad to see that you've made some progress! While FME allows for many different ways of doing things (no single correct method), I think it'll be a bit easier to use the AttributeTransposer for this one. Here's what the workflow could like:
- Read in excel data, make sure you check the starting value or row that FME reads so you don't lose the first serial number value as an attribute name (ex. data starts on row 1 so 'Field Names Row' is 0).
- Use an AttributeTransposer to transpose the column of values into a single row of attributes. Check the Feature Information Window at this step (the icon with a note and a yellow information icon on the visual preview) to see that the attributes are there but just not exposed yet
- Expose attributes so they are visible in the table. Quick tip: Run the transformation to (and including) the AttributteTransposer so you can hit Import > Import From Feature Caches and select all in your AttributeExposer
- Remove the original colA value to keep from writing it
- Write to CSV ensuring the 'Write Field Names Row' is set to No, otherwise it will write out two sets serial numbers: the field name and the value
Below is a screenshot that shows the described workflow. I only used a small subset of the serial number data to show it. Output is a CSV that is one line separated by commas.
I think the tricky part is the AttributeTransposer. It looks like nothing has happened immediately in the output, but the information window should look something like this:
Hi @tim_t, glad to see that you've made some progress! While FME allows for many different ways of doing things (no single correct method), I think it'll be a bit easier to use the AttributeTransposer for this one. Here's what the workflow could like:
- Read in excel data, make sure you check the starting value or row that FME reads so you don't lose the first serial number value as an attribute name (ex. data starts on row 1 so 'Field Names Row' is 0).
- Use an AttributeTransposer to transpose the column of values into a single row of attributes. Check the Feature Information Window at this step (the icon with a note and a yellow information icon on the visual preview) to see that the attributes are there but just not exposed yet
- Expose attributes so they are visible in the table. Quick tip: Run the transformation to (and including) the AttributteTransposer so you can hit Import > Import From Feature Caches and select all in your AttributeExposer
- Remove the original colA value to keep from writing it
- Write to CSV ensuring the 'Write Field Names Row' is set to No, otherwise it will write out two sets serial numbers: the field name and the value
Below is a screenshot that shows the described workflow. I only used a small subset of the serial number data to show it. Output is a CSV that is one line separated by commas.
I think the tricky part is the AttributeTransposer. It looks like nothing has happened immediately in the output, but the information window should look something like this:
Thank you @jovitaatsafe ! I'll try working on this and see where I get. Very much appreciated.
@tim_t If you just want a concatenated list of the serial numbers then the Aggregator should do it for you.
@Mark Stoakes Thank you, the previous response by @jovitaatsafe got me to where I needed to be. Thank you all for the input and help!
+11- Safer
Hi @tim_t, glad to see that you've made some progress! While FME allows for many different ways of doing things (no single correct method), I think it'll be a bit easier to use the AttributeTransposer for this one. Here's what the workflow could like:
- Read in excel data, make sure you check the starting value or row that FME reads so you don't lose the first serial number value as an attribute name (ex. data starts on row 1 so 'Field Names Row' is 0).
- Use an AttributeTransposer to transpose the column of values into a single row of attributes. Check the Feature Information Window at this step (the icon with a note and a yellow information icon on the visual preview) to see that the attributes are there but just not exposed yet
- Expose attributes so they are visible in the table. Quick tip: Run the transformation to (and including) the AttributteTransposer so you can hit Import > Import From Feature Caches and select all in your AttributeExposer
- Remove the original colA value to keep from writing it
- Write to CSV ensuring the 'Write Field Names Row' is set to No, otherwise it will write out two sets serial numbers: the field name and the value
Below is a screenshot that shows the described workflow. I only used a small subset of the serial number data to show it. Output is a CSV that is one line separated by commas.
I think the tricky part is the AttributeTransposer. It looks like nothing has happened immediately in the output, but the information window should look something like this:
@tim_t, no problem! I was just chatting with @Mark Stoakes and wanted to be sure to warn you that doing it this way separates out each serial number to be a new attribute, is that what you want?
Can you tell me a bit more about the overview of what you're trying to do? What program will be consuming the data and do you plan to read it back into FME?
If you are planning to read the CSV back into FME, you'll notice that all the values are a single row like in the screenshot above, which could be difficult to work with later if you want to do more with it. So Mark's suggestion with the Aggregator may be more appropriate to get a single value with all the serial numbers separated by comma (and then write this out to a Text Line Writer instead of the CSV writer, changing the extension to .csv). Both methods will yield a line of CSV, but the Aggregator makes it easier to work with in FME later so the method depends on what your overall goal is for your project is.
@Mark Stoakes Thank you, the previous response by @jovitaatsafe got me to where I needed to be. Thank you all for the input and help!
@Mark Stoakes Yes it is, I needed to have all the serial numbers have the first 2 leading characters removed (28). Those 2 digits never change. Then I needed it in the csv already formatted so one of our folks could just copy and paste into another app for RMA's of meters.
Reply
Helpful Members This Week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.