Excel CSV - cell format

Question

I am creating a report as a CSV file. When I try to open the file in Excel, it assumes a data type based on the contents of the cell and reformatts it accordingly.

For example, if the CSV file contains

...,005,... 

Excel then shows this as 5. Is there a way to override this and display 005?

I would prefer to do something for the file itself so that the user can simply double-click the CSV file to open it.

I am using Excel 2003.

Answer 1

There is no easy way to control Excel formatting when opening a CSV file. However, below are three approaches that may help.

My preference is the first option.

Option 1 - Change the data in the file

You can change the data in the CSV file as follows: = "005" , ... This will be displayed in Excel as ..., 005 , ...

Excel will save the data as a formula, but copying the column and using special paste values ​​will get rid of the formula, but keep the formatting

Option 2 - Format Data

If this is just a format problem, and all of your data in this column is three digits long. Then open the data in Excel and then format the column containing the data with this custom format 000

Option 3 Change the file extension to .dif (data exchange format).

Change the file extension and use the file import wizard to manage formats. Files with the extension .dif are opened automatically by Excel with a double click.

Step by step:

• Change the file extension from .csv to .dif
• Double-click the file to open it in Excel.
• The "Import File Wizard" will launch.
• Set the "File Type" to "Delimited" and click on the "Next" button.
• In the "Separators" section, select "Comma" and click the "Next" button.
• Click on each column of data displayed and select "Column Data Format". A column with a value of "005" must be formatted as "Text."
• Click the end button, the file will open Excel with the formats you specified.

Answer 2

Do not use CSV, use SYLK.
http://en.wikipedia.org/wiki/SYmbolic_LinK_(SYLK)

This gives you much more control over formatting, and Excel will not try to guess the type of field by examining the contents. It looks a little more complicated, but you can get away with using a very small subset.

Answer 3

This works for Microsoft Office 2010, Excel version 14

I am reading the OP option incorrectly to do something for the file itself. I still keep this for those who want the solution to directly format the import.

• Open an empty (new) file (File → New from book)
  
• Open the import wizard (data → from text)
  
• Select your .csv file and Import
  
• In the dialog box, select Delimited and click Next.
  
• Select the delimiters (uncheck everything except the "comma"), select your text classifiers (probably {No}), click "Next"
  
• In the Data preview field , select the column that you want to be text. He must emphasize.
  
• In the Column data format field , select Text.
  
• Click Finish.
  

Answer 4

You can simply format your range as text.

Also here is a good article on number formats and how you can program them.

Answer 5

In fact, I found that, at least since Office 2003, you can save an Excel spreadsheet as an XML file. That way I can create an XML file, and when I double-clicked it, it will open in Excel. It provides the same level of control as SYLK, but the XML syntax is more intuitive.

Answer 6

Adding unused space in a cell can help. For example: "firstvalue";"secondvalue";"005 ";"othervalue"

This forces Excel to treat it as text, and the space is not visible. On Windows, you can add non-breaking space by typing alt + 0160. See here for more information: http://en.wikipedia.org/wiki/Non-breaking_space

I tried it in Excel 2010. I hope this helps people who are still looking for a completely correct solution to this problem.

Answer 7

I had this problem when exporting CSV data from C # code and resolved this by adding the first null data with the \ t tab character, so the data was interpreted as text rather than numeric in Excel (but unlike other characters preceding it, can not see).

I liked the approach = "001", but that would not allow me to import CSV data again into my C # application without removing all this formatting from the import CSV file (instead, I just trim the data import).

Answer 8

I believe that when importing a file, you can choose the type of column. Make text instead of number. At the moment, I do not have a copy in front of me to check.

Answer 9

Load csv into oleDB and force enter all output data types into string

I asked the same question and then answered it with a code.

basically, when the csv file is loaded, the oledb driver makes assumptions, you can tell what assumptions to make.

My code forces all data types to use a string, though ... it's very easy to change the schema. for my purposes, I used xslt to get ti the way I wanted, but I parse a large number of files.

Answer 10

I know this is an old question, but I have a solution that is not mentioned here.

When you create csv, add a space after the comma, but before your value, for example. , 005,

This helped prevent automatic date formatting in Excel 2007.

Answer 11

The import text import method does NOT work when the imported CSV file has line breaks inside the cell. This method handles this script (at least with tab delimited data):

• Create a new Excel file
• Ctrl + A to select all cells
• In the field with the list of format numbers, select the text
• Open tab delimited file in a text editor
• Select all, copy and paste in Excel

Answer 12

This drove me crazy all day (since you really can't control the types of Excel columns before opening the CSV file), and it worked for me using VB.NET and Excel Interop:

  'Convert .csv file to .txt file. FileName = ConvertToText(FileName) Dim ColumnTypes(,) As Integer = New Integer(,) {{1, xlTextFormat}, _ {2, xlTextFormat}, _ {3, xlGeneralFormat}, _ {4, xlGeneralFormat}, _ {5, xlGeneralFormat}, _ {6, xlGeneralFormat}} 'We are using OpenText() in order to specify the column types. mxlApp.Workbooks.OpenText(FileName, , , Excel.XlTextParsingType.xlDelimited, , , True, , True, , , , ColumnTypes) mxlWorkBook = mxlApp.ActiveWorkbook mxlWorkSheet = CType(mxlApp.ActiveSheet, Excel.Worksheet) Private Function ConvertToText(ByVal FileName As String) As String 'Convert the .csv file to a .txt file. 'If the file is a text file, we can specify the column types. 'Otherwise, the Codes are first converted to numbers, which loses trailing zeros. Try Dim MyReader As New StreamReader(FileName) Dim NewFileName As String = FileName.Replace(".CSV", ".TXT") Dim MyWriter As New StreamWriter(NewFileName, False) Dim strLine As String Do While Not MyReader.EndOfStream strLine = MyReader.ReadLine MyWriter.WriteLine(strLine) Loop MyReader.Close() MyReader.Dispose() MyWriter.Close() MyWriter.Dispose() Return NewFileName Catch ex As Exception MsgBox(ex.Message) Return "" End Try End Function 

Answer 13

When you open CSV, you get a text import wizard. At the last step of the wizard, you should be able to import a specific column in the form of text, while maintaining the prefix 00. After that, you can format the cell in any way you want.

I tried with Excel 2007 and it seems to work.

Answer 14

Well, excel never pops up a wizard for CSV files. If you rename it to .txt, you will see a wizard the next time you do File> Open in Excel.

Answer 15

Put one quote in front of the field. Excel will treat it as text, even if it looks like a number.

 ...,`005,... 

EDIT: This is wrong. The apoptop trick only works when entering data directly into Excel. When you use it in a CSV file, an apostrophe appears in a field that you do not need.

http://support.microsoft.com/kb/214233

Answer 16

Just add 'in front of the number in the CSV document.