Преобразовать CSV в Excel в виде таблицы

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

Как конвертировать CSV в формат Excel

Существуют различные способы переноса документа CSV, загруженного из базы данных, в Excel. Мы обсудим три из них:

1. Прямое открытие в Excel.
2. Открытие через проводник Windows.
3. Импортируйте документ с изменением формата.

Открытие документа CSV в Excel

Excel может открывать документы .csv напрямую без преобразования. Формат не меняется после открытия таким способом, сохраняется расширение .csv — хотя расширение можно изменить после редактирования.

1. Запустите Excel, нажмите «Файл", Затем"Откройте».
2. В появившемся окне выберите опцию «Текстовые файлыиз расширенного списка.

3. Найдите нужный документ и дважды щелкните по нему.

Документы формата .csv открываются в Excel сразу, без каких-либо дополнительных манипуляций. А вот файлы .txt потребуют конвертации — появится окно «Мастера импорта текста».

Бывают случаи, когда лучше не открывать документ напрямую, а вызвать Master. Такой подход оправдан в следующих случаях:

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

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

Если вы внесете изменения в документ после его открытия, Excel предложит вам сохранить его как .xls (или .xlsx), поскольку в противном случае часть форматирования будет потеряна. Тогда всегда будет возможность изменить формат обратно, однако при этом может потеряться еще часть контента – могут исчезнуть нули в начале чисел, часть записей может изменить свой внешний вид.

Открытие документа CSV через проводник Windows

Этот путь принципиально не отличается от предыдущего. Чтобы открыть документ, просто дважды щелкните его в проводнике Windows.

Перед открытием убедитесь, что рядом с названием документа стоит значок программы Excel, а не какой-то другой – это означает, что Excel выбран в качестве программы, которая должна открывать такие файлы. В противном случае откроется какая-то другая программа. Вы можете изменить его на Excel следующим образом:

1. Вызовите контекстное меню, щелкнув правой кнопкой мыши по любому файлу .csv и примените Открыть с помощью… > Выбрать программу.
2. Выбирать Excel (рабочий стол) of Рекомендуемые программы, назначьте ее как программу, которую всегда следует использовать для таких файлов (поставьте галочку ниже), и закройте окно, нажав OK.

Импортировать CSV в Excel

Также можно превратить открытый документ в книгу Excel. Формат изменится на .xls для более ранних версий Excel (2000, 2003) и .xlsx для всех остальных. Весь контент будет отображаться на одном листе.

1. Нажмите на ячейку на листе, с которой должен начаться импорт. Обычно это самая первая ячейка в таблице. A1. Начиная с него будет заполнено столько строк, сколько есть в открытом файле, и столько столбцов, сколько значений есть в каждом столбце.
2. Во вкладке «Данные" в группе "Получение внешних данных» Выбрать "Из текста».

3. В появившемся окне найдите необходимый документ и дважды щелкните по нему (можно также воспользоваться кнопкой Импортировать внизу окна).

4. Далее необходимо следовать пошаговым инструкциям открывшегося Мастера импорта текста.

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

Шаг 1. Мастер предложит вам выбрать тип файла. – как можно догадаться, это будет»с сепараторами" (по-английски - разделенный), и строка, с которой начнется передача контента – скорее всего, нужно начать с условия 1если вы не хотите передавать только часть контента. В окне ниже будут показаны первые строки из выбранного документа.

Шаг 2. Теперь нужно понять, какой из них используется разделитель (их может быть несколько) в файле и укажите этот символ в Магистратура. У него есть возможность выбора стандартных разделителей, но если в документе используется нетипичный символ, вы можете выбрать Другое и введите нужный символ. В файле, используемом для демонстрации, разделители: Запятая и Tab. Запятые отделяют ячейки с характеристиками продукта, такими как серийный номер и количество проданных экземпляров, а табуляции отделяют один продукт от другого — информация о каждом должна начинаться с новой строки.

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

Шаг 3. Здесь достаточно посмотреть превью и, если оно не показывает недопустимых недостатков, нажать кнопку Завершить. Может случиться так, что некоторые значения будут разделены не одним разделителем, а несколькими, в результате между ними появятся ячейки без значений. Чтобы этого не произошло, установите флажок Рассматривать последовательные разделители как один.

5. Выберите путь назначения (это может быть новый или существующий лист) и нажмите OKдля завершения процесса импорта.

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

Иногда результат конвертации далек от ожидаемого. О том, как можно изменить этот результат, пойдет речь в следующем разделе статьи.

Проблемы при конвертации и их решения

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

Разделители текста также могут различаться – чаще всего это либо кавычки, либо знак порядка байтов. Разделителей может не быть вообще — тогда символ, используемый в качестве разделителя, всегда воспринимается таковым (тогда это обычно не запятая — она слишком часто используется в тексте — а другой, менее распространенный символ).

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

Файл не открывается корректно

Доказательства. Все содержимое документа помещается в первый столбец.

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

Решения. Существуют разные способы решения этой проблемы:

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

• изменить разделитель на запятую: сентябрь
• изменить на точку с запятой: сент =;

Еще один персонаж, написанный после сентябрь = в начальной строке также станет разделителем.

2. Символ-разделитель, используемый в файле, также можно указать в самом Excel. В версиях 2016, 2013 или 2010 для этого нужно открыть вкладку Данные и выберите «Текст по столбцам» в группе "Работа с данными».

Откроется окно «Мастера распределения текста по столбцам». Там из предложенных форматов данных нужно выбрать те, что с разделителями. Затем вам нужно нажать Следующая и, выбрав разделитель, Завершить.

3. Чтобы автоматически открыть документ с помощью Мастер импорта, и не только в листе Excel, расширение можно изменить с .csv на .txt. В Магистратура в качестве разделителя можно указать любой символ – как это сделать, в статье объяснялось ранее.
4. Используйте ВБА. Вы можете использовать, например, – подходит для Excel 2000 или 2003. Код можно изменить, чтобы он подходил для других версий.

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

5. Изменить разделитель и десятичную точку в настройках компьютера

В Панель управления, вызывается кнопкой Start, Выбрать "Дополнительные опции" из списка»Региональные стандарты». После нажатия появится окноНастройка формата» — в нем вы можете выбрать «Разделитель списка» и разделитель целой и дробной частей числа. Если в качестве разделителя файлов требуется запятая, сначала установите точку в виде десятичной точки. Может оказаться наоборот — в качестве символа-разделителя понадобится точка с запятой. Тогда для дробей можно оставить любой знак, конфликта это не вызовет.

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

Удалить ведущие нули

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

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

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

Некоторые значения выглядят как даты

Войти. Даты отображают значения, которые изначально представляют собой обычный текст или числа.

Вызывать. Общий формат предполагает преобразование значений в даты, напоминающие таковые для Excel. Если документ CSV содержит одно значение, например may12, то на листе Excel оно будет отображаться как дата.

Решения. Аналогично тому, что было в предыдущем случае. Измените расширение на .txt, в Магистратура изменить формат значений, преобразованных в даты, в текстовый.

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

Как импортировать несколько файлов CSV в Excel

Excel может открывать несколько файлов CSV одновременно.

1. Нажмите Файл> Открыть и выберите опцию Текстовые файлы из раскрывающегося списка ниже.
2. Чтобы выбрать несколько файлов рядом, сначала выберите первый, затем нажмите Shift и нажмите на последний. Помимо выбранных файлов, будут выбраны все файлы между ними.
3. Нажмите Откройте.

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

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