SQL, Анализ данных

Конвертация данных из формата CSV в XLSX

Время прочтения: 3 мин.

Самый простой и самый рискованный способ открыть файл CSV с помощью Excel, если он не превышает 1 048 576 строк – двойной клик по нему левой кнопкой мыши. Опасность скрывается в том, что Excel по умолчанию применяет к данным из CSV-файла формат «Общий» (General) и вносит изменения в виде удаления лидирующих нулей, округления чисел до двух символов после запятой, перевода к экспоненциальной форме (если число более 15 цифр, исходное значение теряется), преобразования числа в формат даты, и это не все возможные варианты.

Но есть способ избежать этих изменений с помощью встроенного в Excel механизма импорта.

Для этого необходимо создать новый файл Excel, в нем зайти в «Данные» => «Получение внешних данных» => «Из текста». В открывшемся обозревателе найти и выбрать свой CSV-файл, нажать «Импорт», появится окно «Мастер текстов (импорт)».

Шаг №1 – выбрать формат данных «с разделителями», в списке с форматами файла выбрать тот, с которым в окне предварительного просмотра все символы станут читаемыми (чаще всего используют: «65001:Юникод(UTF-8)», «20127:Windows-1251», «Windows(ANSI)»).

Шаг №3 – В образце разбора данных передвигаем «бегунок» вправо до конца => зажимаем «левый Shift» => «левой кнопкой мыши» кликаем на последнюю колонку => в «формат данных столбца» выбираем текстовый => «Готово».

После нажатия «Готово» встроенный в Excel механизм импорта открывает CSV файл без изменения исходной информации.

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

Самый эффективный способ борьбы — исключить их в момент выгрузки. Например, наш SQL запрос:

SELECT <COL_NAME1>,
       <COL_NAME2>,
       <COL_NAME3>
FROM <TABLE_NAME>

Символы «переноса» или «табуляции» содержатся в колонке «<COL_NAME2>». Добавляем функцию REPLACE и заменяем данные символы на пробелы:

SELECT <COL_NAME1>,
       REPLACE(
REPLACE(
REPLACE(<COL_NAME2>,CHAR(10),' '),
CHAR(13),' '),
CHAR(9),' ') AS <COL_NAME2>,
       <COL_NAME3>
FROM <TABLE_NAME>

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

import csv
import pandas as pd
#Чтение CSV + игнорирование ошибок
encoding = 'utf-8'
input_stream = open("ТЕКСТ.csv",errors="ignore")
big_df = pd.read_csv(input_stream,sep=';',encoding=encoding,dtype="str")
#Записи в файл "xlsx" (Название листа "sheet_name", убрать колонки с индексами "index=False")
big_df.to_excel('./ТЕКСТ_v2.xlsx', sheet_name='Лист1',  index=False)
Советуем почитать