texto - no puedo cambiar el formato de fecha en excel

Evita que Excel convierta automáticamente ciertos valores de texto a fechas

Question

¿Alguien sabe si hay un token que puedo agregar a mi csv para un campo determinado para que Excel no intente convertirlo en una fecha?

Estoy tratando de escribir un archivo .csv desde mi aplicación y uno de los valores parece ser una fecha en la que Excel lo convierte automáticamente de texto a fecha. He intentado colocar todos mis campos de texto (incluido el que parece una fecha) entre comillas dobles, pero eso no tiene ningún efecto.

Answer 1

¡El espacio de prefijo entre comillas dobles resolvió el problema!

Tenía datos como "7/8" en una de las columnas del archivo .csv y MS-Excel lo estaba convirtiendo a la fecha como "07-Ago". Pero con "LibreOffice Calc" no hubo problema.

Para resolver esto, simplemente prefijo el carácter de espacio (espacio añadido antes de 7) como "7/8" y funcionó para mí. Esto se prueba para Excel-2007.

Answer 2

(EXCEL 2007 y posteriores)

Cómo forzar a Excel a no "detectar" formatos de fecha sin editar el archivo fuente

Ya sea:

• renombra el archivo como .txt
• Si no puede hacer eso, en lugar de abrir el archivo CSV directamente en Excel, cree un nuevo libro y luego vaya a
  Data > Get external data > From Text
  y seleccione su CSV.

De cualquier manera, se le presentarán opciones de importación, simplemente seleccione cada columna que contenga fechas y diga a Excel que formatee como "texto" no "general".

Answer 3

(EXCEL 2016 y posteriores, en realidad no lo he probado en versiones anteriores)

1. Abrir nueva página en blanco
2. Ir a la pestaña "Datos"
3. Haga clic en "De texto / CSV" y elija su archivo csv
4. Compruebe en la vista previa si sus datos son correctos.
5. En caso de que una columna se convierta en fecha, haga clic en "editar" y luego seleccione tipo de texto haciendo clic en el calendario en el encabezado de la columna
6. Haga clic en "Cerrar y cargar"

Answer 4

(Suponiendo Excel 2003 ...)

Cuando se usa el Asistente de texto a columnas, en el Paso 3 puede dictar el tipo de datos para cada una de las columnas. Haga clic en la columna en la vista previa y cambie la columna de comportamiento incorrecto de "General" a "Texto".

Answer 5

ADVERTENCIA: Excel ''07 (al menos) tiene un error (nother): si hay una coma en el contenido de un campo, no analiza el = "campo, contenido" correctamente, sino que coloca todo después de la coma en el campo siguiente, independientemente de las comillas.

La única solución que he encontrado que funciona es eliminar = cuando el contenido del campo incluye una coma.

Esto puede significar que hay algunos campos que son imposibles de representar exactamente "correctos" en Excel, pero ahora confío en que nadie está demasiado sorprendido.

Answer 6

Al crear la cadena que se escribiría en mi archivo CSV en C #, tuve que formatearla de esta manera:

"=/"" + myVariable + "/""

Answer 7

Al trabajar con la solución de Jarod y el problema planteado por Jeffiekins, se podría modificar

"May 16, 2011"

a

"=""May 16, 2011"""

Answer 8

En Excel 2010 abre una nueva hoja. En la cinta de datos, haga clic en "Obtener datos externos del texto". Seleccione su archivo CSV luego haga clic en "Abrir". Haga clic en Siguiente". Desmarque "Tabulación", coloque una marca de verificación junto a "Coma", luego haga clic en "Siguiente". Haga clic en cualquier lugar en la primera columna. Mientras mantiene presionada la tecla Mayús, arrastre el control deslizante hasta que pueda hacer clic en la última columna y luego suelte la tecla Mayús. Haga clic en el botón de opción "texto" y luego haga clic en "Finalizar"

Todas las columnas se importarán como texto, tal como estaban en el archivo CSV.

Answer 9

En mi caso, "Sept8" en un archivo csv generado usando R se convirtió en "8-Sept" en Excel 2013. El problema se resolvió utilizando la función write.xlsx2 () en el paquete xlsx para generar el archivo de salida en formato xlsx , que puede ser cargado por Excel sin conversión no deseada. Por lo tanto, si recibe un archivo csv, puede intentar cargarlo en R y convertirlo en xlsx usando la función write.xlsx2 ().

Answer 10

Esta es la única forma en que sé cómo lograr esto sin desordenar el archivo. Como de costumbre con Excel, aprendí esto golpeando mi cabeza en el escritorio durante horas.

Cambie la extensión de archivo .csv a .txt; esto evitará que Excel convierta automáticamente el archivo cuando se abra. Así es como lo hago: abra Excel en una hoja de cálculo en blanco, cierre la hoja en blanco, luego Archivo => Abrir y elija su archivo con la extensión .txt. Esto obliga a Excel a abrir el "Asistente para importar texto", donde le hará preguntas sobre cómo desea que interprete el archivo. Primero elige su delimitador (coma, tabulación, etc.), luego (aquí está la parte importante) elige un conjunto de columnas de columnas y selecciona el formato. Si desea exactamente lo que hay en el archivo, seleccione "Texto" y Excel mostrará solo lo que hay entre los delimitadores.

Answer 11

Esta semana he encontrado esta convención, que parece ser un enfoque excelente, pero no puedo encontrarla en ningún lugar. ¿Alguien está familiarizado con esto? ¿Puedes citar una fuente para ello? No he buscado horas y horas, pero espero que alguien reconozca este enfoque.

Ejemplo 1: = ("012345678905") se muestra como 012345678905

Ejemplo 2: = ("1954-12-12") se muestra como 1954-12-12 , no 12/12/1954 .

Answer 12

Este es el método simple que usamos en el trabajo aquí cuando generamos el archivo csv en primer lugar, cambia los valores un poco por lo que no es adecuado en todas las aplicaciones:

Prepone un espacio a todos los valores en el csv

Este espacio se eliminará por sobresalir de números como "1", "2.3" y "-2.9e4", pero permanecerá en fechas como "01/10/1993" y booleanos como "TRUE", evitando que se conviertan en Los tipos de datos internos de Excel.

También evita que las comillas dobles se impriman, por lo que una manera infalible de hacer que el texto en un csv permanezca sin cambios por excel EVEN SI es un texto como "3.1415" es rodearlo con comillas dobles Y anteponer toda la cadena con un espacio, es decir (usando comillas simples para mostrar lo que escribiría) ''"3.1415"''. Luego, en Excel, siempre tiene la cadena original, excepto que está rodeada por comillas dobles y precedida por un espacio, por lo que debe tener en cuenta las de las fórmulas, etc.

Answer 13

Este problema sigue presente en Mac Office 2011 y Office 2013, no puedo evitar que suceda. Parece una cosa tan básica.

En mi caso tuve valores como "1 - 2" y "7 - 12" dentro del CSV encerrados correctamente dentro de las comas invertidas, esto se convierte automáticamente a una fecha dentro de Excel, si intenta convertirlo posteriormente a texto simple, obtendrá una representación numérica de la fecha como 43768. Además, vuelve a formatear números grandes encontrados en códigos de barras y números EAN a números 123E + de nuevo que no se pueden convertir de nuevo.

Descubrí que las Google Sheets de Google Drive no convierten los números en fechas. Los códigos de barras tienen comas en ellos cada 3 caracteres, pero estos se eliminan fácilmente. Maneja los CSV realmente bien, especialmente cuando se trata de CSV de MAC / Windows.

Podría salvar a alguien alguna vez.

Answer 14

He encontrado que poner un ''='' antes de las comillas dobles logrará lo que quieres. Obliga a los datos a ser texto.

p.ej. = "2008-10-03", = "más texto"

EDITAR (según otras publicaciones) : debido al error de Excel 2007 observado por Jeffiekins, se debe usar la solución propuesta por Andrew : "=""2008-10-03"""

Answer 15

Hola tengo el mismo problema,

Escribo este vbscipt para crear otro archivo CSV. El nuevo archivo CSV tendrá un espacio en la fuente de cada campo, por lo que Excel lo entenderá como texto.

Así que crea un archivo .vbs con el siguiente código (por ejemplo, Modify_CSV.vbs), guárdelo y ciérrelo. Arrastre y suelte su archivo original a su archivo vbscript. Creará un nuevo archivo con "SPACE_ADDED" al nombre del archivo en la misma ubicación.

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

Answer 16

Lo hago para los números de tarjetas de crédito que siguen convirtiéndose a notación científica: termino importando mi .csv en Google Sheets. Las opciones de importación ahora permiten deshabilitar el formato automático de valores numéricos. Establecí cualquier columna sensible a Texto sin formato y la descargué como xlsx.

Es un flujo de trabajo terrible, pero al menos mis valores quedan como deben ser.

Answer 17

Lo que he hecho para este mismo problema fue agregar lo siguiente antes de cada valor csv: "=" "" y una comilla doble después de cada valor CSV, antes de abrir el archivo en Excel. Tome los siguientes valores, por ejemplo:

012345,00198475

Estos deben ser alterados antes de abrir en Excel para:

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

Después de hacer esto, cada valor de celda aparece como una fórmula en Excel y, por lo tanto, no se formateará como un número, una fecha, etc. Por ejemplo, un valor de 012345 aparece como:

="012345"

Answer 18

Ninguna de las soluciones ofrecidas aquí es una buena solución. Puede funcionar para casos individuales, pero solo si tiene el control de la pantalla final. Tomemos mi ejemplo: mi trabajo produce una lista de productos que venden al por menor. Esto está en formato CSV y contiene códigos de pieza, algunos de ellos comienzan con cero, establecidos por los fabricantes (no bajo nuestro control). Quita los ceros iniciales y puedes igualar otro producto. Los clientes minoristas desean la lista en formato CSV debido a los programas de procesamiento de back-end, que también están fuera de nuestro control y son diferentes por cliente, por lo que no podemos cambiar el formato de los archivos CSV. Sin prefijo ''='', ni pestañas añadidas. Los datos en los archivos CSV sin procesar son correctos; Es cuando los clientes abren esos archivos en Excel los problemas comienzan. Y muchos clientes no son realmente expertos en computación. Pueden abrir y guardar un archivo adjunto de correo electrónico. Estamos pensando en proporcionar los datos en dos formatos ligeramente diferentes: uno como Excel Friendly (utilizando las opciones sugeridas anteriormente al agregar un TAB, el otro como el ''maestro''. Pero esto puede ser una ilusión, ya que algunos clientes no entenderán por qué tenemos que hacer esto. Mientras tanto, continuamos explicando por qué a veces ven datos "incorrectos" en sus hojas de cálculo. Hasta que Microsoft realice un cambio adecuado, no veo una solución adecuada a esto, siempre y cuando uno no tenga control sobre cómo los usuarios finales Usa los archivos.

Answer 19

No es el Excel. Windows reconoce la fórmula, los datos como fecha y autocorrección. Tienes que cambiar la configuración de Windows.

"Panel de control" (-> "Cambiar a vista clásica") -> "Opciones regionales y de idioma" -> pestaña "Opciones regionales" -> "Personalizar ..." -> pestaña "Números" -> Y luego cambiar los símbolos Según lo que quieras.

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

Funcionará en su computadora, si estas configuraciones no se modifican, por ejemplo, en la computadora de sus clientes, verán las fechas en lugar de los datos.

Answer 20

Ok, encontré una forma sencilla de hacer esto en Excel 2003 hasta 2007. Abra un libro de trabajo xls en blanco. Luego vaya al menú Datos, importe datos externos. Seleccione su archivo csv. Ir a través del asistente y luego en "formato de datos de columna" seleccione cualquier columna que deba ser forzada a "texto". Esto importará toda la columna como un formato de texto que evita que Excel intente tratar cualquier celda específica como una fecha.

Answer 21

Pegar tabla en palabra. Haga una búsqueda / reemplazo y cambie todos - (guiones) en - (guión doble). Copiar y pegar en Excel. Puede hacer lo mismo con otros símbolos (/), etc. Si necesita volver a un guión una vez en Excel, simplemente formatee la columna en texto, luego haga el cambio. Espero que esto ayude.

Answer 22

Sé que es un viejo tema. Para los que, como yo, los que todavía tienen este problema al usar office 2013 a través del objeto powershell com pueden usar el método opentext . El problema es que este método tiene muchos argumentos, que a veces son mutuamente exclusivos. Para resolver este problema, puede utilizar el método de parámetro invoke-named introducido en esta publicación . Un ejemplo sería

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

Desafortunadamente, acabo de descubrir que este método rompe de alguna manera el análisis CSV cuando las células contienen saltos de línea. Esto está soportado por csv pero la implementación de microsofts parece tener problemas. También de alguna manera no detectó caracteres específicos alemanes. Darle la cultura correcta no cambió esta conducta. Todos los archivos (csv y script) se guardan con la codificación utf8. Primero escribí el siguiente código para insertar el csv celda por celda.

$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++;}

Pero esto es extremadamente lento, por lo que busqué una alternativa. Aparentemente, Excel le permite establecer los valores de un rango de celdas con una matriz. Así que utilicé el algoritmo en este blog para transformar el csv en una 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" ";"

Puede usar el código anterior como debe convertir cualquier csvs en excel. Solo cambia la ruta al csv y al delimitador en la parte inferior.

Answer 23

SOLUCIÓN MÁS FÁCIL Acabo de descubrir esto hoy.

• Abrir en Word
• Reemplace todos los guiones con guiones
• Guardar y cerrar
• Abrir en Excel

Una vez que haya terminado de editar, siempre puede volver a abrirla en Word para reemplazar los guiones de nuevo con guiones.

Answer 24

Si coloca una coma invertida al comienzo del campo, se interpretará como texto.

Ejemplo: 25/12/2008 convierte en ''25/12/2008

También puede seleccionar el tipo de campo al importar.

Answer 25

Sigue siendo un problema en el lanzamiento de Microsoft Office 2016, bastante molesto para aquellos de nosotros que trabajamos con nombres de genes como MARC1, MARCH1, SEPT1, etc. La solución que encontré como la más práctica después de generar un archivo ".csv" en R, que luego se abrirá / compartirá con los usuarios de Excel:

1. Abra el archivo CSV como texto (bloc de notas)
2. Cópialo (ctrl + a, ctrl + c).
3. Péguelo en una nueva hoja de Excel; todo se pegará en una columna como cadenas de texto largas.
4. Elija / seleccione esta columna.
5. Vaya a Datos- "Texto a columnas ...", en la ventana abierta, elija "delimitado" (siguiente). Compruebe que la "coma" esté marcada (marcándola ya mostrará la separación de los datos en las columnas a continuación) (a continuación), en esta ventana puede elegir la columna que desee y marcarla como texto (en lugar de general) (Finalizar).

HTH

Answer 26

Sin modificar su archivo csv puede:

1. Cambia la opción de Excel Formato de Celdas a "texto"
2. Luego, utilice el "Asistente para importar texto" para definir las celdas csv.
3. Una vez importada borra esos datos.
4. luego simplemente pegar como texto plano

excel formateará y separará correctamente sus celdas csv como texto formateado ignorando los formatos de fecha automáticos.

Es un poco tonto, pero es mejor modificar los datos csv antes de importar. Andy Baird y Richard eludieron este método, pero fallaron un par de pasos importantes.

Answer 27

Tuve un problema similar y esta es la solución que me ayudó sin tener que editar el contenido del archivo csv:

Si tiene la flexibilidad de asignar un nombre al archivo que no sea ".csv", puede nombrarlo con una extensión ".txt", como "Myfile.txt" o "Myfile.csv.txt". Luego, cuando lo abra en Excel (no arrastrando y soltando, sino utilizando Archivo-> Abrir o la lista de archivos Usados ​​más recientemente), Excel le proporcionará un "Asistente para importar texto".

En la primera página del asistente, elija "Delimitado" para el tipo de archivo.

En la segunda página del asistente, seleccione "," como delimitador y también elija el calificador de texto si ha rodeado sus valores entre comillas

En la tercera página, seleccione cada columna individualmente y asigne a cada uno el tipo "Texto" en lugar de "General" para evitar que Excel juegue con sus datos.

Espero que esto te ayude a ti oa alguien con un problema similar!

Answer 28

Una solución alternativa utilizando Google Drive (o Números si estás en una Mac):

1. Abrir los datos en Excel.
2. Establezca el formato de la columna con datos incorrectos en Texto (Formato> Celdas> Número> Texto)
3. Cargue el archivo .csv en Google Drive y ábralo con Google Sheets
4. Copia la columna ofensiva
5. Pegar columna en Excel como texto (Edición> Pegado especial> Texto)

Alternativamente, si está en una Mac para el paso 3, puede abrir los datos en Números.

Answer 29

2018

La única solución adecuada que funcionó para mí (y también sin modificar el CSV).

Excel 2010:

1. Crear nuevo libro de trabajo
2. Datos> Desde texto> Seleccione su archivo CSV
3. En la ventana emergente, elija el botón de radio "Delimitado", luego haga clic en "Siguiente>"
4. Delimitadores casillas de verificación: marque solo "Coma" y desmarque las otras opciones, luego haga clic en "Siguiente>"
5. En la "Vista previa de datos", desplácese hasta el extremo derecho, luego mantenga presionada la tecla Mayús y haga clic en la última columna (esto seleccionará todas las columnas). Ahora, en el "Formato de datos de columna", seleccione el botón de opción "Texto", luego haga clic en "Finalizar".

Excel office365: (versión cliente)

1. Crear nuevo libro de trabajo
2. Datos> De texto / CSV> Seleccione su archivo CSV
3. Detección de tipos de datos> no detectar

Nota: Excel office365 (versión web), mientras escribo esto, no podrás hacerlo.

Answer 30

Sé que esta es una vieja pregunta, pero el problema no desaparecerá pronto. Los archivos CSV son fáciles de generar desde la mayoría de los lenguajes de programación, son más bien pequeños, legibles para los humanos en un momento crítico con un editor de texto plano y ubicuos.

El problema no es solo con las fechas en los campos de texto, sino que cualquier cosa numérica también se convierte de texto a números. Un par de ejemplos donde esto es problemático:

• ZIP / códigos postales
• números telefónicos
• números de identificación del gobierno

que a veces puede comenzar con uno o más ceros (0), que se desechan cuando se convierten en números. O el valor contiene caracteres que pueden confundirse con operadores matemáticos (como en las fechas: /, -).

Dos casos en los que puedo pensar que la solución "prepending =", como se mencionó anteriormente, podría no ser ideal es

• donde el archivo podría importarse a un programa que no sea MS Excel (la función de combinación de correspondencia de MS Word me viene a la mente),
• donde la legibilidad humana puede ser importante.

Mi truco para solucionar esto

Si uno pre / agrega un carácter no numérico y / o no de fecha en el valor, el valor se reconocerá como texto y no se convertirá. Un carácter no imprimible sería bueno ya que no alterará el valor mostrado. Sin embargo, el carácter de espacio anterior sencillo (/ s, ASCII 32) no funciona para esto ya que Excel lo corta y el valor aún se convierte. Pero hay varios otros caracteres de espacio de impresión y no impresión que funcionarán bien. Sin embargo, lo más fácil es agregar (agregar después) el carácter de tabulación simple (/ t, ASCII 9).

Beneficios de este enfoque:

• Disponible desde el teclado o con un código ASCII fácil de recordar (9),
• No molesta la importación,
• Normalmente , no molesta los resultados de la combinación de correspondencia (según el diseño de la plantilla, pero normalmente solo agrega un espacio amplio al final de una línea). (Si esto es un problema, mire otros caracteres, por ejemplo, el espacio de ancho cero (ZWSP, Unicode U + 200B)
• no es un gran obstáculo al ver el CSV en el Bloc de notas (etc),
• y podría eliminarse mediante buscar / reemplazar en Excel (o Bloc de notas, etc.).
• No es necesario importar el CSV, pero puede simplemente hacer doble clic para abrir el CSV en Excel.

Si hay una razón por la que no quiere usar la pestaña, busque en una tabla Unicode para otra cosa adecuada.

Otra opción

podría ser generar archivos XML, para los cuales también se acepta la importación de un formato determinado por las nuevas versiones de MS Excel, y que permite muchas más opciones similares al formato .XLS, pero no tengo experiencia con esto.

Así que hay varias opciones. Dependiendo de sus requisitos / aplicación, uno podría ser mejor que otro.

Adición

Debe decirse que las versiones más recientes (2013+) de MS Excel ya no abren el CSV en formato de hoja de cálculo, una velocidad más en el flujo de trabajo hace que Excel sea menos útil ... Al menos, existen instrucciones para solucionarlo. Consulte, por ejemplo, este : ¿Cómo mostrar correctamente los archivos .csv en Excel 2013? .