SSIS remove $ format from csv

Question

I have a csv file with multiple columns that are formatted as currency, what is the best way to remove the formatting but keeping the values. I would like to automate the process if possible.

I tried using the data conversion tool but I must be doing something wrong.

This is what the raw file looks like in NotePad. :

1-2-1,2020,1,1,Ada,2,County,1,Ada County," $144,552,433 ",," $27,710 "," $57,836 ",0.00,,,,,,," $12,251,223

Excel Snip

Answer 1

You can add a Derived Column and apply REPLACE SSIS expression for the currency column to remove $

REPLACE(ColumnName,'$','')

Then map the output to destination column.

Answer 2

The raw data clearly shows the currency fields as text, with a $ character included and surrounded by blanks. If you open this CSV file with Excel, Excel will do some built-in interpretation of the values and translate a text containing a $ sign and numbers into a numeric value that it formats as a currency in the Excel grid. The underlying value of that Excel file is still a number.

If you want to avoid that, load the data via Get&Transform on the Data ribbon, and clean up the data in Power Query before you load it into the Excel grid.