Stop Excel from automatically converting certain t

Question

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.

Answer 1

(Assuming Excel 2003...)

When using the Text-to-Columns Wizard has, in Step 3 you can dictate the data type for each of the columns. Click on the column in the preview and change the misbehaving column from "General" to "Text."

Answer 2

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

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

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

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.