+3Hi,
I'd like to restructure a csv by separating what is essentially one column of data with a huge text string which makes up constituent attributes and the corresponding data below it into the attribute values. Something akin to the 'text to column' in Excel.
Apologies if I haven't made this very clear, hopefully the csv I've provided will explain further. The top row contains the column headings I wish to split out.
Any suggestions would be most appreciated.
Best answer by mark2catsafe
As a follow up to this...
FME will read the file and split it into fields using the CSV reader. Set the separator to the tab character and check the option to ignore duplicate separators.
However... what you also need to do is remove the quote characters (") from the beginning and end of each line. That's because separator characters inside quotes are taken to be part of the string, not separators. So FME assumes it is a single field of data. Remove the quotes and the data can be read quote easily.
Hope this helps
Mark
+11Hi,
I think, it is better to do in Excel itself. Use text to columns with tab delimiters.
Is there any reason that you are using FME for this activity.
+47This appears to be a tab-separated file. When you create a CSV reader you have the option to specify the separator character in the parameters. Please note that for CSV this can only be done when creating the reader, as it influences the way feature types are set up. You can also specify that the first line of the file holds the attribute names.
There's some tutorials here.
Hope this helps.
You can use either the CSV or the textfile reader, then use an AttributeSplitter and use a TAB as the delimiter, like this:
Make sure to set "Drop emtpy parts" to No.
You will then get a list with all the columns.
Alternatively you could do it in two steps
+5This appears to be a tab-separated file. When you create a CSV reader you have the option to specify the separator character in the parameters. Please note that for CSV this can only be done when creating the reader, as it influences the way feature types are set up. You can also specify that the first line of the file holds the attribute names.
There's some tutorials here.
Hope this helps.
The CSV reader should let you choose tab as a delimiter, but when I did this FME didn't identify the different fields (I'm using 2016.1) - so I filed a PR with our developers to see what they think. I agree with you; it should be possible to read this one fairly easily.
+19Hi @jake, in FME 2016, it might be possible to read the source CSV, write an intermediate CSV, and then read it and write all the records into an Excel file with Dynamic Schema.
This is an experimental dynamic workflow including FeatureWriter and FeatureReader. See also the attached template: experimental-dynamic-textline2xlsxw.fmwt (FME 2016.1)
+5As a follow up to this...
FME will read the file and split it into fields using the CSV reader. Set the separator to the tab character and check the option to ignore duplicate separators.
However... what you also need to do is remove the quote characters (") from the beginning and end of each line. That's because separator characters inside quotes are taken to be part of the string, not separators. So FME assumes it is a single field of data. Remove the quotes and the data can be read quote easily.
Hope this helps
Mark
+8Hi,
I think, it is better to do in Excel itself. Use text to columns with tab delimiters.
Is there any reason that you are using FME for this activity.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
