Skip to main content
Question

Replace commas within quotes when using CSV Reader

sunsilk11
Contributor
Forum|alt.badge.img+6

Hi I have a CSV file with X,Y coordinates. I am loading this to a point feature class. However the XY coordinates sometimes come in quotes and have a comma, e.g.

GLOUCESTERSHIRE,ST BRAIVELS COMMON,GL15 6SD,"355,280","203,644"

Any suggestions what's the best way to handle this when using the CSV reader.

8 replies

david_r
Celebrity
  • david_rCelebrity
  • November 9, 2017

How is "355,280" to be interpreted?

As x=355.28 or as (x=355, y=280)?

dustin
Influencer
Forum|alt.badge.img+31
  • dustinInfluencer
  • Influencer
  • November 9, 2017

Look into using the StringReplacer to remove unwanted characters.

sunsilk11
Contributor
Forum|alt.badge.img+6
  • sunsilk11Contributor
  • Author
  • Contributor
  • November 9, 2017

I need to interpret this as 355280

StringReplacer doesn't seem to work for me

david_r
Celebrity
  • david_rCelebrity
  • November 9, 2017

Use a StringReplacer to replace the comma "," with an empty string.

Result:

csv2none.fmwt

dustin
Influencer
Forum|alt.badge.img+31
  • dustinInfluencer
  • Influencer
  • November 9, 2017
sunsilk11 wrote:

I need to interpret this as 355280

StringReplacer doesn't seem to work for me

@sunsilk11 - Could you attach your CSV file?

 

dustin
Influencer
Forum|alt.badge.img+31
  • dustinInfluencer
  • Influencer
  • November 9, 2017
david_r wrote:

Use a StringReplacer to replace the comma "," with an empty string.

Result:

csv2none.fmwt

Similarly, you can use regular expression to remove both the quotes and comma at the same time, if that's what you are needing.

 

david_r
Celebrity
  • david_rCelebrity
  • November 9, 2017
dustin wrote:
Similarly, you can use regular expression to remove both the quotes and comma at the same time, if that's what you are needing.

 

The CSV reader removes the double quotes by default, so it shouldn't be necessary. See the template workspace I uploaded.

 

 

1
Forum|alt.badge.img+1

If you are more comfortable with Excel, then you could also open the CSV file in Excel and change the data type of the X and Y coordinate fields to be number without a comma separator, then save it as CSV again,.. and read it into FME using the CSV reader without having to worry about it.

Reply


Helpful Members This Week

Community Stats

31,317
Posts
118,765
Replies
38,907
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.