By user16324


2008-10-02 23:30:43 8 Comments

Does anyone happen to know if there is a token I can add to my csv for a certain field so Excel doesn't try to convert it to a date?

I'm trying to write a .csv file from my application and one of the values happens to look enough like a date that Excel is automatically converting it from text to a date. I've tried putting all of my text fields (including the one that looks like a date) within double quotes, but that has no effect.

30 comments

@evilReiko 2018-08-12 07:59:04

2018

The only proper solution that worked for me (and also without modifying the CSV).

Excel 2010:

  1. Create new workbook
  2. Data > From Text > Select your CSV file
  3. In the popup, choose "Delimited" radio button, then click "Next >"
  4. Delimiters checkboxes: tick only "Comma" and uncheck the other options, then click "Next >"
  5. In the "Data preview", scroll to the far right, then hold shift and click on the last column (this will select all columns). Now in the "Column data format" select the radio button "Text", then click "Finish".

Excel office365: (client version)

  1. Create new workbook
  2. Data > From Text/CSV > Select your CSV file
  3. Data type detection > do not detect

Note: Excel office365 (web version), as I'm writing this, you will not be able to do that.

@Jack 2018-08-29 08:51:06

This works without changing the CSV files beforehand, so I think this should be the correct answer.

@Roman Yakoviv 2018-06-27 09:45:43

(EXCEL 2016 and later, actually I have not tried in older versions)

  1. Open new blank page
  2. Go to tab "Data"
  3. Click "From Text/CSV" and choose your csv file
  4. Check in preview whether your data is correct.
  5. In сase when some column is converted to date click "edit" and then select type Text by clicking on calendar in head of column
  6. Click "Close & Load"

@fr13d 2013-02-27 08:16:41

I know this is an old question, but the problem is not going away soon. CSV files are easy to generate from most programming languages, rather small, human-readable in a crunch with a plain text editor, and ubiquitous.

The problem is not only with dates in text fields, but anything numeric also gets converted from text to numbers. A couple of examples where this is problematic:

  • ZIP/postal codes
  • telephone numbers
  • government ID numbers

which sometimes can start with one or more zeroes (0), which get thrown away when converted to numeric. Or the value contains characters that can be confused with mathematical operators (as in dates: /, -).

Two cases that I can think of that the "prepending =" solution, as mentioned previously, might not be ideal is

  • where the file might be imported into a program other than MS Excel (MS Word's Mail Merge function comes to mind),
  • where human-readability might be important.

My hack to work around this

If one pre/appends a non-numeric and/or non-date character in the value, the value will be recognized as text and not converted. A non-printing character would be good as it will not alter the displayed value. However, the plain old space character (\s, ASCII 32) doesn't work for this as it gets chopped off by Excel and then the value still gets converted. But there are various other printing and non-printing space characters that will work well. The easiest however is to append (add after) the simple tab character (\t, ASCII 9).

Benefits of this approach:

  • Available from keyboard or with an easy-to-remember ASCII code (9),
  • It doesn't bother the importation,
  • Normally does not bother Mail Merge results (depending on the template layout - but normally it just adds a wide space at the end of a line). (If this is however a problem, look at other characters e.g. the zero-width space (ZWSP, Unicode U+200B)
  • is not a big hindrance when viewing the CSV in Notepad (etc),
  • and could be removed by find/replace in Excel (or Notepad etc).
  • You don't need to import the CSV, but can simply double-click to open the CSV in Excel.

If there's a reason you don't want to use the tab, look in an Unicode table for something else suitable.

Another option

might be to generate XML files, for which a certain format also is accepted for import by newer MS Excel versions, and which allows a lot more options similar to .XLS format, but I don't have experience with this.

So there are various options. Depending on your requirements/application, one might be better than another.


Addition

It needs to be said that newer versions (2013+) of MS Excel don't open the CSV in spreadsheet format any more - one more speedbump in one's workflow making Excel less useful... At least, instructions exist for getting around it. See e.g. this Stackoverflow: How to correctly display .csv files within Excel 2013? .

@Morten Jensen 2015-05-04 12:39:28

I got correct leading zeroes after adding \t to my separation character ; ... damn you MS Office, why did this have to take me more than 2 minutes to figure out?

@ocroquette 2016-02-24 08:52:51

Adding \t at the end of all values does indeed the trick. It's a hacky workaround, but in practice it works fine. I prefer this to the formula trick with the equal '=', because the former might be difficult to work with in other tools.

@Asaf 2016-04-11 11:40:40

There are not enough upvotes that can express my gratitude for you. Search and replace in notepad++ ',' to '\t,\t' (to accommodate for first and last columns as well) works like a charm. Thanks.

@fr13d 2016-04-11 13:28:12

My pleasure, glad it helps some fellow strugglers, @Asaf :-)

@Just Plain High 2016-06-24 14:39:43

In my MySQL query (for CSV output through PHP), I used CONCAT('\t', column_name). Also did the trick. Thanks!

@Andy Corman 2017-02-08 18:59:28

@MortenJensen, you're tip worked like a charm :)

@Fütemire 2017-11-02 00:20:47

CSV's are not small and easy to generate when working on files with almost a million rows. Just say'n... For CSV files I wish Microsoft would just get off their a$$ and fix this. They shouldn't try to assume what we want, and should at least ask before applying a format other than text for a known text file type. Or, at the bare minimum give us the original value back if you change the format back to text. I mean, isn't that the logical approach? But thank you for your answer, it helped.

@zisha 2017-11-17 00:17:24

This works great if you intend to keep the data in Excel but I find that if I copy data from the excel sheet and paste into notepad it shows up as " 1234"

@fr13d 2017-11-18 16:44:29

Yes @zisha, as mentioned in the 5th bullet point, if that bothers you, you should do a find/replace on the file. It's a hack and not everything works transparently - one needs to use it intelligently depending on circumstances.

@Heath Raftery 2018-04-25 09:36:58

Great idea. Alas, Numbers is a bit "smarter" than Excel, and doesn't fall for the tab trick. Best alternative I can come up with is a leading ', since it's kinda well understood and not too distracting... but an invisible would be better.

@Derek Hill 2017-12-19 22:00:59

A workaround using Google Drive (or Numbers if you're on a Mac):

  1. Open the data in Excel
  2. Set the format of the column with incorrect data to Text (Format > Cells > Number > Text)
  3. Load the .csv into Google Drive, and open it with Google Sheets
  4. Copy the offending column
  5. Paste column into Excel as Text (Edit > Paste Special > Text)

Alternatively if you're on a Mac for step 3 you can open the data in Numbers.

@imsrgadich 2018-06-12 05:57:20

This is a sane solution rather than working on it for hours without any resolution.

@mljm 2017-11-30 10:59:29

None of the solutions offered here is a good solution. It may work for individual cases, but only if you're in control of the final display. Take my example: my work produces list of products they sell to retail. This is in CSV format and contain part-codes, some of them start with zero's, set by manufacturers (not under our control). Take away the leading zeroes and you may actually match another product. Retail customers want the list in CSV format because of back-end processing programs, that are also out of our control and different per customer, so we cannot change the format of the CSV files. No prefixed'=', nor added tabs. The data in the raw CSV files is correct; it's when customers open those files in Excel the problems start. And many customers are not really computer savvy. They can just about open and save an email attachment. We are thinking of providing the data in two slightly different formats: one as Excel Friendly (using the options suggested above by adding a TAB, the other one as the 'master'. But this may be wishful thinking as some customers will not understand why we need to do this. Meanwhile we continue to keep explaining why they sometimes see 'wrong' data in their spreadsheets. Until Microsoft makes a proper change I see no proper resolution to this, as long as one has no control over how end-users use the files.

@LarsS 2017-09-17 16:04:02

I made this vba macro which basically formats the output range as text before pasting the numbers. Works perfect for me when i want to paste values such as 8/11, 23/6, 1/3 etc. without Excel interpreting them as dates.

Sub PasteAsText()
' Created by Lars-Erik Sørbotten, 2017-09-17
Call CreateSheetBackup

Columns(ActiveCell.Column).NumberFormat = "@"

Dim DataObj As MSForms.DataObject
Set DataObj = New MSForms.DataObject
DataObj.GetFromClipboard

ActiveCell.PasteSpecial

End Sub

I'm very interested in knowing if this works for other people as well. I've been looking for a solution to this problem for a while, but I haven't seen a quick vba solution to it before which didn't include inserting ' in front of the input text. This code retains the data in its original form.

@Brendonwbrown 2016-08-16 20:09:22

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.

It's a terrible workflow, but at least my values are left the way they should be.

@Laurel 2016-08-16 20:32:30

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

@Brendonwbrown 2016-08-16 21:21:28

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.

@NetMage 2019-01-29 23:23:35

But you can just use the Text Import in Excel to import the CSV file and not bother with Sheets, as noted in other answers.

@Dinghai Zheng 2016-02-29 02:43:30

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.

@sriharsha KB 2016-09-22 11:38:51

I don't know why your answer is downvoted. It is useful to people at least who are using R. This answer helped me. Thanks :)

@ChrisB 2015-12-08 01:12:13

What I have done for this same problem was to add the following before each csv value: "=""" and one double quote after each CSV value, before opening the file in Excel. Take the following values for example:

012345,00198475

These should be altered before opening in Excel to:

"="""012345","="""00198475"

After you do this, every cell value appears as a formula in Excel and so won't be formatted as a number, date, etc. For example, a value of 012345 appears as:

="012345"

@bert bruynooghe 2019-02-18 13:14:36

Another approach is storing the number in the csv as 1234500198475E-8; this way further calculations in excel will still be possible.

@Ana Maria Mendes-Pereira 2015-11-30 17:35:45

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:

  1. Open the CSV file as text (notepad)
  2. Copy it (ctrl+a, ctrl+c).
  3. Paste it in a new excel sheet -it will all paste in one column as long text strings.
  4. Choose/select this column.
  5. 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).

HTH

@Saikat 2016-05-16 05:22:00

This helps even though it is manual effort.

@Ana Maria Mendes-Pereira 2016-05-19 13:33:28

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

@Ana Maria Mendes-Pereira 2016-05-19 13:41:21

Batch processing nonetheless.

@rolls 2016-10-24 03:12:25

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.

@rolls 2016-10-24 03:13:32

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

@Guest 2015-10-24 19:46:00

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.

@Some_Guy 2015-09-17 09:39:27

(EXCEL 2007 and later)

How to force excel not to "detect" date formats without editing the source file

Either:

  • rename the file as .txt
  • 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.

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

@Randy Hoffman 2015-08-12 15:10:26

EASIEST SOLUTION I just figured this out today.

  • Open in Word
  • Replace all hyphens with en dashes
  • Save and Close
  • Open in Excel

Once you are done editing, you can always open it back up in Word again to replace the en dashes with hyphens again.

@EricNo7 2015-05-21 10:45:48

This issue is still present in Mac Office 2011 and Office 2013, I cannot prevent it happening. It seems such a basic thing.

In my case I had values such as "1 - 2" & "7 - 12" within the CSV enclosed correctly within inverted commas, this automatically converts to a date within excel, if you try subsequently convert it to just plain text you would get a number representation of the date such as 43768. Additionally it reformats large numbers found in barcodes and EAN numbers to 123E+ numbers again which cannot be converted back.

I have found that Google Drive's Google Sheets doesnt convert the numbers to dates. The barcodes do have commas in them every 3 characters but these are easily removed. It handles CSVs really well especially when dealing with MAC / Windows CSVs.

Might save someone sometime.

@flatbeat 2015-04-17 08:19:32

I know this is an old thread. For the ones like me, who still have this problem using office 2013 via powershell com object can use the opentext method. The problem is that this method has many arguments, that are sometimes mutual exclusive. To resolve this issue you can use the invoke-namedparameter method introduced in this post. An example would be

$ex = New-Object -com "Excel.Application"
$ex.visible = $true
$csv = "path\to\your\csv.csv"
Invoke-NamedParameter ($ex.workbooks) "opentext" @{"filename"=$csv; "Semicolon"= $true}

Unfortunately I just discovered that this method somehow breaks the csv parsing when cells contain linebreaks. This is supported by csv but microsofts implementation seems to be bugged. Also it did somehow not detect german specific chars. Giving it the correct culture did not change this behaveiour. All files (csv and script) are saved with utf8 encoding. First I wrote the following code to insert the csv cell by cell.

$ex = New-Object -com "Excel.Application"
$ex.visible = $true;
$csv = "path\to\your\csv.csv";
$ex.workbooks.add();
$ex.activeWorkbook.activeSheet.Cells.NumberFormat = "@";
$data = import-csv $csv -encoding utf8 -delimiter ";"; 
$row = 1; 
$data | %{ $obj = $_; $col = 1; $_.psobject.properties.Name |%{if($row -eq1){$ex.ActiveWorkbook.activeSheet.Cells.item($row,$col).Value2= $_ };$ex.ActiveWorkbook.activeSheet.Cells.item($row+1,$col).Value2 =$obj.$_; $col++ }; $row++;}

But this is extremly slow, which is why i looked for an alternative. Appearently Excel allows you to set the values of a range of cells with a matrix. So i used the algorithm in this blog to transform the csv in a multiarray.

function csvToExcel($csv,$delimiter){
     $a = New-Object -com "Excel.Application"
     $a.visible = $true

    $a.workbooks.add()
     $a.activeWorkbook.activeSheet.Cells.NumberFormat = "@"
     $data = import-csv -delimiter $delimiter $csv; 
     $array = ($data |ConvertTo-MultiArray).Value
     $starta = [int][char]'a' - 1
     if ($array.GetLength(1) -gt 26) {
         $col = [char]([int][math]::Floor($array.GetLength(1)/26) + $starta) + [char](($array.GetLength(1)%26) + $Starta)
     } else {
         $col = [char]($array.GetLength(1) + $starta)
     }
     $range = $a.activeWorkbook.activeSheet.Range("a1:"+$col+""+$array.GetLength(0))
     $range.value2 = $array;
     $range.Columns.AutoFit();
     $range.Rows.AutoFit();
     $range.Cells.HorizontalAlignment = -4131
     $range.Cells.VerticalAlignment = -4160
}

 function ConvertTo-MultiArray {
     param(
         [Parameter(Mandatory=$true, Position=1, ValueFromPipeline=$true)]
         [PSObject[]]$InputObject
     )
     BEGIN {
         $objects = @()
         [ref]$array = [ref]$null
     }
     Process {
         $objects += $InputObject
     }
     END {
         $properties = $objects[0].psobject.properties |%{$_.name}
         $array.Value = New-Object 'object[,]' ($objects.Count+1),$properties.count
         # i = row and j = column
         $j = 0
         $properties |%{
             $array.Value[0,$j] = $_.tostring()
             $j++
         }
         $i = 1
         $objects |% {
             $item = $_
             $j = 0
             $properties | % {
                 if ($item.($_) -eq $null) {
                     $array.value[$i,$j] = ""
                 }
                 else {
                     $array.value[$i,$j] = $item.($_).tostring()
                 }
                 $j++
             }
             $i++
         }
         $array
     } 
} 
csvToExcel "storage_stats.csv" ";"

You can use above code as is it should convert any csvs into excel. Just change the path to the csv and the delimiter character at the bottom.

@rolls 2016-10-24 03:14:48

too complicated

@cliffclof 2015-03-30 19:00:02

Without modifying your csv file you can:

  1. Change the excel Format Cells option to "text"
  2. Then using the "Text Import Wizard" to define the csv cells.
  3. Once imported delete that data
  4. then just paste as plain text

excel will properly format and separate your csv cells as text formatted ignoring auto date formats.

Kind of a silly work around, but it beats modifying the csv data before importing. Andy Baird and Richard sort of eluded to this method, but missed a couple important steps.

@Chris 2013-08-27 15:08:03

This is a only way I know how to accomplish this without messing inside the file itself. As usual with Excel, I learned this by beating my head on the desk for hours.

Change the .csv file extension to .txt; this will stop Excel from auto-converting the file when it's opened. Here's how I do it: open Excel to a blank worksheet, close the blank sheet, then File => Open and choose your file with the .txt extension. This forces Excel to open the "Text Import Wizard" where it'll ask you questions about how you want it to interpret the file. First you choose your delimiter (comma, tab, etc...), then (here's the important part) you choose a set columns of columns and select the formatting. If you want exactly what's in the file then choose "Text" and Excel will display just what's between the delimiters.

@Jonathan Leffler 2015-03-04 05:15:42

This just worked fine for me on 2015-03-03 working with Excel for Mac 2011 (version 14.4.8 150116), working with the data in the SO question awk — avoid reformatting of date-like values, where the problem is with Excel rather than awk, despite the question title.

@NitinBirari 2014-10-21 15:19:51

Prefixing space in double quotes resolved the issue!!

I had data like "7/8" in one of the .csv file columns and MS-Excel was converting it to date as "07-Aug". But with "LibreOffice Calc" there was no issue.

To resolve this, I just prefixed space character(added space before 7) like " 7/8" and it worked for me. This is tested for Excel-2007.

@Gary McGill 2015-02-26 12:37:37

OK, but then you have a space in the cell value. This may not be a problem, but invisible space at the start/end of cells often is...

@Harry Duong 2014-02-20 06:37:30

Hi I have the same issue,

I write this vbscipt to create another CSV file. The new CSV file will have a space in font of each field, so excel will understand it as text.

So you create a .vbs file with the code below (for example Modify_CSV.vbs), save and close it. Drag and Drop your original file to your vbscript file. It will create a new file with "SPACE_ADDED" to file name in the same location.

Set objArgs = WScript.Arguments

Set objFso = createobject("scripting.filesystemobject")

dim objTextFile
dim arrStr ' an array to hold the text content
dim sLine  ' holding text to write to new file

'Looping through all dropped file
For t = 0 to objArgs.Count - 1
    ' Input Path
    inPath = objFso.GetFile(wscript.arguments.item(t))

    ' OutPut Path
    outPath = replace(inPath, objFso.GetFileName(inPath), left(objFso.GetFileName(inPath), InStrRev(objFso.GetFileName(inPath),".") - 1) & "_SPACE_ADDED.csv")

    ' Read the file
    set objTextFile = objFso.OpenTextFile(inPath)


    'Now Creating the file can overwrite exiting file
    set aNewFile = objFso.CreateTextFile(outPath, True) 
    aNewFile.Close  

    'Open the file to appending data
    set aNewFile = objFso.OpenTextFile(outPath, 8) '2=Open for writing 8 for appending

    ' Reading data and writing it to new file
    Do while NOT objTextFile.AtEndOfStream
        arrStr = split(objTextFile.ReadLine,",")

        sLine = ""  'Clear previous data

        For i=lbound(arrStr) to ubound(arrStr)
            sLine = sLine + " " + arrStr(i) + ","
        Next

        'Writing data to new file
        aNewFile.WriteLine left(sLine, len(sLine)-1) 'Get rid of that extra comma from the loop


    Loop

    'Closing new file
    aNewFile.Close  

Next ' This is for next file

set aNewFile=nothing
set objFso = nothing
set objArgs = nothing

@syandras 2014-05-08 11:51:32

Its not the Excel. Windows does recognize the formula, the data as a date and autocorrects. You have to change the Windows settings.

"Control Panel" (-> "Switch to Classic View") -> "Regional and Language Options" -> tab "Regional Options" -> "Customize..." -> tab "Numbers" -> And then change the symbols according to what you want.

http://www.pcreview.co.uk/forums/enable-disable-auto-convert-number-date-t3791902.html

It will work on your computer, if these settings are not changed for example on your customers' computer they will see dates instead of data.

@Richard 2014-05-06 01:14:02

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.

@Colin Pear 2014-04-21 13:09:04

While creating the string to be written to my CSV file in C# I had to format it this way:

"=\"" + myVariable + "\""

@Vieenay Siingh 2016-03-26 19:39:07

this woked for me in Rails also. Thanks

@Shoyo 2017-03-16 20:30:48

Works on Java as Well

@Rob Stockley 2014-02-06 21:15:58

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"

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

@Milacay 2014-03-05 18:42:31

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

@CoDEmanX 2016-06-27 14:18:36

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...

@Baracus 2018-03-22 12:59:55

Unfortunately this does not solve the issue of Excel's random formatting "help" - the text DEC1 is still changed to the date Dec-01. Same with text of YYYY-MM-DD being switched to DD/MM/YYYY. :(

@GW4 2013-06-13 00:34:45

I have jus this week come across this convention, which seems to be an excellent approach, but I cannot find it referenced anywhere. Is anyone familiar with it? Can you cite a source for it? I have not looked for hours and hours but am hoping someone will recognize this approach.

Example 1: =("012345678905") displays as 012345678905

Example 2: =("1954-12-12") displays as 1954-12-12, not 12/12/1954.

@fawsha1 2018-04-13 19:00:04

Yes, this works. Thanks!

@Yuvaraj 2013-05-03 05:55:30

CSV - comma separated values. Just create/edit through text editor instead of xls/xlsx/exel. In editing you can set date in required format and it must be kept intact. This is assuming same file is then getting processed programatically.

@rainerbit 2013-02-21 05:06:50

I had a similar problem and this is the workaround that helped me without having to edit the csv file contents:

If you have the flexibility to name the file something other than ".csv", you can name it with a ".txt" extension, such as "Myfile.txt" or "Myfile.csv.txt". Then when you open it in Excel (not by drag and drop, but using File->Open or the Most Recently Used files list), Excel will provide you with a "Text Import Wizard".

In the first page of the wizard, choose "Delimited" for the file type.

In the second page of the wizard choose "," as the delimiter and also choose the text qualifier if you have surrounded your values by quotes

In the third page, select every column individually and assign each the type "Text" instead of "General" to stop Excel from messing with your data.

Hope this helps you or someone with a similar problem!

@rainerbit 2013-02-21 05:08:47

Oh, by the way I am not the first to discover this. risingline.com/use-excel-read-csv-without-reformatting.php That web page also has the helpful instruction that you can shift-click the columns in the third page of the wizard to select them all together before assigning each the type "Text".

@Michael 2017-02-10 16:28:45

This may work for date values, but it doesn't seem to work for things excel interprets as numeric. If your string is all numbers and commas, "Text" format is going to be scientific number format.

@Kevin 2017-08-24 18:17:49

this worked perfectly for me and didn't involve any changes to my file except to rename it as .txt. thank you.

@Ryan 2018-02-05 19:38:50

This was my favorite answer. It worked for me.

@Dan 2013-01-28 18:05:52

Here is the simple method we use at work here when generating the csv file in the first place, it does change the values a bit so it is not suitable in all applications:

Prepend a space to all values in the csv

This space will get stripped off by excel from numbers such as " 1"," 2.3" and " -2.9e4" but will remain on dates like " 01/10/1993" and booleans like " TRUE", stopping them being converted into excel's internal data types.

It also stops double quotes being zapped on read in, so a foolproof way of making text in a csv remain unchanged by excel EVEN IF is some text like "3.1415" is to surround it with double quotes AND prepend the whole string with a space, i.e. (using single quotes to show what you would type) ' "3.1415"'. Then in excel you always have the original string, except it is surrounded by double quotes and prepended by a space so you need to account for those in any formulas etc.

@jacklin 2013-01-31 16:36:42

This worked for me. I had some values like 1-1-1 that get auto converted to dates. As mentioned the space will transfer so the value is changed, but I do a strip on it when parsing so for me that isn't a big deal.

@Abby 2011-02-24 08:10:04

SELECT CONCAT('\'',NOW(),'\''), firstname, lastname 
FROM your_table
INTO OUTFILE 'export.csv' 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '\"' 
LINES TERMINATED BY '\n'

@Martijn Pieters 2012-11-28 21:29:15

Rather than only post a block of code, please explain why this code solves the problem posed. Without an explanation, this is not an answer.

@Jarod Elliott 2008-10-02 23:33:21

I have found that putting an '=' before the double quotes will accomplish what you want. It forces the data to be text.

eg. ="2008-10-03",="more text"

EDIT (according to other posts): because of the Excel 2007 bug noted by Jeffiekins one should use the solution proposed by Andrew: "=""2008-10-03"""

@user16324 2008-10-02 23:50:14

I'm accepting this answer because 1) my csv file will only be used by Excel, and 2) this is for accounting and can't have a ' at the beginning, and 3) I don't want them to have to do an import. I just want them to open the csv.

@user16324 2008-10-02 23:50:46

Oh, and thank you all for your quick replies!

@TMS 2011-12-29 16:16:27

Great! But because of the Excel 2007 bug use the solution proposed by Andrew: "=""2008-10-03""". Updated the post.

@steve 2014-03-31 15:09:14

This doesn't work for me in Excel 2010 if the text is too long :(

@steve 2014-03-31 15:10:37

I ended up prefacing my text with a | symbol (which still shows up in Excel). It is less than optimal but since the excel documents I'm generating are mainly just for reading data, this works in my case.

@lokori 2014-10-08 13:16:59

The reason this works is because when Excel sees the character "=" it decides to evaluate the expression following it. In this case the expression is simply a string literal and the value of a string literal is the content of the string. Thus the quotation marks magically disappear. You can put =1+2+3 and get the value 6 after CSV import. The formula itself is not destroyed in the import process.

@Yevgeniy Afanasyev 2016-09-09 04:33:33

If you use ="sometext" solution(formula trick) then you should know that Excel (tested with 2010) has limit of 255 chars for expressions inside formulas. This means that anything after 255 chars will be chopped off by our beloved Excel.

@Doğukan Tunç 2017-01-23 15:38:08

This answer just saved my life. All the workarounds include "import this, pre-format the cells, etc." I just wanted to double click the .csv file and this solution is it! Thanks a lot, my friend.

@Jin Kwon 2017-07-03 07:20:44

Why 6 double quotes? not 4?

@ACV 2018-01-09 15:33:47

There is a big problem with this approach - if you open the file in excel and edit and then save and open again all these " disappear

@TiredOfProgramming 2018-12-04 20:25:43

Where do I donate for @Jarod Elliott?

@Andrew Ferk 2011-05-16 21:48:35

Working off of Jarod's solution and the issue brought up by Jeffiekins, you could modify

"May 16, 2011"

to

"=""May 16, 2011"""

@Abdulaziz 2014-01-08 14:53:42

It doesn't work on Excel 2013

@steve 2014-03-31 15:11:16

Doesn't work in Excel 2010 if the text is longer than a certain length.

@Pacerier 2015-05-05 03:55:32

@Andrew, This "fix" will cause problems if you need to have " values anywhere inbetween the date.

@d512 2015-12-18 04:57:42

FYI, if you save the Excel file the = goes away and you're back to square one the next time you open in.

@tbc0 2017-09-26 20:43:21

My experience matches neither steve nor d512. It works for me in the incoming .csv in Excel 2013, and after saving as .xlsx it does not revert. Easy enough to for anyone to test using this row of data: "806676","None","41","=""May 16, 2011""","100.00","False"

@NetMage 2019-01-29 23:15:50

In Excel 2016 non of this appears to work through a save.

@NetMage 2019-01-29 23:34:57

@tbc0 Try "806676","None","41","=""05-16-2011""","100.00","False" instead and you will see a save and reload loses the hyphenated text.

Related Questions

Sponsored Content

44 Answered Questions

36 Answered Questions

[SOLVED] Excel to CSV with UTF8 encoding

4 Answered Questions

[SOLVED] Excel wrongly converts ranges into dates, how to avoid it?

17 Answered Questions

[SOLVED] Generating CSV file for Excel, how to have a newline inside a value

  • 2009-08-06 20:28:54
  • Nir
  • 296869 View
  • 142 Score
  • 17 Answer
  • Tags:   excel csv newline

2 Answered Questions

[SOLVED] Excel VBA - Stop converting date to number

  • 2015-12-14 04:54:57
  • Patrick M
  • 1213 View
  • 1 Score
  • 2 Answer
  • Tags:   excel vba csv

1 Answered Questions

[SOLVED] Keep Excel from converting dates when opening/saving CSV

  • 2014-10-17 16:01:03
  • prototype
  • 7163 View
  • 1 Score
  • 1 Answer
  • Tags:   excel csv

1 Answered Questions

[SOLVED] PHP force date to be text in CSV

1 Answered Questions

[SOLVED] Convert Excel to CSV - Properly Convert Date Fields

1 Answered Questions

[SOLVED] Date converting to number on import from Excel to SQL Server

2 Answered Questions

[SOLVED] Stopping Excel Date Parsing

Sponsored Content