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
  • 126
  • 1
  • 5
  • possible duplicate of [How do I get excel to not mess around with the formatting of a .csv file](http://superuser.com/questions/304972/how-do-i-get-excel-to-not-mess-around-with-the-formatting-of-a-csv-file) – Excellll Jun 25 '15 at 17:16
  • @Excellll it's not because I don't want Excel to ignore formatting, I want it to format specifically. Also, I don't wish to manually change the formatting of 100+ columns and rows – Adam Jun 25 '15 at 17:24
  • Have you tried VBA to format columns for you after the import? – Engineer Toast Jun 25 '15 at 17:40
  • @EngineerToast I have. I can get the columns to format as needed, but this is no different than manually changing the format. I need the cells to automatically be formatted as currency upon import. – Adam Jun 25 '15 at 17:59
  • Have you tried to modify the columns before importing? That is 1 -> $1 or 1$? – ott-- Jun 25 '15 at 18:21
  • @ott-- that would require me to manually modify each cell before importing, which is highly impractical due to the number of cells – Adam Jun 25 '15 at 18:23
  • No, you don't need to do it manually. – ott-- Jun 25 '15 at 18:25
  • @ott-- how else would I modify the columns to have the .CSV read `$1` instead of `1`? – Adam Jun 25 '15 at 18:36
  • 1
    Excel won't apply number formatting to cells containing text, so after importing your data just select it (CTRL+A) and apply the format you need. – Máté Juhász Jun 25 '15 at 18:43

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
  • 2