[Урок 10] . Разбираем формат CSV. Грамотное чтение и запись таблиц Excel(без !DATASOURCE).

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

Структура формата CSV

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


Каждая строка — это одна строка таблицы.
Разделителем (англ. delimiter) значений колонок является символ запятой «,». Однако на практике часто используются другие разделители, например «;»
Значения, содержащие зарезервированные символы (двойная кавычка, запятая, точка с запятой, новая строка) обрамляются двойными кавычками («). Если в значении встречаются кавычки — они представляются в файле в виде двух кавычек подряд.


Ссылка на спецификацию CSV-1203(eng.) — http://mastpoint.curzonnassau.com/csv-1203/csv-1203.pdf
Ссылка на спецификацию RFC-4180(рус.) — http://tradeincome.ru/useful-content/RFC%204180%20rus.pdf

 

Вы можете написать следующий текст и сохранить его в файл с расширением «csv»:

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

Такой файл легко открывается в Excel(или в OpenOffice) и все значения находятся в своих ячейках таблицы.

iMacros Excel

Аналогично вы можете взять любую экселевскую таблицу(с расширениями .xls или .xlsx) и сохранить ее в формат CSV. Это очень простой формат и он не поддерживает формулы, вычисления и т.д., как полноценные форматы Excel. Однако, бывает очень удобно записывать данные из iMacros и потом обрабатывать их в Excel и, наоборот, брать информацию из Excel и использовать ее в своих скриптах.

Примечание: на самом деле у Excel есть еще XML-формат, который поддерживает формулы, стили оформления и большую часть функционала Excel, он текстовый, а не бинарный, как .xls или .xlsx. Тем не менее, он куда более сложный, нежели CSV и требует более глубоко понимания и навыков в кодинге. Мне доводилось генерировать при помощи JS вывод отчетов с перекрестными формулами в Excel, задача достаточно трудоемкая и времязатратная.

Чтение формата CSV и получение любой ячейки таблицы с помощью iMacros и Javascript

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

Для загрузки файла в переменную, мы используем код для чтения файла из прошлого урока:

var loadFile = function (fileName) {
	var fileDescriptor = imns.FIO.openNode(fileName);
	return imns.FIO.readTextFile(fileDescriptor);
}

Замечательно! Мы получим содержимое файла в виде текста. Это не очень удобно, поэтому преобразуем его в двумерный Javascript-массив.

Вот такой массив я хочу видеть в результате:

var arr = [
	[1,"John Smith",1961],
	[2,"Mike Trump",1975],
	[3,"Oliver Stone",1982]
];

// Что потом можно делать с массивом
window.console.log(arr[0][0]); // 1 - первая строка, первый столбец
window.console.log(arr[2][2]); // 1982 - третья строка, третий столбец

window.console.log(arr.length); // 3 - считаем строки
window.console.log(arr[0].length); // 3 - считаем столбцы

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

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

На первый взгляд может показаться, что мы можем тупо использовать 2 раза метод split(), поскольку текст мы можем разбить на массив строк при помощи split('\r\n'), а строку на ячейки при помощи 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|^)" +          
			"(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" +               
			"([^\"\\" + strDelimiter + "\\r\\n]*))"
		),
		"gi"
		);

	var arrData = [[]];
	var arrMatches = null;

	while (arrMatches = objPattern.exec( strData )){           
		var strMatchedDelimiter = arrMatches[ 1 ];
		if (
			strMatchedDelimiter.length &&
			strMatchedDelimiter !== strDelimiter
			){
			arrData.push( [] );
		}
		var strMatchedValue;
		if (arrMatches[ 2 ]){
			strMatchedValue = arrMatches[ 2 ].replace(
				new RegExp( "\"\"", "g" ),
				"\""
				);
		} else {
			strMatchedValue = arrMatches[ 3 ];
		}
		arrData[ arrData.length - 1 ].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|^)" +          
				"(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" +               
				"([^\"\\" + strDelimiter + "\\r\\n]*))"
			),
			"gi"
			);

		var arrData = [[]];
		var arrMatches = null;

		while (arrMatches = objPattern.exec( strData )){           
			var strMatchedDelimiter = arrMatches[ 1 ];
			if (
				strMatchedDelimiter.length &&
				strMatchedDelimiter !== strDelimiter
				){
				arrData.push( [] );
			}
			var strMatchedValue;
			if (arrMatches[ 2 ]){
				strMatchedValue = arrMatches[ 2 ].replace(
					new RegExp( "\"\"", "g" ),
					"\""
					);
			} else {
				strMatchedValue = arrMatches[ 3 ];
			}
			arrData[ arrData.length - 1 ].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") );

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

Сохранение двумерного Javascript-массива в CSV в iMacros

Считывать содержимое csv-файлов мы научились. Отсюда возникает вопрос, а как же выполнить обратное преобразование массива в текст? На самом деле, тут нету ничего сложного.

Давайте воспользуемся следующей функцией:

// Функция для сохранения массива в файл в формате CSV
var saveToCSVFile = function (fileName, tableArray, separator) {

	this.arrayToCSV = function(tableArray, replacer) {
		replacer = replacer || function(r, c, v) { return v; };
		separator = separator || ",";
		var csv = '', c, cc, r, rr = tableArray.length, cell;
		for (r = 0; r < rr; ++r) {
			if (r) { csv += '\r\n'; }
			for (c = 0, cc = tableArray[r].length; c < cc; ++c) {
				if (c) { csv += separator; }
				cell = replacer(r, c, tableArray[r][c]);
				if (/[,\r\n"]/.test(cell)) { cell = '"' + cell.replace(/"/g, '""') + '"'; }
				cell = typeof(cell) == "string" && cell.length > 0 ? "\"" + cell + "\"" : cell;
				csv += (cell || 0 === cell) ? cell : '';
				
			}
		}
		return csv;
	}

	var fileDescriptor = imns.FIO.openNode(fileName);
	imns.FIO.writeTextFile(fileDescriptor, this.arrayToCSV(tableArray));

};

// Тестовый массив для преобразования
var arr = [
	[1,"John Smith",1961],
	[2,"Mike Trump",1975],
	[3,"Oliver Stone",1982]
];

// Сохраняем в формате CSV
saveToCSVFile("C:\\BOTS\\iMacros\\test.csv", arr, ";");

Важное примечание:   CSV-файлы необязательно должны использовать запятую в качестве разделителя, в большей части мира используется точка с запятой(;).
Excel 2016 корректно работает с вашими региональными настройками и использует тот разделитель, который указан в панели управления в Настройках региона->Дополнительные параметры->Разделитель элементов списков. В Excel 2010 на английской винде, у меня по умолчанию стояла запятая в качестве разделителя, вы его можете поменять.  Поэтому, если вдруг вы сгенерировали CSV-файл, а он у вас в Excel открылся в одной ячейке, первым делом проверьте какие разделители вы используете и какие по умолчанию использует ваша операционная система.

Пишите ваши вопросы, предложения и замечания в комментариях!

13 комментариев

  1. я бы добавил, что если разделитель в настройках не соответствует используемому в данном конкретном csv-файле и при двойном клике по файлу файл открывается, но все данные вписаны в первый столбец, то корректно открыть csv — можно через меню Excel’я файл -> открыть

    Ответить
  2. Итоговый скрипт чтения и парсинга файла формата CSV для iMacros+JS:
    Не работает разделитеть.
    я добавил 2-й параметр
    var loadCSVFile = function (fileName, strDelimiter) {…

    Ответить
    • а что там понимать, у автора кода половина функционала задублирована, и выполняет одну и ту же функцию или не выполняет, если говорить о replacer()
      в результате, возвращается текст с кучей лишних двойных кавычек
      по поводу replacer() видимо код должен был выглядеть так:
      replacer = replacer || function(r, c, v) { return v[r][c]; };
      и вызываться так:
      cell = replacer(r, c, tableArray);
      ===========
      итого, после очистки функции от ненужного мусора получаем:
      [code]
      function saveToCSVFile (fileName, tableArray, separator){
      var separator = separator || ‘,’, csv = », c, cc, r, rr = tableArray.length, cell;
      for (r = 0; r < rr; r++) {
      if (r) { csv += '\r\n'; };
      for (c = 0, cc = tableArray[r].length; c 0 ? ‘»‘ + cell + ‘»‘ : cell;
      csv += (cell || 0 === cell) ? cell : »;
      };
      };
      var fileDescriptor = imns.FIO.openNode(fileName);
      imns.FIO.writeTextFile(fileDescriptor, csv);
      };
      [/code]

      Ответить
  3. Читаю массив… 10 строк по 2 значения…
    Распарсился… Картинка немножко другая получилась:http://joxi.ru/EA4nJDnfwO6jpm
    А как вывести командой alert 2й элемент 2й строки? Тяжело въезжаю, а вы в теме, подскажите пжлста!

    Ответить
    • Александр, допустим ваш массив находится в переменной data. Тогда, чтобы вывести 2-й элемент 2-й строки, вам нужно написать alert( data[1][1] );
      В массивах нумерация элементов начинается с нуля.

      Ответить
  4. В скрипте сохранения в csv все работает отлично, до тех пор, пока не попадается многострочный CSV. Тогда на выходе получется полная белеберда. Причем парсинг многострочной таблички проходит без проблем, а вот сохранить результаты не возможно.

    Подскажите, такие ошибки только у меня, или у всех? И как исправить, код увы полностью не понимаю.

    Было:
    http://picua.org/img/2018-02/25/snzw7329pnsu43080sd3jilnt.png
    Стало:
    http://picua.org/img/2018-02/25/7nkgucz93m5vd3brkvkduhdk6.png

    П.С. Собственно пытаюсь использовать JS для работы, а не на кранах, как дядюшка Нагибака и завещал.
    И да, сохраниние в csv мне действительно нужно, для управления процессом через екселевскую табличку JSON при всех своих приймуществах абсолютно непригоден для прямого редактирования человеком.

    Ответить
    • Уважаемый, что такое многостраничный CSV? Вы вообще представляете себе структуру CSV файла? Открой и вики и почитайте и не несите бред.

      Ответить
      • Не много-СТРА-ничный а много-СТРО-чный, т.е. в ячейках которого по несколько строк. Попробуйте запустить крайний код из статьи, добавив \n в его тестовый масив и увидите, что я имею в виду.

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

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

        P.S.: Пардон за некропостинг, 2 месяца назад еще комент оставил, но что-то его тут не видать. Ну что-ж, JS приучил меня не сдаваться после первой-же попытки.

        Ответить

Оставить комментарий