Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm generating CSV files from a script and then want to look at them with Excel. I noticed Excel does a smart job of identifying money (by the $) and dates, and formats those cells accordingly. However, with my percentage column it wasn't so smart as to remove the % from the values and yet render it as percentages (as it did with $). Also, I have columns where I want to format them to show a fixed number of decimals, and despite padding the values it discards them.

Is there a way I can give Excel more clues about these last two types so that it will format my data as I want? I don't want to use the Wizard or manually format each time.

share|improve this question

3 Answers 3

Import the csv file manually using Get External Data. Look for an advanced tab as you go through the import steps. You will be able to specify details for the field type. You can save the import specifications once you have set it up the way you like it and then apply that import spec to future imports.

If you are familiar with VBA you could easily write a quick macro to import the file and apply the import spec. If you are not that tech,you could try recording the steps with the macro recorder. Newer versions (2007+ ?) prompt you to save the import steps if you wish and then offer recent imports on the Get External data ribbon.

share|improve this answer

In Excel, it is possible to save text import settings (as a "template") and reuse them with any text file later. A blogpost by Excel MVP Jan Karel Pieterse describes this in detail: http://blogs.office.com/2010/06/10/importing-text-files-in-an-excel-sheet/

The "text import template" is automatically created when you are done with Text Import Wizard. Make sure that you save your workbook to keep the "template".

To reuse it with another (or the same) text file:

  1. Click any cell within your sheet that holds (used to hold) data of a previous import.
    • Excel 2007/2010: Click Data -> Refresh All dropdown -> Refresh
    • Excel 2003 and older: Click Data -> Refresh Data

If you use this "template" to always import a file of the same name and location, you can customize the "template" so that Excel does not prompt you for a file name each time you hit the Refresh button.

During the text import, click Properties... in the Import Data dialog and untick Prompt for file name on refresh in the External Data Range Properties dialog. You can also edit that later in the Connection Properties dialog.

I would post screenshots, but I "need at least 10 reputation to post images."

share|improve this answer

When dealing with CSVs in Excel, you should always import the CSV from an open Excel workbook rather than simply opening the CSV with Excel. In Excel 2007 and later, you can import your CSV by choosing Get External Data > From Text on the Data ribbon. The wizard will walk you through the process and allow you to assign formats to each field (e.g. currency, percent, text). For preserving trailing or leading zeros, your best bet is to import the field as Text.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.