We help IT Professionals succeed at work.

Creating a CSV file from Excel maintaining numbers in character column

Creating a CSV file from Excel  and retaining the character format of a column that has all numeric



I have an excel spreadsheet that is defined as character, however the columns that have numeric are converting to scientific format.

The file looks fine when opened in notepad, however once opened in Excel the format changes.

The file was created in SAS Enterprise Guide.
Comment
Watch Question

Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
When openning a CSV file, Excel attempt to guess the data type, and pick the most appropriate one, wich may not be the one you want.

Solution:
Adjust the displayed format within Excel, the CSV is not at fault.
Roy CoxGroup Finance Manager

Commented:
You need to open the file in Excel then format the number columns then save as csv file.

  1. Select the cells you want to modify. Selecting a cell range.
  2. Click the drop-down arrow next to the Number Format command on the Home tab. The Number Formatting drop-down menu will appear.
  3. Select the desired formatting option. ...
  4. The selected cells will change to the new formatting style.

For more details read this

How to control and understand settings in the Format Cells dialog box in Excel
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
If you change the file extension from .csv to .txt, you will have a lot more control over how the data are handled when you open the file in Excel. You don't need to make any changes to the file--just rename it.

When you open a .txt file, you are offered a dialog that looks very similar to the Data...Text to Columns wizard. There are the same three steps, so you may specify the delimiter(s). And in the third step of the wizard, you may specify on a column by column basis whether the data should be treated as General, Text, Date (multiply varieties), or not imported. If you import the same type of file on a regular basis, you can even record a macro that captures those settings.
moriniaAdvanced Analytics Analyst

Author

Commented:
Thanks to all.