Question

Replace commas within quotes when using CSV Reader

  • 9 November 2017
  • 8 replies
  • 12 views
sunsilk11
Rank
Badge +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
Rank
Userlevel 4

How is "355,280" to be interpreted?

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

dustin
Rank
Userlevel 3
Badge +26

Look into using the StringReplacer to remove unwanted characters.

sunsilk11
Rank
Badge +6

I need to interpret this as 355280

StringReplacer doesn't seem to work for me

david_r
Rank
Userlevel 4

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

Result:

csv2none.fmwt

dustin
Rank
Userlevel 3
Badge +26

I need to interpret this as 355280

StringReplacer doesn't seem to work for me

@sunsilk11 - Could you attach your CSV file?

 

dustin
Rank
Userlevel 3
Badge +26

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
Rank
Userlevel 4
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
Badge +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


Community Stats

28,709
Posts
109,482
Replies
36,556
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.