Форматирование CSV с разделителями-запятыми, чтобы заставить Excel интерпретировать значение как строку

Question

Некоторое время я искал, пытаясь выяснить, как вывести файл CSV таким образом, чтобы заставить Excel интерпретировать значения как строку, а не пытаться преобразовать их в числа или даты.

например:

"141", "10/11/2002", "350.00", "1311742251"

Excel пытается "разумно" преобразовать все это в свои родные форматы даты / числа. Есть ли способ обойти это?

---

РЕДАКТИРОВАТЬ: уточнил смысл моего вопроса, извините за путаницу.

Answer 1

Для тех, кто имеет контроль над исходными данными, очевидно, что Excel будет автоматически определять формат поля CSV, если столбец CSV не находится в этом формате:

"=""Data Here"""

например...

20,       5.5%,      "0404 123 351", "3-6",  "=""123"""
[number]  [percent]  [number]        [date]  [string]  <-- how Excel interprets

Он также работает в Google Spreadsheet, но не уверен, что другие приложения для работы с электронными таблицами поддерживают эту нотацию.

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

"=""She said """"Hello"""" to him"""

(РЕДАКТИРОВАТЬ: Обновлено с исправлениями, спасибо DMA57361!)

Answer 2

Как и многие, я боролся с теми же решениями, которые принимает Microsoft, и пробовал различные предлагаемые решения.

Для Excel 2007:

• Помещение всех значений в двойные кавычки НЕ помогает
• Помещение = перед всеми значениями после их двойных кавычек помогает, НО делает файл csv бесполезным для большинства других приложений
• Скобки вокруг двойных кавычек вокруг всех значений - это чушь
• Помещение пробела перед всеми значениями перед двойными кавычками препятствует преобразованию в даты, но НЕ препятствует обрезанию начальных или конечных нулей.
• Помещение одинарной кавычки перед значением работает только при вводе данных в Excel.

Тем не мение:

Помещение табуляции перед всеми значениями перед тем, как заключить в них двойные кавычки, ДЕЙСТВИТЕЛЬНО предотвращает преобразование в даты, И ДЕЙСТВИТЕЛЬНО препятствует обрезанию начальных или конечных нулей, и на листе даже не отображаются неприятные предупреждающие маркеры в верхнем левом углу каждой ячейки.

Например:

"<tab character><some value>","<tab character><some other value>"

Обратите внимание, что символ табуляции должен быть в двойных кавычках. Редактировать: оказывается, что двойные кавычки даже не нужны.

Двойной щелчок по файлу csv может открыть файл в виде электронной таблицы в Excel, показывающей все значения, которые обрабатываются как выше, как текстовые данные. Убедитесь, что в Excel установлено «.» в качестве десятичной точки, а не ',' или каждая строка файла CSV будет заканчиваться как один текст в первой ячейке каждой строки. По-видимому, Microsoft считает, что CSV означает "не десятичная точка" разделенное значение.

Answer 3

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

Вы можете найти его как Data → Get External Data → From Text в Excel 2007/2010.
Или Data → Import External Data → Import Data в Excel 2003.

Вот изображение мастера импорта текста в Excel 2003 в действии с данными примера, показывающее, как импортировать последние два столбца в виде текста:

Answer 4

Пример от Саймона у меня не сработал, и я подозреваю, что это языковая разница. В C # вот как выглядит строка моего рабочего формата:

var linebreak = (i++ == list.Count) ? "" : "\r\n";

csv += String.Format("=\"{0}\",{1},{2},{3},=\"{4}\"{5}",
    item.Value, item.Status, item.NewStatus, item.Carrier, c.Status, linebreak);

и вот как выглядит выходной файл:

="abababababab",INVALID,INVALID,USPS,="",
="9500100030492359000149",UNKNOWNSTATUS,DELIVERED,USPS,="3"
="9500100030492359000149",UNKNOWNSTATUS,DELIVERED,USPS,="3"
="9500100030492359000149",UNKNOWNSTATUS,DELIVERED,USPS,="3"
="9500100030492359000149",UNKNOWNSTATUS,DELIVERED,USPS,="3"
="9400110200793482982812",UNKNOWNSTATUS,DELIVERED,USPS,="3"
="9400110200793482982812",UNKNOWNSTATUS,DELIVERED,USPS,="3"
="9400110200793000216184",UNKNOWNSTATUS,INVALID,USPS,=""

Как можно видеть, формат в выходном файле: ="VALUE", не "=""VALUE""", что, я считаю, может быть соглашением Visual Basic.

Я использую Excel 2010. Кстати, Google Sheets не будет открывать / конвертировать файл, отформатированный таким образом. Это сработает, если вы удалите знак равенства, то есть "VALUE", - Excel все равно откроет файл, но проигнорирует тот факт, что вы хотите, чтобы ваши столбцы были строками.

Answer 5

Простой способ заставить Excel интерпретировать дату как текст - это поставить одну кавычку перед датой, вместо использования полных кавычек, как в:

'10/11/2002

Если вы можете импортировать CSV вместо его открытия, вы можете указать Excel, в каком формате должен быть каждый столбец. Посмотрите на этот вопрос, который я задал.