Вопрос: Как я могу остановить Excel от употребления моих вкусных файлов CSV и извлечения бесполезных данных?


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

Некоторые клиенты хотят получить электронную таблицу, совместимую с Excel, из приобретенных ими виджетов. Мы генерируем это с помощью PHP-скрипта, который запрашивает базу данных и выводит результат как CSV с именем магазина и связанными данными. Это прекрасно работает.

При открытии в текстовом редакторе, таком как Notepad или vi, файл выглядит следующим образом:

"Account Number","Store Name","S1","S2","S3","Widget Type","Date"
"4173","SpeedyCorp","268435459705526269","","268435459705526269","848 Model Widget","2011-01-17"

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

Account Number  Store Name  S1  S2  S3  Widget Type Date 
4173    SpeedyCorp  2.68435E+17     2.68435E+17 848 Model Widget    2011-01-17

Как вы могли заметить, серийные номера заключены в двойные кавычки. Кажется, что Excel не соблюдает классификаторы текста в CSV-файлах. При импорте этих файлов в Access у нас есть нулевая трудность. Открывая их как текст, никаких проблем вообще. Но Excel непременно преобразует эти файлы в бесполезный мусор. Попытка проинструктировать конечных пользователей в открытии CSV-файла с использованием приложения, отличного от стандартного, становится, надо сказать, утомительной. Есть надежда? Есть ли настройка, которую мне не удалось найти? Это похоже на Excel 2003, 2007 и 2010.


121
2018-01-19 01:08


Источник


могу ли я дать +1 только для имени? - tombull89
Excel does not seem to respect text qualifiers in .csv files - двойные кавычки не текстовые классификаторы, они просто разрешают запятые в ваших данных, если вы не используете запятые в своих данных, тогда они бессмысленный, Все данные в CSV-файле являются нетипизированными, поэтому Excel может только предположить, что ваш большой серийный номер является номер, и именно тогда вы заходите в Excel максимальная точность из 15 цифр, что является сокращением ваших чисел. - DMA57361
Excel, похоже, не уважает все запятые в двойных кавычках. Рассмотрим «12 августа 2012 г.» Excel превращает это в мусор. - zundarz
Я хочу упомянуть об этом SU вопрос, Он объясняет, какие параметры у вас есть при работе с CSV в Excel. - nixda
@nixda Спасибо! Это полезные предложения, особенно для более опытных пользователей. Моя проблема почти в большей степени связана с человеческой проблемой, поскольку Excel связывается с файлами .csv, и люди видят значок и дважды щелкают (потому что так вы открываете вещи), а затем обычно нажимаете Save (потому что мы всегда говорим их спасти!), и все потеряно. Но я, безусловно, буду использовать ваши методы, когда это возможно. - atroon


Ответы:


Но Excel непременно преобразует эти файлы в бесполезный мусор.

Excel - бесполезный мусор.

Решение

Я был бы немного удивлен, если бы любой клиент, желающий ваши данные в формате Excel, не смог изменить видимое форматирование на этих трех столбцах на «Число» с нулевыми знаками после запятой или на «текст». Но давайте предположим, что короткий практический документ не может быть и речи.

Ваши варианты:

  1. Бросьте в свои серийные номера не числовой, а не пробельный символ.
  2. Запишите файл xls или xlsx с некоторым форматированием по умолчанию.
  3. Обманите и выведите эти числа в виде формул ="268435459705526269","",="268435459705526269" (вы также можете сделать ="268435459705526269",,="268435459705526269" сохраняя 2 символа). Это имеет то преимущество, что вы правильно показываете и, вероятно, в целом полезны, но тонко разбиты (поскольку они являются формулами).

Будьте осторожны с вариантом 3, потому что некоторые программы (включая Excel и Open Office Calc) больше не будут обрабатывать запятые внутри ="" поля как экранированные. Это значит ="abc,xyz" будет охватывать два столбца и прерывать импорт.

Использование формата "=""abc,xy""" решает эту проблему, но этот метод по-прежнему ограничивает вас до 255 символов из-за ограничения длины формулы Excel.


57
2018-01-19 01:57



Вообще-то, это не сурово. Скопируйте и вставьте один из приведенных выше цифр в Excel, затем измените формат номера, как было предложено. Excel изменяет значение, что приводит к мусору. - Joe Internet
@Joe, я был слишком поверхностным в своем первоначальном обзоре. Excel действительно производит мусор и сам по себе мусор. Я обновил свой ответ, чтобы отразить это. Опция может иметь «Excel csv», а также иметь «полезный, стоящий csv», - Tyler
@Tyler - я не думаю, что Excel - это мусор, просто говоря, что OP был прав, что в этом случае он производил мусор. На самом деле это очень хороший вопрос, без какого-либо элегантного решения. - Joe Internet
Опция Format Cells ... была предложена, и я попытался ее использовать. В этом случае, как только вы открываете файл, Excel, похоже, преобразует сериалы в научную нотацию (согласованную, а не неожиданную) и бросает точность. Когда вы меняете их на число или на текст, строка не возвращается. Что на самом деле суть проблемы. Вывод в виде формул может сделать это, хотя ... Я об этом не думал. - atroon
@ DMA57361 Поведение не ожидается, это можно определить. Цифровая точность хорошо документирована, так как Excel не читает CSV. Отсутствие предупреждения и молча отбрасывание данных является абсурдным. Тот факт, что вы не можете даже сказать Excel, как импортировать данные, также абсурден. Является ли отрицательность необходимый? Нет, но честность - лучшая политика, и я так себя чувствую. - Tyler


У нас была аналогичная проблема, когда у нас были CSV-файлы с столбцами, содержащими диапазоны, такие как 3-5, и Excel всегда преобразовывал их в даты, например. 3-5 было бы 3 Мар, после чего переключение на числовое давало нам бесполезное целое число. Мы обошли его

  1. Переименование CSV на расширение TXT
  2. Затем, когда мы открыли его в Excel, это вызовет мастер импорта текста
  3. На шаге 3 из 3 мастера мы сказали, что в колонках речь идет о тексте и они импортированы должным образом.

Вы могли бы сделать то же самое здесь, я бы подумал.

text import wizard

ура


41
2018-01-31 18:30



+1 за то, что это правильный способ сделать это. (Редактировать: извините пришлось немного изменить, чтобы уточнить решение) - Jay
Вам не нужно переименовывать файл. Просто используйте мастер импорта Shift - выберите все столбцы и выберите в качестве текста. - nixda
Мастер импорта текста - это ответ. Все остальные решения - бесполезный хакер, вызванный непониманием использования Excel для просмотра и редактирования CSV. - Excellll
@Excellll, делая это один файл за раз. Автоматизируя этот процесс, «бесполезный хакерство» часто спасает день. - Parrish Husband
это совершенно бесполезно, когда excel используется стандартными пользователями для отображения CSV-файлов. прежде чем я попытаюсь объяснить, как использовать мастер импорта-импорта для ~ 15 пользователей в офисе начинающих, я бы скорее напечатал исходный код документа excel. - northkildonan


Лучшее решение - генерировать книгу XML. Как это:

<?xml version="1.0" encoding="UTF-8"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  </OfficeDocumentSettings>

  <ss:Worksheet ss:Name="Sheet 1">
    <Table>
    <Column ss:Width="100"/>
    <Column ss:Width="100"/>
    <Column ss:Width="150"/>
    <Column ss:Width="150"/>
    <Column ss:Width="150"/>
    <Column ss:Width="150"/>
    <Column ss:Width="80"/>
    <Column/>

    <Row>
      <Cell><Data ss:Type="String">Account Number</Data></Cell>
      <Cell><Data ss:Type="String">Store Name</Data></Cell>
      <Cell><Data ss:Type="String">S1</Data></Cell>
      <Cell><Data ss:Type="String">S2</Data></Cell>
      <Cell><Data ss:Type="String">S3</Data></Cell>
      <Cell><Data ss:Type="String">Widget Type</Data></Cell>
      <Cell><Data ss:Type="String">Date</Data></Cell>
    </Row>

    <Row>
      <Cell><Data ss:Type="String">4173</Data></Cell>
      <Cell><Data ss:Type="String">SpeedyCorp</Data></Cell>
      <Cell><Data ss:Type="String">268435459705526269</Data></Cell>
      <Cell><Data ss:Type="String">x</Data></Cell>
      <Cell><Data ss:Type="String">268435459705526269</Data></Cell>
      <Cell><Data ss:Type="String">848 Model Widget</Data></Cell>
      <Cell><Data ss:Type="String">2011-01-17</Data></Cell>
    </Row>


    </Table>
    <x:WorksheetOptions/>
  </ss:Worksheet>
</Workbook>

Файл должен иметь расширение .xml. Excel и OpenOffice открывают его правильно.


9
2018-02-17 11:13



Вы имеете в виду, что OP должен использовать PHP-скрипт для преобразования базы данных в формат XML? - Prasanna
Очень чище, чем открывать пользователям .csv в Excel или испортить ваш CSV, чтобы только Excel мог понять ваш CSV. Это даже не тот комплекс схемы. - binki
Где этот стандарт документирован? Я хотел бы узнать больше о доступных типах данных. - John Doherty


Мое решение: У меня такая же проблема с импортом серийных номеров. Их не нужно рассматривать как числа, т. Е. На нем не выполняются математические функции, но нам нужно здесь всего числа. Самое простое, что у меня есть - вставить пробел в серийный номер. например, «12345678 90123456 1234». Когда Excel импортирует его, он будет рассматриваться как текст вместо числа.


1
2018-05-08 08:08





У меня длинные номера счетов были искажены.

Вот как я это исправил:

Откройте файл file.csv в Libre Office / Open Office (возможно, вам придется указать разделители и т. Д.), А затем сохраните файл в виде XML-файла Excel.

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

Откройте файл формата Excel, и столбец все равно будет в порядке!


0
2018-05-17 14:53



Хотя это бы работа, пытаясь объяснить кому-то, кто говорит только по-английски, почему он должен будет использовать другой офисный пакет, создает столько же проблем, сколько он решает. Альтернативное программное обеспечение для M $ Office - это все к лучшему по моему мнению, но я понимаю, что я не могу преобразовать всех. - atroon


Мастер импорта - лучшее решение для случайных пользователей и одноразовых ситуаций. Если вам требуется программное решение, вы можете использовать метод QueryTables.Add (который использует мастер импорта за кулисами).

Workbooks.Add
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & "C:\myfile.csv", Destination:=Range("$A$1"))
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 2, 2) 'Edit this line. Add a number for each column, 1 is general, 2 is text. Search the internet for other formats.
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With

0
2017-07-23 19:48