String to Numeric conversion in csv file

[kartikeya]

Hi All,

I want to perform analytics on a series of excel files( 30 files with same format). I took them as input and converted them into one large CSV file. 

In the CSV file, I have a column 'A' which were stored as strings in their excel format when they actually were Numeric. 

I want to now convert this column 'A' from string to numeric in the CSV file but am not able to do so with either the ToNumber([A]) fuction in the formula tool or by changing the Filetype in the Select tool.

Please advise .

[jdunkerley79 · Accepted solution]

Could you post a sample of your data?

Do the value have , in them for example?

You could try a formula:

ToNumber(REGEX_Replace([A], "[^-.\d]", ""))

Which will remove non-numeric chacters

[kartikeya]

Hi,

Turns out it was indeed some data issue. 

Only a a row line item was incorrect. After removing it , the process worked fine. 

Thank you for the solution though, i can use it for a different problem :p