Остановить Excel от автоматического преобразования определенных текстовых значений в даты

Кто-нибудь знает, есть ли токен, который я могу добавить в свой csv для определенного поля, чтобы Excel не пытался преобразовать его в дату?

Я пытаюсь записать CSV файл из своего приложения, и одно из значений выглядит так же, как дата, когда Excel автоматически преобразует ее из текста в дату. Я попытался поместить все мои текстовые поля (включая ту, которая похожа на дату) в двойные кавычки, но это не имеет никакого эффекта.

+450
источник поделиться
34 ответа
  • 1
  • 2

Я обнаружил, что ставим '=' перед тем, как двойные кавычки будут выполнять то, что вы хотите. Это заставляет данные быть текстом.

например. = "2008-10-03", = "больше текста"

EDIT (согласно другим сообщениям): из-за ошибки Excel 2007, отмеченной Джеффикинсом, следует использовать предложенное Andrew: "=""2008-10-03"""

+316
источник

Я знаю, что это старый вопрос, но проблема скоро не исчезнет. CSV файлы легко сгенерированы с большинства языков программирования, довольно маленькие, удобные для чтения в хрусте с текстовым редактором и повсеместно.

Проблема заключается не только в датах в текстовых полях, но и в том, что числовые числа также преобразуются из текста в числа. Несколько примеров, где это проблематично:

  • ZIP/почтовые индексы
  • телефонные номера
  • правительственные идентификационные номера

который иногда может начинаться с одного или нескольких нулей (0), которые отбрасываются при преобразовании в числовое. Или значение содержит символы, которые можно спутать с математическими операторами (как в датах:/, -).

Два случая, о которых я могу думать, что решение "prepending =", как упоминалось ранее, может быть не идеальным

  • где файл может быть импортирован в программу, отличную от MS Excel (функция MS Mail Mail Merge приходит на ум),
  • где человеческая читаемость может быть важной.

Мой взлом, чтобы обойти это

Если один из pre/добавляет в числозначительный и/или не-даты символ, значение будет распознаваться как текст и не преобразовываться. Непечатаемый символ будет хорош, так как он не изменит отображаемое значение. Однако простой старый символ пространства (\ s, ASCII 32) не работает для этого, поскольку он отрубается Excel, а затем значение все еще преобразуется. Но есть и другие печатные и непечатные пробелы, которые будут хорошо работать. Однако проще всего добавить (добавить после) простой символ табуляции (\ t, ASCII 9).

Преимущества такого подхода:

  • Доступный с клавиатуры или с легко запоминающимся кодом ASCII (9),
  • Он не беспокоит импорт,
  • Обычно не беспокоит результаты слияния почты (в зависимости от шаблона макета, но обычно он просто добавляет широкое пространство в конце строки). (Если это проблема, посмотрите на другие символы, например, пространство нулевой ширины (ZWSP, Unicode U + 200B)
  • не является большим препятствием при просмотре CSV в "Блокноте" (и т.д.),
  • и может быть удален путем поиска/замены в Excel (или Notepad и т.д.).
  • Вам не нужно импортировать CSV, но можно просто дважды щелкнуть, чтобы открыть CSV в Excel.

Если есть причина, по которой вы не хотите использовать вкладку, посмотрите в таблице Юникод для чего-то еще подходящего.

Другой вариант

может быть сгенерировать XML файлы, для которых определенный формат также принимается для импорта более новыми версиями MS Excel и который позволяет намного больше параметров, похожих на формат.XLS, но у меня нет опыта с этим.

Таким образом, есть различные варианты. В зависимости от ваших требований/приложений, может быть лучше другого.


прибавление

Нужно сказать, что более новые версии (2013+) MS Excel больше не открывают CSV в формате электронных таблиц - еще один speedbump в одном рабочем процессе делает Excel менее полезным... По крайней мере, существуют инструкции по обходу, См. Например, этот Stackoverflow: как правильно отображать CSV файлы в Excel 2013? ,

+123
источник
другие ответы

Связанные вопросы


Похожие вопросы

Отключение решения Jarod и проблема, поднятая Джеффикинсом, вы можете изменить

"May 16, 2011"

к

"=""May 16, 2011"""
+96
источник

У меня была аналогичная проблема, и это временное решение, которое помогло мне без необходимости редактировать содержимое файла csv:

Если у вас есть возможность назвать файл чем-то иным, чем ".csv", вы можете назвать его с расширением ".txt", например "Myfile.txt" или "Myfile.csv.txt". Затем, когда вы открываете его в Excel (не перетаскивая мышью, а используя список файлов → Открыть или список недавно использованных файлов), Excel предоставит вам "Мастер импорта текста".

На первой странице мастера выберите "Разграничено" для типа файла.

На второй странице мастера выберите "," в качестве разделителя, а также выберите классификатор текста, если вы окружили свои значения кавычками

На третьей странице выберите каждый столбец отдельно и назначьте каждый тип "Текст" вместо "Общие", чтобы остановить обмен сообщениями с вашими данными.

Надеюсь, это поможет вам или кому-то с подобной проблемой!

+56
источник

ПРЕДУПРЕЖДЕНИЕ: Excel '07 (по крайней мере) имеет ошибку (nother): если есть запятая в содержимом поля, она не корректно анализирует поле "=", "содержимое", а скорее ставит все после запятую в следующее поле, независимо от кавычек.

Единственным обходным решением, которое я нашел, что работает, является устранение = когда содержимое поля содержит запятую.

Это может означать, что есть некоторые поля, которые невозможно представить точно "правильно" в Excel, но теперь я уверен, что никто не удивляется.

+27
источник

При создании строки, которая будет записана в файл CSV на С#, мне пришлось форматировать ее следующим образом:

"=\"" + myVariable + "\""
+19
источник

В Excel 2010 откройте новый лист. На ленте данных нажмите "Получить внешние данные из текста". Выберите файл CSV и нажмите "Открыть". Нажмите "Далее". Снимите флажок "Вкладка", поставьте галочку рядом с "Запятая", затем нажмите "Далее". Щелкните в любом месте в первом столбце. Удерживая клавишу shift, перетащите ползунок влево до тех пор, пока вы не сможете щелкнуть последний столбец, а затем отпустите клавишу shift. Нажмите кнопку "текст", затем нажмите "Готово"

Все столбцы будут импортированы как текст, так же, как они были в файле CSV.

+15
источник

Все еще проблема в выпуске Microsoft Office 2016, довольно тревожная для тех из нас, кто работает с именами генов, такими как MARC1, MARCH1, SEPT1 и т.д. Решение, которое я нашел наиболее практичным после создания файла ".csv" в R, затем будет открываться/совместно использоваться пользователями Excel:

  • Откройте CSV файл в виде текста (блокнот)
  • Скопируйте его (ctrl + a, ctrl + c).
  • Вставьте его в новый лист excel - все будет вставляться в один столбец в виде длинных текстовых строк.
  • Выберите/выберите этот столбец.
  • Перейдите к Data- "Текст в столбцы...", в открывшемся окне выберите "с разделителем" (далее). Убедитесь, что отмечена "запятая" (если она уже показывает разделение данных на столбцы ниже) (далее), в этом окне вы можете выбрать нужный столбец и пометить его как текст (вместо общего) ( "Готово" ).

НТН

+13
источник

Вот простой метод, который мы используем здесь при создании файла csv в первую очередь, он немного меняет значения, поэтому он не подходит во всех приложениях:

Подготовьте пробел ко всем значениям в csv

Это пространство будет отделено от цифр, таких как "1", "2,3" и "-2,9e4", но останется в таких датах, как "01/10/1993", и имеет значение boolean как "TRUE", останавливая их преобразуется в внутренние типы данных excel.

Он также останавливает двойные кавычки, забитые при чтении, поэтому надежный способ создания текста в csv остается неизменным благодаря excel EVEN IF - некоторый текст, такой как "3.1415", должен окружать его двойными кавычками. И добавьте всю строку с помощью пробел, т.е. (используя одинарные кавычки, чтобы показать, что вы набираете) '' 3.1415 ". Затем в excel у вас всегда есть исходная строка, за исключением того, что она окружена двойными кавычками и добавляется пробелом, поэтому вам нужно учитывать их в любых формулах и т.д.

+10
источник

(Предполагая Excel 2003...)

При использовании мастера "Текст-столбцы" на шаге 3 вы можете указать тип данных для каждого из столбцов. Нажмите на столбец в предпросмотре и измените столбец с ошибкой от "Общий" до "Текст".

+8
источник

Это единственный способ, которым я знаю, как это сделать, не вникая в сам файл. Как обычно в Excel, я узнал об этом, часами бьюсь головой по столу.

Измените расширение .csv на .txt; это остановит Excel от автоматической конвертации файла при его открытии. Вот как я это делаю: откройте Excel на пустой лист, закройте пустой лист, затем File = > Open и выберите файл с расширением .txt. Это заставляет Excel открыть "Мастер импорта текста", где он задаст вам вопросы о том, как вы хотите, чтобы он интерпретировал файл. Сначала вы выбираете разделитель (запятая, табуляция и т.д.), Затем (здесь важная часть) вы выбираете набор столбцов столбцов и выбираете форматирование. Если вы хотите точно, что в файле, то выберите "Текст", и в Excel будет отображаться только то, что между разделителями.

+8
источник

(EXCEL 2007 и более поздние версии)

Как заставить excel не "определять" форматы даты без редактирования исходного файла

Либо:

  • переименуйте файл как .txt
  • Если вы не можете этого сделать, вместо открытия файла CSV непосредственно в excel, создайте новую книгу, затем перейдите в
    Data > Get external data > From Text и выберите свой CSV.

В любом случае вам будут предоставлены параметры импорта, просто выберите каждый столбец, содержащий даты, и скажите excel, чтобы формат был "текст", а не "общий".

+4
источник

2018

Единственное правильное решение, которое сработало для меня (а также без изменения CSV).

Excel 2010:

  1. Создать новую рабочую книгу
  2. Данные> Из текста> Выберите файл CSV
  3. Во всплывающем окне выберите переключатель "Разграничить", затем нажмите "Далее>"
  4. Флажки разделителей: отметьте только "Запятая" и снимите флажки с других параметров, затем нажмите "Далее>"
  5. В "Предварительном просмотре данных" перейдите в крайнее правое положение, затем удерживайте клавишу Shift и щелкните по последнему столбцу (при этом будут выбраны все столбцы). Теперь в "Формате данных столбца" выберите переключатель "Текст", затем нажмите "Готово".

Excel office365: (версия клиента)

  1. Создать новую рабочую книгу
  2. Данные> Из текста /CSV> Выберите файл CSV
  3. Обнаружение типа данных> не обнаруживать

Примечание: Excel office365 (веб-версия), поскольку я пишу это, вы не сможете этого сделать.

+4
источник

Это не Excel. Windows распознает формулу, данные как дату и автосохраняет. Вы должны изменить настройки Windows.

"Панель управления" (- > "Переключиться на классический вид" ) → "Региональный и язык Опции "- > вкладка" Региональные параметры "- > " Настроить... "- > вкладка" Числа "- > И затем измените символы в соответствии с тем, что вы хотите.

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

Он будет работать на вашем компьютере, если эти параметры не будут изменены, например, на компьютере ваших клиентов, они будут видеть даты вместо данных.

+3
источник

Ни одно из предлагаемых решений не является хорошим решением. Он может работать для отдельных случаев, но только если вы контролируете окончательный дисплей. Возьмем мой пример: моя работа создает список продуктов, которые они продают в розницу. Это в формате CSV и содержит частичные коды, некоторые из которых начинаются с нуля, установленных производителями (не под нашим контролем). Уберите ведущие нули, и вы можете фактически сопоставить другой продукт. Розничные клиенты хотят получить список в формате CSV из-за программ обработки задних страниц, которые также не поддаются контролю и отличаются от каждого клиента, поэтому мы не можем изменить формат файлов CSV. Нет префикса '=', а также не добавлены вкладки. Данные в исходных файлах CSV верны; когда клиенты открывают эти файлы в Excel, проблемы начинаются. И многие клиенты не очень разбираются в компьютерах. Они могут просто открыть и сохранить вложение электронной почты. Мы думаем предоставить данные в двух немного разных форматах: один как Excel Friendly (используя предложенные выше варианты, добавив TAB, а другой - как "мастер". Но это может быть желаемое за действительное, так как некоторые клиенты не поймут, почему мы должны это делать. Тем временем мы продолжаем объяснять, почему они иногда видят "неправильные" данные в своих электронных таблицах. До тех пор, пока Microsoft не внесет правильное изменение, я не вижу надлежащего разрешения на это, пока не контролируется, как конечные пользователи используют файлы.

+3
источник

У меня есть jus на этой неделе, натолкнувшись на это соглашение, которое, кажется, отличный подход, но я не могу найти его в любом месте. Кто-нибудь знаком с этим? Не могли бы вы привести источник? Я не искал часы и часы, но надеюсь, что кто-то узнает этот подход.

Пример 1: = ( "012345678905" ) отображается как 012345678905

Пример 2: = ( "1954-12-12" ) отображается как 1954-12-12, а не 12/12/1954.

+2
источник

Привет, у меня такая же проблема,

Я пишу этот vbscipt для создания другого CSV файла. Новый CSV файл будет содержать пробел в шрифте каждого поля, поэтому excel поймет его как текст.

Итак, вы создаете файл .vbs с кодом ниже (например Modify_CSV.vbs), сохраняете и закрываете его. Перетащите исходный файл в файл vbscript. Он создаст новый файл с именем "SPACE_ADDED" для имени файла в том же месте.

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
+2
источник

Без изменения вашего файла csv вы можете:

  • Измените опцию "Выбрать ячейки форматирования" на "текст"
  • Затем, используя "Мастер импорта текста", чтобы определить ячейки csv.
  • После импорта удалите эти данные
  • затем просто вставьте как обычный текст

excel будет правильно форматировать и разделять ваши ячейки csv как форматированные тексты, игнорируя автоматические форматы дат.

Вид глупой работы вокруг, но он превосходит изменения данных csv перед импортом. Энди Бэрд и Ричард вроде бы избегали этого метода, но пропустили пару важных шагов.

+2
источник

То, что я сделал для этой же проблемы, заключалось в том, чтобы добавить следующее до каждого значения csv: знак равно и одну двойную кавычку после каждого значения CSV, прежде чем открывать файл в Excel. Возьмите следующие значения, например:

012345,00198475

Они должны быть изменены перед открытием в Excel:

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

После этого каждое значение ячейки отображается как формула в Excel и поэтому не будет отформатировано как число, дата и т.д. Например, значение 012345 выглядит как:

="012345"
+2
источник

Я знаю, что это старый поток. Для таких, как я, у кого до сих пор есть эта проблема, используя офис 2013 через объект powershell com, можно использовать метод opentext. Проблема в том, что этот метод имеет много аргументов, которые иногда взаимно исключают друг друга. Чтобы устранить эту проблему, вы можете использовать метод invoke-namedparameter, введенный в этот пост. Пример:

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

К сожалению, я просто обнаружил, что этот метод каким-то образом нарушает синтаксический анализ csv, когда ячейки содержат разрывы строк. Это поддерживается csv, но реализация микрософт, по-видимому, прослушивается. Также он каким-то образом не обнаружил специфические немецкие символы. Придание ему правильной культуры не изменило этого поведения. Все файлы (csv и script) сохраняются с помощью кодировки utf8. Сначала я написал следующий код для вставки ячейки csv по ячейке.

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

Но это очень медленно, поэтому я искал альтернативу. Очевидно, что Excel позволяет вам устанавливать значения диапазона ячеек с матрицей. Поэтому я использовал алгоритм в этот блог, чтобы преобразовать csv в 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" ";"

Вы можете использовать код выше, так как он должен преобразовывать любые csvs в excel. Просто измените путь на csv и символ разделителя внизу.

+1
источник

Вставить таблицу в слово. Сделайте поиск/замену и измените все - (тире) на - (двойная тире). Скопируйте и вставьте в Excel. Можно сделать то же самое для других символов (/) и т.д. Если вам нужно снова вернуться к тире один раз в Excel, просто отформатируйте столбец в текст, а затем внесите изменения. Надеюсь, это поможет.

+1
источник

В моем случае "сентябрь" в файле csv, сгенерированном с использованием R, был преобразован в Excel-2013 в "8-Sept". Проблема была решена с помощью функции write.xlsx2() в пакете xlsx для генерации выходного файла в формате xlsx, который может быть загружен Excel без нежелательного преобразования. Итак, если вам дан csv файл, вы можете попробовать его загрузить в R и преобразовать в xlsx с помощью функции write.xlsx2().

+1
источник

Я делаю это для номеров кредитных карт, которые продолжают конвертировать в научную нотацию: я в конечном итоге импортирую свой CSV в Google Таблицы. Параметры импорта теперь позволяют отключить автоматическое форматирование числовых значений. Я устанавливаю любые чувствительные столбцы в Plain Text и загружаюсь как xlsx.

Это ужасный рабочий процесс, но по крайней мере мои ценности оставлены так, как они должны быть.

+1
источник

Обходной путь с использованием Google Диска (или номеров, если вы находитесь на Mac):

  1. Открыть данные в Excel
  2. Установите формат столбца с неправильными данными в текст (Формат> Ячейки> Число> Текст)
  3. Загрузите CSV в Google Диск и откройте его с помощью Google Таблиц
  4. Скопировать столбец
  5. Вставить столбец в Excel в виде текста (Edit> Paste Special> Text)

Кроме того, если вы находитесь на Mac для шага 3, вы можете открыть данные в Numbers.

+1
источник

(EXCEL 2016 и более поздние, на самом деле я не пробовал в более старых версиях)

  1. Открыть новую пустую страницу
  2. Перейдите на вкладку "Данные"
  3. Нажмите "Из текста /CSV" и выберите файл csv.
  4. Проверьте предварительный просмотр, верны ли ваши данные.
  5. В случае, когда какой-либо столбец преобразуется в дату, нажмите "изменить", а затем выберите "Текст", нажав на календарь в заголовке столбца
  6. Нажмите "Закрыть и загрузить"
+1
источник

Если вы поместите инвертированную запятую в начале поля, она будет интерпретирована как текст.

Пример: 25/12/2008 становится '25/12/2008

Вы также можете выбрать тип поля при импорте.

0
источник

Хорошо нашел простой способ сделать это в Excel с 2003 по 2007 год. Откройте пустую рабочую книгу xls. Затем перейдите в меню "Данные", импортируйте внешние данные. Выберите файл csv. Пройдите мастер, а затем в "формате данных столбца" выберите любой столбец, который нужно принудительно "text". Это будет импортировать весь столбец в виде текстового формата, не позволяя Excel пытаться обрабатывать любые конкретные ячейки в качестве даты.

0
источник

Префикс пробела в двойных кавычках разрешил проблему!

У меня были такие данные, как "7/8" в одном из столбцов файла .csv, а MS-Excel конвертировал его на сегодняшний день как "07-Aug". Но с "LibreOffice Calc" проблем не было.

Чтобы разрешить это, я просто префикс пробела (добавлено пространство до 7), например "7/8" , и это сработало для меня. Это проверено на Excel-2007.

0
источник

Эта проблема все еще присутствует в Mac Office 2011 и Office 2013, я не могу предотвратить ее. Кажется, такая основная вещь.

В моем случае у меня были значения, такие как "1 - 2" и "7 - 12" в CSV, заключенные правильно в кавычки, это автоматически преобразуется в дату в excel, если вы попытаетесь впоследствии преобразовать его в обычный текст вы получите числовое представление даты, например 43768. Кроме того, он переформатирует большие числа, найденные в штрих-кодах и номерах EAN, на номера 123E +, которые не могут быть возвращены обратно.

Я обнаружил, что Google Google Google Таблицы не конвертирует числа в даты. Штрих-коды имеют в них запятые каждые 3 символа, но их легко удалить. Он отлично справляется с CSV, особенно при работе с CSV-серверами MAC/Windows.

Может кто-нибудь кого-нибудь спасти.

0
источник

EASIEST SOLUTION Я просто понял это сегодня.

  • Открыть в Word
  • Заменить все дефисы с помощью дефисов
  • Сохранить и закрыть
  • Открыть в Excel

Как только вы закончите редактирование, вы всегда можете снова открыть его в Word, чтобы снова заменить дефисы дефисом.

0
источник
  • 1
  • 2

Посмотрите другие вопросы по меткам или Задайте вопрос