Импорт csv и txt файлов в excel c помощью power query

Постановка задачи

Бьюсь об заклад, что у 99% средних и крупных организаций в России инфраструктура построена на использовании Active Directory. Активный каталог замечательная вещь и легко интегрируется в различные сторонние сервисы. Самая частая задача администратора, это получение отчетов или выгрузок по определенным критериям, например:

• Получить список пользователей Active Directory с рядом атрибутов
• Получить список серверов у которых операционная система Windows Server 2008 R2, чтобы в дальнейшем их вывести из эксплуатации
• Получить список всех компьютеров или пользователей, кто не авторизовывался какое-то время. И так до бесконечности

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

Как сохранить данные Excel как CSV без закрытия Excel файла

Когда вы конвертируете Excel файлы через «Сохранить как…CSV», появляется сообщение: «Некоторые возможности станут недоступными». Далее, похоже, что ваш исходный XLSX-файл замещён CSV-файлом. На самом же деле, ваш исходный файл Excel был автоматически закрыт. Вместо него в интерфейсе Excel открываться только что сгенерированный файл CSV. Поэтому после каждой конвертации приходится снова и снова открывать исходный документ Excel. Это отнимает время и вносит риск потери несохранённых изменений исходных данных.

Надстройка «Экспорт в CSV» оптимизирует рутинный экспорт XLSX-в-CSV:

Выбелите диапазон > Нажмите «Экспорт в CSV» > Установите параметры CSV файла.
Нажмите ОК > Присвойте имя и сохраните файл на свой компьютер.
Готово

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

Вы можете также «Открыть CSV файл после экспорта», чтобы сразу увидеть результат. Файл откроется в новом окне Excel.

Появились вопросы или предложения? Оставьте комментарий ниже.

Как открыть CSV в Excel

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

Как открыть CSV в Excel – Неформатированный файл CSV в Excel

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

1. Перейдите во вкладку «Данные», и в группе «Получение внешних данных» выберите пункт «Из текста».

Как открыть CSV в Excel – Получение внешних данных из текста в Excel

1. В открывшемся окне найдите в вашем компьютере CSV файл, который хотите открыть в Excel. Выберите его и нажмите кнопку «Импорт» либо дважды кликните по файлу.

Как открыть CSV в Excel – Импорт CSV файла в Excel

1. Далее появится окно мастера импорта текстов.

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

В поле «Начать импорт со строки» мы указываем номер первой строки импортируемых данных. В нашем случае начнем с первой строки. И нажимаем кнопку «Далее».

Как открыть CSV в Excel – Импорт CSV файла. Шаг 1

1. Шаг 2. На втором этапе мы выбираем разделители и ограничители строк.

В поле «Символом-разделителем является:» мы выбираем символ, который разделяет значения в текстовом файле CSV. В нашем случае это запятая. Если в вашем CSV файле разделителями выступают другие символы, или он отсутствует в списке, то выбирайте пункт «другой:» и введите его в поле.

В поле «Ограничитель строк» мы выбираем “. Нажимаем кнопку «Далее». 

Как открыть CSV в Excel – Импорт CSV файла. Шаг 2

1. Шаг 3. В группе «Формат данных столбца» задаем формат данных для каждого столбца в предпросмотре «Образец разбора данных».

Как открыть CSV в Excel – Импорт CSV файла. Шаг 3

В случае, если какой-то столбец не нужно импортировать из CSV файла выберите пункт пропустить.

Как открыть CSV в Excel – Пропустить столбец при импорте CSV в Excel

Нажимаем кнопку «Готово».

1. После мастера импорта текста появится окно «Импорт данных»:

Как открыть CSV в Excel – Окно «Импорт данных»

В группе «Куда следует поместить данные» выбираем расположение наших импортируемых данных из CSV в Excel. Если выбираем поле «Имеющийся лист», то указываем адрес ячейки. А если хотим расположить на новом листе, то – пункт «Новый лист». Мы выберем «Имеющийся лист». Нажимаем кнопку «ОК».

В итоге мы получили вот такую таблицу:

Как открыть CSV в Excel – CSV файл в Excel

Ну вот и все, теперь вы с легкостью можете открыть CSV в Excel, и далее работать с полученными табличными данными привычными инструментами MS Excel.

Изменение разделителя в Excel

При создании CSV-файла Excel использует разделитель, установленный в региональных настройках Windows или iOS.

Поменять стандартный разделитель Excel на Windows, можно с помощью инструкции «Изменение разделителя списка для новых текстовых файлов» в статье Microsoft Office.

Если вы используете Excel на операционной системе macOS, вы можете изменить разделитель, перейдя в Системные настройки → Язык и регион → Дополнительно → Общие → Разделители чисел.

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

Экспорт в CSV из Excel: экспорт выбранных диапазонов без закрытия исходного файла

Популярность текстовых файлов CSV объясняется тем, что этот формат поддерживается большинством приложений. Поэтому конвертация файлов Excel в файлы CSV с разделителями-запятыми вполне привычная операция. Но что если вам нужно экспортировать не весь лист, а только выбранный диапазон, или применить другой разделитель? Кроме того, операция «Сохранить как CSV» автоматически закрывает книгу Excel — и это осложняет работу при необходимости экспортировать данные многократно.

Надстройка «Экспорт в CSV» упрощает этот рутинный процесс:

• Конвертация диапазонов из XLSX (XLS) в CSV
• Выбор разделителя: запятая, точка с запятой, табуляция, вертикальная черта
• Экспорт в CSV значений или значений с форматом
• Сохранить как CSV без закрытия исходного файла Excel
• Корректный экспорт объединённых ячеек

Язык видео: английский. Субтитры: русский, английский

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

Экспорт таблицы (диапазона ячеек) в CSV

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

В данном случае поможет экспорт заданного диапазона ячеек в файл CSV, что проще всего сделать макросом с использованием функции Range2CSV:

Вот код самой функции Range2CSV:

Улучшенная версия кода (работает заметно быстрее), и дополнительно заключает текст всех ячеек в кавычки:

Для работы макроса понадобится ещё и функция сохранения текстового файла SaveTXTfile. Найти её можно здесь: http://excelvba.ru/code/txt

30970 просмотров

Как csv разделить по столбцам через Google Таблицы

Не так давно я вам рассказывал про установку через групповую политику расширения для Google Chrome под названием “Редактирование файлов Office“. Это расширение позволяло работать с файлами Word, Excel и PowerPoint в Google Документах, Google Таблицах и Google Презентациях. Если вы через него откроете ваш файл CSV, то вы буквально в два клика сможете его разбить на столбцы. Для этого выберите меню “Файл – Сохранить в формате Google Таблиц“.

Начнется переделывание формата CSV в формат Google Таблиц

На выходе вы получаете разделенный по столбцам документ.

Далее вы уже можете оперировать этими данными или же можете их сохранить в нужный формат. Если у вас изначально документ Google Таблиц содержит данные разделенные запятой в виде одной строки, то вы их можете преобразовать в столбцы вот таким методом. Открываете меню “Данные – Разделить текст на столбцы“

Выбираете тип разделения, в моем случае запятая.

Затем выбираете ваш столбец с данным и еще раз нажмите “Данные – Разделить текст на столбцы“

В результате чего они будут разбиты на аккуратные столбцы

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

Иногда возникает необходимость работы с файлами, которые имеют расширение *.csv

Что такое csv файл?

Это текстовый файл, в котором содержится информация.

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

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

Пример файла CSV- формата:

После преобразования в табличный вид будет выглядеть так:

Теперь рассмотрим три способа преобразования CSV формата в «табличное» представление.

Зайдем в модуль Ученики, выделим несколько учеников установив флажок напротив необходимой записи зайдем в Действие и выберем Экспортировать.

Получим файл с расширением csv.

Открыв его в excel, мы увидим не очень «дружелюбное» представление информации

Выделяем весь первый столбец, переходим во вкладку «Данные» и выберем пункт «Текст по столбцам»

Далее проходим три шага согласно подсказкам.

Выбираем знак табуляции

На третьем шаге, вы сможете для каждого столбца выбрать необхоимый формат.

После нажатия кнопки «Готово», получаем «читабельный табличный» формат файла.

Можем воспользоваться таким свободным и открытым офисным пакетом как Apache OpenOffice (Не реклама).

Для этого необходимо открыть файл CSV формата с помощью OpenOffice.

При открытии файла, OpenOffice, предложит импортировать текст в «читаемый табличный» вид.

В «Параметры разделителя» выбираем несколько вариантов (табуляция, точка с запятой, запятая…) и нажимаем кнопку «Ок». Файл будет преобразован в «табличный» вид.

И еще один вариант преобразования, это открыть файл в любом текстовом редакторе

например в Блокноте. Первой строкой прописываем «sep=»

и сохраняем файл.

Теперь открыв файл в excel

получаем «читабельный текстовый» формат файла.

Процедура открытия

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

Способ 1: BeCSV

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

Способ 2: ConvertCSV

Ещё одним онлайн-ресурсом, на котором можно производить различные манипуляции с объектами формата CSV, в том числе и просмотр их содержимого, является популярный сервис ConvertCSV.

1. Перейдите на главную страницу ConvertCSV по представленной выше ссылке. Далее щелкните по пункту «CSV Viewer and Editor».

Откроется раздел, в котором можно онлайн не только просматривать, но и редактировать CSV. В отличие от предыдущего метода, данный сервис в блоке «Select your input» предлагает сразу 3 варианта добавления объекта:

• Выбор файла с компьютера или с подключенного к ПК дискового носителя;
• Добавление ссылки на размещенный в интернете CSV;
• Ручная вставка данных.

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

При добавлении размещенного на компьютере CSV щелкните напротив опции «Choose a CSV/Excel file» по кнопке «Выберите файл».

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

После того как вы щелкнули по вышеуказанной кнопке, объект будет загружен на сайт и его содержимое отобразится в табличном виде прямо на странице.

Если же вы желаете просмотреть содержимое файла, который размещен во всемирной паутине, в этом случае напротив опции «Enter an URL» введите полный его адрес и щелкните по кнопке «Load URL». Результат будет представлен в табличном виде, как и при загрузке CSV с компьютера.

Из двух рассмотренных веб-сервисов ConvertCSV является несколько более функциональным, так как позволяет производить не только просмотр, но и редактирование CSV, а также выполнять загрузку исходника из интернета. Но для простого просмотра содержимого объекта возможностей сайта BeCSV тоже будет вполне достаточно.

Опишите, что у вас не получилось.
Наши специалисты постараются ответить максимально быстро.

Как открыть csv в Excel (файл правильно в Эксель)

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

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

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

Попробуем открыть текстовый файл в формате «csv». Выбираем наш файл и вызываем контекстное меню – «Открыть с помощью» – здесь уже выбираем программу, которая будет запускаться для открытия нашего файла из списка либо уже ранее выбранный Excel.

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

Файл «csv» также можно открыть непосредственно из самой программы.

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

Далее необходимо заполнить все поля в появившемся диалоговом окне «Мастера функций». Укажем формат данных «с разделителями», в формате файла «Юникод» и применим операцию.

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

В нашем случае разделитель «точка с запятой». В английской версии программного обеспечения нужно будет выбрать «запятую».

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

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

Еще одним способом открыть файл «csv» – через меню программы. Для этого необходимо перейти во вкладку «Файл» – «Открыть».

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

Как csv разделить +по столбцам в Excel

Первым инструментом, который позволит из csv получить красивые столбцы и разбиение по ним, будет Excel. Откройте в Excel ваш файл csv.

• Выделите первый столбец и перейдите на вкладку “Данные“
• Найдите пункт “Текст по столбцам“

На первом окне мастера распределения текста по столбцам нажмите “Далее”.

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

• Знак табуляции
• Точка с запятой
• Запятая
• Пробел
• Другой вариант

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

Далее при необходимости вы можете указать формат (Общий, текстовый, дата) и диапазон к которому будет применяться ваше преобразование. Нажимаем “Готово“.

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

Далее вам остается сохранить файл в формате xlsx и радуемся жизни.

Онлайн сервис для нормализации CSV-файлов

Впрочем, все выше изложенные проблемы решает простой онлайн сервис Normalization CSV. Он позволяет менять кодировку и символ разделителя. Правда существует ограничение в 64 000 байтов (сколько это в Кб?) для загружаемого файла, но CSV-файлы обычно весят не очень много, должно хватить. Результат обработки выводится как обычный текстовый файл, его можно сохранить с расширением .csv .

К слову, скрипт преобразовавает значение процентов, например: 95 % – в нужное значение для столбца «проценты» в Excel, т.е. делится на 100 и получаем число с плавающей запяток, например: 0,95 . Если нужны ещё какие-то преобразования: пишите, попробуем доработать.

Ну, а если предложенный онлайн сервис вас не устраивает, вы всегда можете воспользоваться выше приведённой инструкцией.

Парсинг формата CSV

На первый взгляд может показаться, что мы можем тупо использовать 2 раза метод split(), поскольку текст мы можем разбить на массив строк при помощи , а строку на ячейки при помощи , поскольку в качестве разделителя у нас используется запятая. Это не совсем так, если у нас в некоторых ячейках будет текст, содержащий в себе запятые, наш алгоритм будет работать неправильно.

Давайте добавим немного запятых в исходные данные(test.csv):

1,"John, Smith",1961
2,"Mike, Trump",1975
3,"Oliver, Stone",1982

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

var CSVToArray = function ( strData, strDelimiter ){
	
	strDelimiter = (strDelimiter || ",");

	var objPattern = new RegExp(
		(
			"(\\" + strDelimiter + "|\\r?\\n|\\r|^)" +          
			"(?:\"(*(?:\"\"*)*)\"|" +               
			"(*))"
		),
		"gi"
		);

	var arrData = ];
	var arrMatches = null;

	while (arrMatches = objPattern.exec( strData )){           
		var strMatchedDelimiter = arrMatches;
		if (
			strMatchedDelimiter.length &&
			strMatchedDelimiter !== strDelimiter
			){
			arrData.push( [] );
		}
		var strMatchedValue;
		if (arrMatches){
			strMatchedValue = arrMatches.replace(
				new RegExp( "\"\"", "g" ),
				"\""
				);
		} else {
			strMatchedValue = arrMatches;
		}
		arrData.push( strMatchedValue );
	}

	return( arrData );
}

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

Итоговый скрипт чтения и парсинга файла формата CSV для iMacros+JS:

var loadCSVFile = function (fileName) {

	this.CSVToArray = function ( strData, strDelimiter ){
		
		strDelimiter = (strDelimiter || ",");

		var objPattern = new RegExp(
			(
				"(\\" + strDelimiter + "|\\r?\\n|\\r|^)" +          
				"(?:\"(*(?:\"\"*)*)\"|" +               
				"(*))"
			),
			"gi"
			);

		var arrData = ];
		var arrMatches = null;

		while (arrMatches = objPattern.exec( strData )){           
			var strMatchedDelimiter = arrMatches;
			if (
				strMatchedDelimiter.length &&
				strMatchedDelimiter !== strDelimiter
				){
				arrData.push( [] );
			}
			var strMatchedValue;
			if (arrMatches){
				strMatchedValue = arrMatches.replace(
					new RegExp( "\"\"", "g" ),
					"\""
					);
			} else {
				strMatchedValue = arrMatches;
			}
			arrData.push( strMatchedValue );
		}

		return( arrData );
	}

	var fileDescriptor = imns.FIO.openNode(fileName);
	return this.CSVToArray(
		imns.FIO.readTextFile(fileDescriptor)
	);

	
}


// Пример использования
window.console.log( loadCSVFile("C:\\BOTS\\iMacros\\test.csv") );

В консоли мы видим аккуратно распарсенный массив с нашей таблицей.

Импорт и экспорт CSV-файлов в Excel

Перенести большой объём данных о товарах, заказах и покупателях из Эквид-магазина в Excel можно с помощью встроенных инструментов импорта и экспорта.

Импорт и экспорт в Эквиде работают на основе CSV-файлов. CSV (от английского Comma-Separated Values — значения, разделённые запятыми) — текстовый формат, предназначенный для представления данных в виде таблицы. В таких файлах для разделения текста на колонки используется специальный символ — разделитель.

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

Информация была полезной? Поделись с друзьями!

Данные как ваша отправная точка

Когда вы начинаете проект по data science, вам придется работать с данными, которые вы собрали по всему интернету, и с наборами данных, которые вы загрузили из других мест — Kaggle, Quandl и тд

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

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

Проверяем качество таблицы

• Представляет ли электронная таблица статические данные?
• Смешивает ли она данные, расчеты и отчетность?
• Являются ли данные в вашей электронной таблице полными и последовательными?
• Имеет ли ваша таблица систематизированную структуру рабочего листа?
• Проверяли ли вы действительные формулы в электронной таблице?

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

Лучшие практики для данных электронных таблиц

Прежде чем приступить к чтению вашей электронной таблицы на Python, вы также должны подумать о том, чтобы настроить свой файл в соответствии с некоторыми основными принципами, такими как:

• Первая строка таблицы обычно зарезервирована для заголовка, а первый столбец используется для идентификации единицы выборки;
• Избегайте имен, значений или полей с пробелами. В противном случае каждое слово будет интерпретироваться как отдельная переменная, что приведет к ошибкам, связанным с количеством элементов на строку в вашем наборе данных. По возможности, используйте:
• подчеркивания,
• тире,
• горбатый регистр, где первая буква каждого слова пишется с большой буквы
• объединяющие слова
• Короткие имена предпочтительнее длинных имен;
• старайтесь не использовать имена, которые содержат символы ?, $,%, ^, &, *, (,), -, #,? ,,, <,>, /, |, \, , {, и };
• Удалите все комментарии, которые вы сделали в вашем файле, чтобы избежать добавления в ваш файл лишних столбцов или NA;
• Убедитесь, что все пропущенные значения в вашем наборе данных обозначены как NA.

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

Если вы работаете с Microsoft Excel, вы можете сохранить файл в разных форматах: помимо расширения по умолчанию .xls или .xlsx, вы можете перейти на вкладку «Файл», нажать «Сохранить как» и выбрать одно из расширений, которые указаны в качестве параметров «Сохранить как тип». Наиболее часто используемые расширения для сохранения наборов данных в data science — это .csv и .txt (в виде текстового файла с разделителями табуляции). В зависимости от выбранного варианта сохранения поля вашего набора данных разделяются вкладками или запятыми, которые образуют символы-разделители полей вашего набора данных.

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

Подчеркивание текста в документе, предназначенном для печати