CSV Input causes comma number issues

[Pascal_R]

Hi Team,

Is there a way to tell alteryx that as an example the 1,150 are 1'150 and not 1?

Thank you ,

Pascal

[L_T]

Removing punctuation with the data cleansing tool should work 

[apathetichell]

Hi,

Is this a comma delimited data source where one specific column has a comma in the field information, or are these the only commas?

As a workaround you can combine the two fields created by the commas using a formula to add [field1]+"'"+[field2] - but all three of these field will have to be strings(ie the new field, [field1], and [field2]) or you'll have to use tostring. If you don't want that it's probably easier if you can post some additional data about your .csv and your other fields.

[MattBSlalom]

It sounds like your input CSV file does not have that data field surrounded by a qualifier like double quotes (") to escape that comma character as part of the single field's value.  Is it possible to update the process that created the source file to wrap those values in quotes?

[danilang]

Hi @Pascal_R 

Judging from the colours shown underneath column names of your two screenshot, it looks like you're converting from a string to a number using either a select tool or a Tonumber([Total Outs]) function in a formula tool.  Both of these stop converting when the first non-numeric character is found that isn't "-" or ".".  To get around this, remove the commas using Replace([Total Outs],",","") on the string field before the conversion

Dan 

[apathetichell]

It's worth checking to make sure you have apostrophe set up as the thousand separator - you can do that in user settings/localization.

[Pascal_R]

Hi @apathetichell @danilang @MattBSlalom @L_T 

Please see attached example data on specific items where I have the issue due to the "," in the numbers above 1k + the Alteryx flow I use.

Is that maybe something I should already fix in SAP? In below screenshot you see how transform the csv data.

Please let me know if you have any other ideas? 

[MattBSlalom]

What version and what localization are you using?  When I plug your sample file into your sample workflow, I'm not seeing the behavior you described with those numeric values.  (I'm on 2021.1 with English localization FYI.)

[Shalz]

Hi,

  Instead of Text to columns, you can use RegEx and split by field type. That would be much simpler.

[Shalz · Accepted solution]

or you can do this...