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

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

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

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

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

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

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

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

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

который иногда может начинаться с одного или нескольких нhive (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.

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

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

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

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


прибавление

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

Работая с решением Джарода и вопросом, поднятым Джеффикинсом, вы можете изменить

 "May 16, 2011" 

в

 "=""May 16, 2011""" 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

НТН

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

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

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

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

(Предположим, Excel 2003 …)

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

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

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

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

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

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

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

(EXCEL 2007 и последующие)

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

Или:

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

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

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

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

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

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

Я пишу этот 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 

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

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

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

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

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

 012345,00198475 

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

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

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

 ="012345" 

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

Я знаю, что это старая нить. Для таких, как я, у кого до сих пор есть эта проблема, используя офис 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 и разделителю внизу.

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

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

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

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

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

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

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

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

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

2018

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

Excel 2010:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ЛУЧШЕЕ РЕШЕНИЕ Я только что понял это сегодня.

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

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

  • Разница между @import и ссылкой в ​​CSS
  • Oracle: импортировать CSV-файл
  • Заполнить базу данных Android из файла CSV?
  • Можем ли мы импортировать XML-файл в другой файл XML?
  • Импорт файла outlook.pst в thunderbird
  • Получение импортированных данных json в фрейм данных
  • Почему мой OBJ-парсер оказывает такие сетки?
  • Перенос из Oracle в MySQL
  • Импорт фотографий в Mac OS X
  • Импорт файлов CSV в .Net
  • Как вы можете прочитать CSV-файл в R с различным количеством столбцов
  • Давайте будем гением компьютера.