Как правильно открыть csv файл в excel

Файлы CSV

Последнее обновление: 29.04.2017

Одним из распространенных файловых форматов, которые хранят в удобном виде информацию, является формат csv.
Каждая строка в файле csv представляет отдельную запись или строку, которая состоит из отдельных столбцов, разделенных запятыми. Собственно поэтому
формат и называется Comma Separated Values. Но хотя формат csv — это формат текстовых файлов, Python для упрощения работы с ним
предоставляет специальный встроенный модуль csv.

Рассмотрим работу модуля на примере:

import csv

FILENAME = "users.csv"

users = ,
    ,
    
]

with open(FILENAME, "w", newline="") as file:
    writer = csv.writer(file)
    writer.writerows(users)
	

with open(FILENAME, "a", newline="") as file:
	user = 
    writer = csv.writer(file)
    writer.writerow(user)

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

При открытии файла на запись в качестве третьего параметра указывается значение — пустая строка позволяет корректно считывать
строки из файла вне зависимости от операционной системы.

Для записи нам надо получить объект writer, который возвращается функцией . В эту функцию передается открытый файл.
А собственно запись производится с помощью метода Этот метод принимает набор строк. В нашем случае это двухмерный список.

Если необходимо добавить одну запись, которая представляет собой одномерный список, например, , то в этом случае можно вызвать метод
writer.writerow(user)

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

Tom,28
Alice,23
Bob,34
Sam,31

Для чтения из файла нам наоборот нужно создать объект reader:

import csv

FILENAME = "users.csv"

with open(FILENAME, "r", newline="") as file:
    reader = csv.reader(file)
    for row in reader:
        print(row, " - ", row)

При получении объекта reader мы можем в цикле перебрать все его строки:

Tom  -  28
Alice  -  23
Bob  -  34
Sam - 31

Работа со словарями

В примере выше каждая запись или строка представляла собой отдельный список, например, . Но кроме того, модуль csv имеет
специальные дополнительные возможности для работы со словарями. В частности, функция csv.DictWriter() возвращает объект writer,
который позволяет записывать в файл. А функция csv.DictReader() возвращает объект reader для чтения из файла. Например:

import csv

FILENAME = "users.csv"

users = 

with open(FILENAME, "w", newline="") as file:
    columns = 
    writer = csv.DictWriter(file, fieldnames=columns)
    writer.writeheader()
	
    # запись нескольких строк
    writer.writerows(users)
    
    user = {"name" : "Sam", "age": 41}
    # запись одной строки
    writer.writerow(user)

with open(FILENAME, "r", newline="") as file:
    reader = csv.DictReader(file)
    for row in reader:
        print(row, "-", row)

Запись строк также производится с помощью методов и . Но теперь каждая строка представляет собой отдельный словарь,
и кроме того, производится запись и заголовков столбцов с помощью метода writeheader(), а в метод csv.DictWriter в качестве второго параметра
передается набор столбцов.

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

НазадВперед

Запись¶

Аналогичным образом с помощью модуля csv можно и записать файл в формате
CSV (файл csv_write.py):

import csv

data = ,
        'sw1', 'Cisco', '3750', 'London, Best str'],
        'sw2', 'Cisco', '3850', 'Liverpool, Better str'],
        'sw3', 'Cisco', '3650', 'Liverpool, Better str'],
        'sw4', 'Cisco', '3650', 'London, Best str']]


with open('sw_data_new.csv', 'w') as f
    writer = csv.writer(f)
    for row in data
        writer.writerow(row)

with open('sw_data_new.csv') as f
    print(f.read())

В примере выше строки из списка сначала записываются в файл, а затем
содержимое файла выводится на стандартный поток вывода.

Вывод будет таким:

$ python csv_write.py
hostname,vendor,model,location
sw1,Cisco,3750,"London, Best str"
sw2,Cisco,3850,"Liverpool, Better str"
sw3,Cisco,3650,"Liverpool, Better str"
sw4,Cisco,3650,"London, Best str"

Обратите внимание на интересную особенность: строки в последнем столбце
взяты в кавычки, а остальные значения — нет. Так получилось из-за того, что во всех строках последнего столбца есть
запятая

И кавычки указывают на то, что именно является целой строкой.
Когда запятая находится в кавычках, модуль csv не воспринимает её как
разделитель

Так получилось из-за того, что во всех строках последнего столбца есть
запятая. И кавычки указывают на то, что именно является целой строкой.
Когда запятая находится в кавычках, модуль csv не воспринимает её как
разделитель.

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

Модуль csv позволяет управлять этим. Для того, чтобы все строки
записывались в CSV-файл с кавычками, надо изменить скрипт таким образом
(файл csv_write_quoting.py):

import csv


data = ,
        'sw1', 'Cisco', '3750', 'London, Best str'],
        'sw2', 'Cisco', '3850', 'Liverpool, Better str'],
        'sw3', 'Cisco', '3650', 'Liverpool, Better str'],
        'sw4', 'Cisco', '3650', 'London, Best str']]


with open('sw_data_new.csv', 'w') as f
    writer = csv.writer(f, quoting=csv.QUOTE_NONNUMERIC)
    for row in data
        writer.writerow(row)

with open('sw_data_new.csv') as f
    print(f.read())

Теперь вывод будет таким:

$ python csv_write_quoting.py
"hostname","vendor","model","location"
"sw1","Cisco","3750","London, Best str"
"sw2","Cisco","3850","Liverpool, Better str"
"sw3","Cisco","3650","Liverpool, Better str"
"sw4","Cisco","3650","London, Best str"

Теперь все значения с кавычками. И поскольку номер модели задан как
строка в изначальном списке, тут он тоже в кавычках.

Кроме метода writerow, поддерживается метод writerows. Ему можно
передать любой итерируемый объект.

Например, предыдущий пример можно записать таким образом (файл
csv_writerows.py):

import csv

data = ,
        'sw1', 'Cisco', '3750', 'London, Best str'],
        'sw2', 'Cisco', '3850', 'Liverpool, Better str'],
        'sw3', 'Cisco', '3650', 'Liverpool, Better str'],
        'sw4', 'Cisco', '3650', 'London, Best str']]


with open('sw_data_new.csv', 'w') as f
    writer = csv.writer(f, quoting=csv.QUOTE_NONNUMERIC)
    writer.writerows(data)

with open('sw_data_new.csv') as f
    print(f.read())

Как структурированы CSV файлы

Легко создать свой собственный файл CSV. Просто отсортируйте данные, как вы хотите, с помощью одного из инструментов, упомянутых выше, а затем сохраните то, что у вас есть, в формате CSV. Однако, вы также можете создать его вручную, – с нуля, используя любой текстовый редактор.

Вот пример:

Имя, адрес, номер Иван Иванов, 10-я улица, 37

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

В приведенном выше примере показано, как будут выглядеть данные, если файл CSV был открыт в текстовом редакторе. Однако, так как программы электронных таблиц, такие как Excel и OpenOffice Calc, могут открыть CSV файлов, и эти программы содержат ячейки для отображения информации, то значение имя будет расположено в первой ячейке с Иван Иванов в новой строке чуть ниже, а остальные по той же схеме.

Формат CSV

Чтобы понимать суть вещей, нужно разобраться в спецификации CSV файлов, как устроен формат. Давайте коротко…

CSV (Comma-Separated Values — значения, разделённые запятыми) — текстовый формат, предназначенный для представления табличных данных.

• Каждая строка файла — это одна строка таблицы.

• Разделителем значений колонок является символ: (запятая). Для русского языка используется (точка с запятой), потому что в русском запятая используется в дробных числах.

• Значения, содержащие зарезервированные символы: (двойная кавычка, запятая, точка с запятой, новая строка) обрамляются двойными кавычками .

• Если в значении встречаются двойные кавычки , то они должны выглядеть как двое кавычек подряд .

• Строка файла может разделяться символами: или .

Это все что нужно знать, чтобы работать с CSV!

Пример для рус. языка:

1965;Пиксель;E240 – формальдегид (опасный консервант)!;"красный, зелёный, битый";3000,00
1965;Мышка;"А правильней использовать ""Ёлочки""";;4900,00
"Н/д";Кнопка;Сочетания клавиш;"MUST USE! Ctrl, Alt, Shift";4799,00

Пример для англ. языка:

1997,Ford,E350,"ac, abs, moon",3000.00
1999,Chevy,"Venture «Extended Edition»","",4900.00
1996,Jeep,Grand Cherokee,"MUST SELL! air, moon roof, loaded",4799.00

Wiki-справка

Большинство программ под CSV понимают более общий формат DSV (delimiter-separated values — значения разделённые разделителем), допускающий использование иных символов в качестве разделителя. В частности, в русской и других локалях запятая по умолчанию зарезервирована под десятичный разделитель. Поэтому как разделитель используется точка с запятой или табуляция (формат TSV).

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

Другие способы преобразования файлов Excel в CSV

Описанные выше способы экспорта данных из Excel в CSV (UTF-8 и UTF-16) универсальны, т.е. подойдут для работы с любыми специальными символами и в любой версии Excel от 2003 до 2013.

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

Преобразуем файл Excel в CSV при помощи Таблиц Google

Как оказалось, можно очень просто преобразовать файл Excel в CSV при помощи Таблиц Google. При условии, что на Вашем компьютере уже установлен , выполните следующие 5 простых шагов:

1. В Google Drive нажмите кнопку Создать (Create) и выберите Таблица (Spreadsheet).
2. В меню Файл (File) нажмите Импорт (Import).
3. Кликните Загрузка (Upload) и выберите файл Excel для загрузки со своего компьютера.
4. В диалоговом окне Импорт файла (Import file) выберите Заменить таблицу (Replace spreadsheet) и нажмите Импорт (Import).

Совет: Если файл Excel относительно небольшой, то для экономии времени можно перенести из него данные в таблицу Google при помощи копирования / вставки.

1. В меню Файл (File) нажмите Скачать как (Download as), выберите тип файла CSV – файл будет сохранён на компьютере.

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

Сохраняем файл .xlsx как .xls и затем преобразуем в файл CSV

Для этого способа не требуется каких-либо дополнительных комментариев, так как из названия уже всё ясно.

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

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

Сохраняем файл Excel как CSV при помощи OpenOffice

OpenOffice – это пакет приложений с открытым исходным кодом, включает в себя приложение для работы с таблицами, которое отлично справляется с задачей экспорта данных из формата Excel в CSV. На самом деле, это приложение предоставляет доступ к большему числу параметров при преобразовании таблиц в файлы CSV (кодировка, разделители и так далее), чем Excel и Google Sheets вместе взятые.

Просто открываем файл Excel в OpenOffice Calc, нажимаем Файл > Сохранить как (File > Save as) и выбираем тип файла Текст CSV (Text CSV).

На следующем шаге предлагается выбрать значения параметров Кодировка (Character sets) и Разделитель поля (Field delimiter). Разумеется, если мы хотим создать файл CSV UTF-8 с запятыми в качестве разделителей, то выбираем UTF-8 и вписываем запятую (,) в соответствующих полях. Параметр Разделитель текста (Text delimiter) обычно оставляют без изменения – кавычки (“). Далее нажимаем ОК.

Таким же образом для быстрого и безболезненного преобразования из Excel в CSV можно использовать ещё одно приложение – LibreOffice. Согласитесь, было бы здорово, если бы Microsoft Excel предоставил возможность так же настраивать параметры при создании файлов CSV.

Как структурированы csv файлы

Шаблоны CSV или файлы данных можно загрузить по ссылкам в верхней части инструмента «Загрузить данные». Первая строка шаблона или файла данных содержит заголовки столбцов. Каждая последующая строка соответствует записи в базе данных. Когда загружается шаблон CSV, он содержит только заголовки столбцов. Поскольку шаблоны используются для добавления новых записей, новые строки будут добавляться для каждой записи.

Когда документ данных CSV загружается, первая строка содержит заголовок столбца, а последующие строки содержат записи данных, которые уже существуют в базе данных. Записи в этих строках можно редактировать или удалять.

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

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

Большинство значений заключено в двойные кавычки. Исключением является односимвольное значение, например 1 или 0 (ноль). Заключение значения в двойные кавычки позволяет использовать в поле сложные значения, например, содержащие запятые, без нарушения структуры документа. Например, поле, содержащее ряд элементов, например избранные цвета, может иметь такое значение:

“красный, зеленый и синий”

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

Важная информация о редактировании файлов CSV

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

Однако, иногда вы можете отредактировать файл CSV или создать его с нуля, и в этом случае следует учитывать следующее:

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

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

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

Нет проблем, если вы понимаете это ограничение. Вот почему существуют другие, более продвинутые форматы таблиц, такие как XLSX. Другими словами, если вы хотите сохранить в CSV любую работу, кроме базовых изменений данных, не используйте CSV – вместо этого сохраните или экспортируйте в более расширенный формат.

Как преобразовать файл Excel в CSV

Если требуется экспортировать файл Excel в какое-либо другое приложение, например, в адресную книгу Outlook или в базу данных Access, предварительно преобразуйте лист Excel в файл CSV, а затем импортируйте файл .csv в другое приложение. Ниже дано пошаговое руководство, как экспортировать рабочую книгу Excel в формат CSV при помощи инструмента Excel – «Сохранить как».

1. В рабочей книге Excel откройте вкладку Файл (File) и нажмите Сохранить как (Save as). Кроме этого, диалоговое окно Сохранение документа (Save as) можно открыть, нажав клавишу F12.
2. В поле Тип файла (Save as type) выберите CSV (разделители – запятые) (CSV (Comma delimited)).Кроме CSV (разделители – запятые), доступны несколько других вариантов формата CSV:
   • CSV (разделители – запятые) (CSV (Comma delimited)). Этот формат хранит данные Excel, как текстовый файл с разделителями запятыми, и может быть использован в другом приложении Windows и в другой версии операционной системы Windows.
   • CSV (Macintosh). Этот формат сохраняет книгу Excel, как файл с разделителями запятыми для использования в операционной системе Mac.
   • CSV (MS-DOS). Сохраняет книгу Excel, как файл с разделителями запятыми для использования в операционной системе MS-DOS.
   • Текст Юникод (Unicode Text (*txt)). Этот стандарт поддерживается почти во всех существующих операционных системах, в том числе в Windows, Macintosh, Linux и Solaris Unix. Он поддерживает символы почти всех современных и даже некоторых древних языков. Поэтому, если книга Excel содержит данные на иностранных языках, то рекомендую сначала сохранить её в формате Текст Юникод (Unicode Text (*txt)), а затем преобразовать в CSV, как описано далее в разделе .

Замечание: Все упомянутые форматы сохраняют только активный лист Excel.

1. Выберите папку для сохранения файла в формате CSV и нажмите Сохранить (Save).После нажатия Сохранить (Save) появятся два диалоговых окна. Не переживайте, эти сообщения не говорят об ошибке, так и должно быть.
2. Первое диалоговое окно напоминает о том, что В файле выбранного типа может быть сохранён только текущий лист (The selected file type does not support workbooks that contain multiple sheets). Чтобы сохранить только текущий лист, достаточно нажать ОК.Если нужно сохранить все листы книги, то нажмите Отмена (Cancel) и сохраните все листы книги по-отдельности с соответствующими именами файлов, или можете выбрать для сохранения другой тип файла, поддерживающий несколько страниц.
3. После нажатия ОК в первом диалоговом окне, появится второе, предупреждающее о том, что некоторые возможности станут недоступны, так как не поддерживаются форматом CSV. Так и должно быть, поэтому просто жмите Да (Yes).

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

Стандартизация

Имя «CSV» указывает на использование запятой для разделения полей данных. Тем не менее, термин «CSV» широко используется для обозначения большого семейства форматов, которые во многом различаются. Некоторые реализации позволяют или требуют одинарные или двойные кавычки вокруг некоторых или всех полей; а некоторые резервируют первую запись как заголовок, содержащий список имен полей. Используемый набор символов не определен: некоторым приложениям требуется метка порядка байтов Unicode (BOM) для обеспечения интерпретации Unicode (иногда даже BOM UTF-8 ). Файлы, в которых вместо запятой используется символ табуляции, можно более точно назвать «TSV» для значений, разделенных табуляцией .

Другие отличия реализации включают обработку более распространенных разделителей полей (таких как пробел или точка с запятой) и символов новой строки внутри текстовых полей. Еще одна тонкость — это интерпретация пустой строки: она может в равной степени быть результатом записи записи из нулевых полей или записи одного поля нулевой длины; при этом расшифровка неоднозначна.

Пакет табличных данных без трения OKI

В 2011 году Open Knowledge Foundation (OKF) и различные партнеры создали рабочую группу по протоколам данных, которая позже превратилась в инициативу Frictionless Data. Одним из основных форматов, которые они выпустили, был пакет табличных данных. Пакет табличных данных в значительной степени основан на CSV, используя его в качестве основного формата передачи данных и добавляя метаданные базового типа и схемы (в CSV отсутствует какая-либо информация о типе, чтобы отличить строку «1» от числа 1).

Инициатива Frictionless Data Initiative также предоставила стандартный формат описания диалектов CSV для описания различных диалектов CSV, например, для указания разделителя полей или правил цитирования.

Стандарт табличных данных Internet W3C

В 2013 году рабочая группа W3C «CSV в Интернете» начала определять технологии, обеспечивающие повышенную совместимость веб-приложений, использующих CSV или аналогичные форматы. Рабочая группа завершила свою работу в феврале 2016 года и официально закрылась в марте 2016 года с выпуском набора документов и рекомендаций W3C по моделированию «табличных данных» и расширению CSV метаданными и семантикой .

RFC 4180 стандарт

Технический стандарт 2005 года RFC 4180 формализует формат файла CSV и определяет тип MIME «text / csv» для обработки текстовых полей. Однако интерпретация текста каждого поля по-прежнему зависит от приложения. Файлы, соответствующие стандарту RFC 4180, могут упростить обмен CSV и должны быть широко переносимыми. Среди его требований:

Строки в стиле MS-DOS, которые заканчиваются символами (CR / LF) (необязательно для последней строки).
Необязательная запись заголовка (нет надежного способа определить, присутствует ли она, поэтому при импорте требуется осторожность).
Каждая запись должна содержать одинаковое количество полей, разделенных запятыми.
Любое поле можно заключить в кавычки (в двойные кавычки).
Поля, содержащие перенос строки, двойные кавычки или запятые, должны быть заключены в кавычки. (В противном случае файл, скорее всего, будет невозможно правильно обработать.)

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

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

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