Как я могу остановить 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.

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

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

Решение

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

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

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

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

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

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

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

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

Мастер импорта текста

ура

Лучшее решение – генерировать 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 открывают его правильно.

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

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

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

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

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

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

  • Как предотвратить повреждение общих файлов Excel?
  • Как вводить формулы в Excel в других локалях (с разными десятичными разделителями)?
  • Таблица Excel, вставляющая старую формулу в новые строки. Как я могу вернуться к новой формуле?
  • Как найти неработающие ссылки в Excel, которые не могут быть нарушены с помощью «Разрыв ссылок»?
  • Выполнение SQL-запроса с помощью VBA и заполнение листом данных
  • Excel: форматирование пользовательских номеров
  • Удалите повторяющиеся строки в excel, если не все столбцы одинаковы
  • График в excel для представления корреляции трех параметров
  • Преобразование данных электронной таблицы Excel
  • Вам нужно два окна excel бок о бок, на разных мониторах, в том же экземпляре
  • Excel условное форматирование даты до выходных / праздников
  • Давайте будем гением компьютера.