Перетворіть CSV на Excel як таблицю

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

Як конвертувати CSV у формат Excel

Існують різні способи перенесення документа CSV, завантаженого з бази даних, до Excel. Ми обговоримо три з них:

1. Пряме відкриття в Excel.
2. Відкриття через Windows Explorer.
3. Імпорт документа зі зміною формату.

Відкриття документа CSV в Excel

Excel може відкривати документи .csv безпосередньо без конвертації. Формат не змінюється після відкриття таким чином, розширення .csv зберігається – хоча розширення можна змінити після редагування.

1. Запустіть Excel, натисніть «філе", Потім"відкритий".
2. У вікні, що з’явиться, виберіть опцію «Текстові файлиз розгорнутого списку.

3. Знайдіть потрібний документ і двічі клацніть по ньому.

Документи .csv відкриваються в Excel відразу, без додаткових маніпуляцій. Але файли .txt потребують конвертації – з’явиться вікно «Майстри імпорту тексту".

Бувають випадки, коли документ краще не відкривати безпосередньо, а телефонувати Майстер. Такий підхід виправданий у наступних випадках:

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

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

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

Відкриття документа CSV через Windows Explorer

Цей шлях принципово не відрізняється від попереднього. Щоб відкрити документ, просто двічі клацніть його в Провіднику 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. Тепер потрібно зрозуміти, який з них використовується роздільник (їх може бути більше одного) у файлі та вкажіть цей символ у майстри. Він має варіанти вибору зі стандартних роздільників, але якщо в документі використовується нетиповий символ, ви можете вибрати Інше і введіть потрібний символ. У файлі, який використовується для демонстрації, роздільниками є − Кома и таб. Коми відокремлюють клітинки з характеристиками товару, як-от серійний номер і кількість проданих примірників, а вкладки відокремлюють один продукт від іншого – інформація про кожен має починатися з нового рядка.

Це також необхідно визначити і конкретизувати роздільник тексту. Це символ, який розміщується перед і після кожного фрагмента тексту, який повинен бути розташований в одній комірці. Завдяки роздільнику кожен такий сегмент сприймається як окреме значення, навіть якщо всередині нього є символи, обрані для розділення значень. У нашому документі кожне значення взято в лапки – тому, навіть якщо воно містить кому (наприклад, «початок, потім продовження»), весь його текст буде розміщено в одній клітинці, а не в двох підряд.

Крок 3. Тут достатньо подивитися на превью і, якщо в ньому немає неприйнятних недоліків, натиснути обробка. Може статися так, що деякі значення будуть розділені не одним роздільником, а декількома, в результаті між ними виникнуть комірки без значень. Щоб цього не сталося, установіть прапорець Розглядайте послідовні роздільники як один.

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

Ви також можете натиснути Матеріали — є й інші можливості. Наприклад, ви можете форматувати вміст, налаштовувати розмітку та спосіб оновлення інформації.

Іноді результат конвертації далекий від очікуваного. Про те, як цей результат можна змінити, буде розглянуто в наступному розділі статті.

Проблеми під час перетворення та їх вирішення

За весь час існування формату CSV ніхто не потурбувався офіційно задокументувати його. Таким чином, хоча передбачається, що для розділення значень слід використовувати коми, насправді в різних базах даних використовуються різні роздільники – крапка з комою, табуляція та інші.

Роздільники тексту також можуть бути різними – найчастіше це або лапки, або знак порядку байтів. Розділювачів може не бути взагалі – тоді символ, який використовується як роздільник, завжди сприймається як такий (тоді це зазвичай не кома – вона надто часто використовується в тексті – а інший, менш поширений символ).

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

Файл не відкривається належним чином

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

Викликати. У документі як роздільник використовується символ, який не визначено як такий у налаштуваннях комп’ютера або зарезервовано для іншої функції. Наприклад, кома може бути зарезервована для розділення десяткових частин числа, і тому не може розділяти значення у файлі.

Рішення. Існують різні способи вирішення цієї проблеми:

1. Змініть роздільний символ у самому документі. Відкрийте його в Блокноті або подібному редакторі і в початковому рядку (порожньому, всі дані повинні бути в рядках нижче) введіть наступний текст:

• щоб змінити роздільник на кому: Вересень
• щоб змінити на крапку з комою: sep=;

Інший персонаж, написаний після вер = у початковому рядку також стане роздільником.

2. Символ роздільника, який використовується у файлі, також можна вказати в самому Excel. У версіях 2016, 2013 або 2010 для цього потрібно відкрити вкладку дані і виберіть “Текст за колонками» в групі"Робота з даними».

Це відкриє вікно "Майстри розподілу тексту по колонках». Там із запропонованих форматів даних потрібно вибрати ті, що мають роздільники. Потім потрібно натиснути МАЙБУТНІ і, вибравши роздільник, обробка.

3. Щоб автоматично відкрити документ за допомогою Майстер імпорту, а не лише на аркуші Excel, розширення можна змінити з .csv на .txt. AT майстри як роздільник можна вказати будь-який символ – як це зробити, розповідалося в статті раніше.
4. Використовуйте VBA. Ви можете використовувати, наприклад, – підходить для 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. Якщо на перший погляд щось здається незрозумілим, просто виконайте вказані дії, і все стане зрозуміло.