One Star

Convert Excel Currency Formated Fields to Float

I am using tFileInputExcel to read an excel file that has a column that is formated with a $. Is there a more elegant solution than using the following method to convert this field to a float?
Float.parseFloat(StringHandling.EREPLACE(StringHandling.EREPLACE(row1.Your_Price_Delivered,"\"\\$\"",""), "\\,", ""))

I was thinking there was some way in the tFileInputExcel schema that I could just ignore the formating excel applies to the currency field. I tried the Read real values for numbers option, but that didnt seem to work.
Thanks!
Nick
4 REPLIES
One Star

Re: Convert Excel Currency Formated Fields to Float

This is strange, I created another job using the same excel file and the $ are missing when I perform a transformation. This is what I was hoping for and expecting. Any ides why this would happen?
One Star

Re: Convert Excel Currency Formated Fields to Float

I think I found the problem. If the excel file is saved in excel, the data currency formated data is interpreted correctly. If the file is saved in Open Office Calc as an excel file, the "$" come across in the data.
One Star

Re: Convert Excel Currency Formated Fields to Float

I think you need to have an HTML in order for you to convert excel currency formated Fields to float.

Re: Convert Excel Currency Formated Fields to Float

Just follow this five steps I hope you will find the solution.
1) Select the currency you want to convert to.
2) Find the current conversion rate. Sites such as Yahoo's currency converter will list the latest currency conversion rate for the various international currencies. Several sites offer this service, so perform an Internet search for the site you feel most comfortable with.
3) Open Microsoft Excel. Label the first three columns in the Excel spreadsheet as "Current Currency," "Currency Conversion Factor" and "Converted Currency." You don't have to label the columns as indicated here, but for instructional purposes, it makes it easier to identify the column's contents. You can label them A, B and C if you'd like.
4) Insert the amount of the currency you wish to convert in the first column (Current Currency). If you're converting $453 US dollars into another currency, insert that amount in the first column.
5) Place the currency conversion factor in the second column.
6) Create a formula in the third column. Insert this formula in the third column "+column1/cell1 * +colum2/cell1." In this example, assuming you did not skip a row after your title, your dollar amount will be in A2 and your conversion amount will be in B2; therefore, your formula will read "+A2*+B2." Be sure to hit the "Enter" key once you've put in the formula. It will automatically convert the currency.
7)Add formatting. If you want your converter to look nice, format the column by bolding, underlining or adding color.
Find more solutions for excel sheet as here: http://www.ehow.com/how_2238845_create-currency-converter-microsoft-excel.html