Rectangle 27 0

csv Stop Excel from automatically converting certain text values to dates?


In my case, "Sept8" in a csv file generated using R was converted into "8-Sept" by Excel 2013. The problem was solved by using write.xlsx2() function in the xlsx package to generate the output file in xlsx format, which can be loaded by Excel without unwanted conversion. So, if you are given a csv file, you can try loading it into R and converting it into xlsx using the write.xlsx2() function.

Note
Rectangle 27 0

csv Stop Excel from automatically converting certain text values to dates?


Paste table into word. Do a search/replace and change all - (dashes) into -- (double dash). Copy and paste into Excel. Can do same for other symbols (/), etc. If need to change back to a dash once in Excel, just format the column to text, then make the change. Hope this helps.

Note
Rectangle 27 0

csv Stop Excel from automatically converting certain text values to dates?


Paste table into word. Do a search/replace and change all - (dashes) into -- (double dash). Copy and paste into Excel. Can do same for other symbols (/), etc. If need to change back to a dash once in Excel, just format the column to text, then make the change. Hope this helps.

Note
Rectangle 27 0

csv Stop Excel from automatically converting certain text values to dates?


All columns will be imported as text, just as they were in the CSV file.

I ran into a same problem, but following @Rob's instructions carefully would work perfect!. Thank you

In Excel 2010 open a new sheet. On the Data ribbon click "Get External Data From Text". Select your CSV file then click "Open". Click "Next". Uncheck "Tab", place a check mark next to "Comma", then click "Next". Click anywhere on the first column. While holding the shift key drag the slider across until you can click in the last column, then release the shift key. Click the "text" radio button then click "Finish"

The Data From Text assistant does unfortunately not support multi-line cells. If such a multi-line text is encountered, it will write all lines after the first into new rows, breaking the entire import. It only interprets the line breaks correctly if you open the CSV file by double clicking. Damn you Microsoft...

Note
Rectangle 27 0

csv Stop Excel from automatically converting certain text values to dates?


All columns will be imported as text, just as they were in the CSV file.

I ran into a same problem, but following @Rob's instructions carefully would work perfect!. Thank you

In Excel 2010 open a new sheet. On the Data ribbon click "Get External Data From Text". Select your CSV file then click "Open". Click "Next". Uncheck "Tab", place a check mark next to "Comma", then click "Next". Click anywhere on the first column. While holding the shift key drag the slider across until you can click in the last column, then release the shift key. Click the "text" radio button then click "Finish"

The Data From Text assistant does unfortunately not support multi-line cells. If such a multi-line text is encountered, it will write all lines after the first into new rows, breaking the entire import. It only interprets the line breaks correctly if you open the CSV file by double clicking. Damn you Microsoft...

Note
Rectangle 27 0

csv Stop Excel from automatically converting certain text values to dates?


""""
"=""field, co""ntent"""
field, content

I just tested this in excel 2007, and it works correctly for me. However if there is a leading space before the quotes, then it does not parse comma correctly.

The only workaround I've found that works is to eliminate the = when the field contents include a comma.

This may mean that there are some fields that are impossible to represent exactly "right" in Excel, but by now I trust no-one is too surprised.

This wouldn't appear to be a bug according to RFC 4180. The correct way to quote the field is to quote the whole field and then double quote internal quotes. So "=""field, content""" tools.ietf.org/html/rfc4180

WARNING: Excel '07 (at least) has a(nother) bug: if there's a comma in the contents of a field, it doesn't parse the ="field, contents" correctly, but rather puts everything after the comma into the following field, regardless of the quotation marks.

Note
Rectangle 27 0

csv Stop Excel from automatically converting certain text values to dates?


  • If you can't do that, instead of opening the CSV file directly in excel, create a new workbook then go to Data > Get external data > From Text and select your CSV.
  • rename the file as .txt

Either way, you will be presented with import options, simply select each column containing dates and tell excel to format as "text" not "general".

Note
Rectangle 27 0

csv Stop Excel from automatically converting certain text values to dates?


I did bother, thank you. I hint at the automatic formatting selection being a new part of the import process. My answer provides a very specific workflow, for those who, like me, have been forced to use Sheets to deal with this specific problem. In my mind, this merited a separate answer. A minor adjustment of your tone might actually result in your comment being interpreted as helpful, which is the whole point of this family of websites.

I do this for credit card numbers which keep converting to scientific notation: I end up importing my .csv into Google Sheets. The import options now allow to disable automatic formatting of numeric values. I set any sensitive columns to Plain Text and download as xlsx.

If you bother to read through some of the other answers, you will see that someone already suggested this here.

Note
Rectangle 27 0

csv Stop Excel from automatically converting certain text values to dates?


Okay found a simple way to do this in Excel 2003 through 2007. Open a blank xls workbook. Then go to Data menu, import external data. Select your csv file. Go through the wizard and then in "column data format" select any column that needs to be forced to "text". This will import that entire column as a text format preventing Excel from trying to treat any specific cells as a date.

Note
Rectangle 27 0

csv Stop Excel from automatically converting certain text values to dates?


  • Go to Data- "Text to columns...", on the window opened choose "delimited" (next). Check that "comma" is marked (marking it will already show the separation of the data to columns below) (next), in this window you can choose the column you want and mark it as text (instead of general) (Finish).
  • Open the CSV file as text (notepad)
  • Paste it in a new excel sheet -it will all paste in one column as long text strings.

Batch processing nonetheless.

Still an issue in Microsoft Office 2016 release, rather disturbing for those of us working with gene names such as MARC1, MARCH1, SEPT1 etc. The solution I've found to be the most practical after generating a ".csv" file in R, that will then be opened/shared with Excel users:

The manual effort was also unappealing to me. However, after exhausting every possibility at hand, this one was the most robust and reliable.

This is actually the best answer here that requires the least amount of work. I wish you could just set some option to force it to not parse the file.

You can also replace all commas with tabs, then it will auto deliminate

Note