0

When I import a .CSV file into Excel, how do I get all cells which contain a number to be formatted as currency? Currently, if the value for the cell contains the dollar sign ($), it becomes formatted as currency, but remains as plain text if the dollar sign is absent. I also wish to ignore cells which have text in them, as they should remain as plain text. Examples: 1 -> $1.00, One -> One, 1.0 -> $1.00, "Hello World" -> "Hello World"

The Data Import tool in Excel does not work for my scenario. It lacks the option to format as currency when importing, only giving the options of "Number, Date, or General".

Adam
  • 125

1 Answers1

1

Here is a solution using csv2odf that can automate the formatting in Excel:

Create a spreadsheet template in Excel with these specifications:

  1. Insert column titles with the same number of columns as the csv. (If you want to use titles from the csv file, add the -H option to the command below.)

  2. Add one sample row of data on the second row. Use dummy numbers where numbers will go and dummy text where text will go. Format the text/numbers however you want, format numbers with currency where needed.

  3. Save the template as xlsx (xls will not work).

Run this command:

csv2odf yourdata.csv yourtemplate.xlsx output.xlsx

Your data will be data will be formatted in the output using formatting from the template. You can use the same template each time to automate the conversion.

Note the program needs Python to run.

Larry1833681
  • 11