Solved

CSV "Flat file" Restructure

  • 22 June 2016
  • 8 replies
  • 35 views
J
Badge +3

Hi,

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.

icon

Best answer by mark2catsafe 13 July 2016, 23:40

View original
1 Attachment

8 replies

pratap
Rank
Badge +2

Hi,

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.

redgeographics
Rank
Userlevel 5
Badge +25

This 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.

david_r
Rank
Userlevel 4

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

  1. Workspace with Textfile reader/writer and an AttributeTrimmer to get rid of the quotation marks
  2. Another workspace to read the now regular CSV file using TAB as delimiter
M
Badge +5

This 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.

takashi
Rank
Userlevel 2
Badge +17

Hi @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)

J
Badge +3

Hi All,

Apologies for the delay and thanks a lot for your ideas. Plenty to mull over there!

Jake

M
Badge +5

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

tim_wood
Rank
Badge +7

Hi,

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.

Why would anyone NOT want to use FME for every activity? ;-)

 

 

Reply


Community Stats

28,681
Posts
109,408
Replies
36,542
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.