优化的 EmEditor 宏到元素拆分、排序、最旧到最长日期和提取

Question

我目前有一个这种格式的分离文件（2列选项卡“\t”分隔），“;”分隔列中的所有元素）。

    User\tDate

    Alice Cooper;John Smith\t07/11/2019

    Alice Cooper\t23/11/2018

    Alice Cooper\t21/11/2018

    Alice Cooper\t26/11/2018

    Alice Cooper\t26/11/2018

    Alice Cooper;John Smith\t09/12/2018

    Alice Cooper;John Smith\t09/12/2018

    Alice Cooper;John Smith\t04/12/2018

    Alice Cooper\t07/12/2018

    Alice Cooper\t07/12/2018

我希望对优化的宏（理想情况下是javascript）的任何想法都创建以下输出文件：

    User\tEarliest\tLatest\tDates_with_Most_Occurences\tMost_Occurence_Number

    Alice Cooper\t21/11/2018\t07/11/2019\t26/11/2018;07/12/2018\t2

    John Smith\t04/12/2018\t07/11/2019\t09/12/2018\t1

所以中间步骤（我目前正在手动执行，但想滚入宏）：

步骤 1：分离出列 1 中的 Name 元素

（给出这样的东西）：

    User\tDate

    Alice Cooper\t07/11/2019

    John Smith\t07/11/2019

    Alice Cooper\t23/11/2018

    Alice Cooper\t21/11/2018

    Alice Cooper\t26/11/2018

    Alice Cooper\t26/11/2018

    Alice Cooper\t09/12/2018

    John Smith\t09/12/2018

    Alice Cooper\t09/12/2018

    John Smith\t09/12/2018

    Alice Cooper\t04/12/2018

    John Smith\t04/12/2018

    Alice Cooper\t07/12/2018

    Alice Cooper\t07/12/2018

步骤 2：将 Col1 A-Z 和 Col 2 从最旧到最新进行排序。现在，基于列 1 组合列 2 元素（给出如下内容）：

    User\tDate

    Alice Cooper\t21/11/2018;23/11/2018;26/11/2018;26/11/2018;04/12/2018;07/12/2018;07/12/2018;09/12/2018;09/12/2018;07/11/2019;

    John Smith\t04/12/2018;09/12/2018;09/12/2018;07/11/2019;

步骤3：现在，在Col2中为每行获取日期信息，并创建以下4个新列：最早日期，最新日期，Dates_with_Most_Occurences Most_Occurence_Number（给出如下内容）：

    User\tDate

    Alice Cooper\t21/11/2018;23/11/2018;26/11/2018;26/11/2018;04/12/2018;07/12/2018;07/12/2018;09/12/2018;09/12/2018;07/11/2019;

    John Smith\t04/12/2018;09/12/2018;09/12/2018;07/11/2019;

步骤 4：删除 Col2（日期）：给出最终输出：

    User\tEarliestDate\tLatestDate\tDates_with_Most_Occurences\tMost_Occurence_Number

    Alice Cooper\t21/11/2018\t07/11/2019\t26/11/2018;07/12/2018\t2

    John Smith\t04/12/2018\t07/11/2019\t09/12/2018\t1

我只需要宏来创建最终输出，中间（上面的步骤1，2，3）只是显示了我正在尝试执行的操作的逻辑。真正的源文件将是数千行，所以如果这可以以任何方式优化EmEditor，那将是太棒了。

Answer 1

假设您的数据文件不包含空行，下面是脚本。document.ConvertCsv(2); // This assumes your Tab format is the second one on the CSV/Sort barfunction parseDate(s) {&nbsp; &nbsp; var split = s.split('/');&nbsp; &nbsp; return new Date(split[2], split[1] - 1, split[0]);}var data = [];// Read the filevar numberOfLines = document.GetLines();if (numberOfLines >= 2 && document.GetLine(2) === '') {&nbsp; &nbsp; numberOfLines = 1; // CSV document only has header without data}for (var line = 1; line < numberOfLines; line++) {&nbsp; &nbsp; var rowData = [&nbsp; &nbsp; &nbsp; &nbsp; document.GetCell(line + 1, 1, eeCellIncludeNone),&nbsp; &nbsp; &nbsp; &nbsp; parseDate(document.GetCell(line + 1, 2, eeCellIncludeNone)),&nbsp; &nbsp; ];&nbsp; &nbsp; data.push(rowData);}// Separate combined usersvar separated = [];for (var row = 0; row < data.length; row++) {&nbsp; &nbsp; var split = data[row][0].split(';');&nbsp; &nbsp; for (var i = 0; i < split.length; i++) {&nbsp; &nbsp; &nbsp; &nbsp; separated.push([split[i], data[row][1]]);&nbsp; &nbsp; }}// Group by user// {[key: string]: {data: [[]], earliest: Date, latest: Date, mostOccurrence: Date, occurrence: number}}var users = {};for (var row = 0; row < separated.length; row++) {&nbsp; &nbsp; if (!(separated[row][0] in users)) {&nbsp; &nbsp; &nbsp; &nbsp; users[separated[row][0]] = {data: []};&nbsp; &nbsp; }&nbsp; &nbsp; users[separated[row][0]].data.push(separated[row]);}// At this point, we have parsed the file into useful data.// alert(JSON.stringify(users, null, '&nbsp; ')); // To check// Data analysisfor (var userKey in users) {&nbsp; &nbsp; var sorted = users[userKey].data.sort(function(a, b) {&nbsp; &nbsp; &nbsp; &nbsp; return a[1].getTime() - b[1].getTime();&nbsp; &nbsp; });&nbsp; &nbsp; users[userKey].earliest = sorted[0][1];&nbsp; &nbsp; users[userKey].latest = sorted[sorted.length - 1][1];&nbsp; &nbsp; // Count dates&nbsp; &nbsp; var dates = {}; // {[key: number]: number}&nbsp; &nbsp; for (var i = 0; i < sorted.length; ++i) {&nbsp; &nbsp; &nbsp; &nbsp; if (!(sorted[i][1].getTime() in dates)) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; dates[sorted[i][1].getTime()] = 0;&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; dates[sorted[i][1].getTime()] += 1;&nbsp; &nbsp; }&nbsp; &nbsp; var mostOccurrence = {date: [], occurrence: -1};&nbsp; &nbsp; for (var k in dates) {&nbsp; &nbsp; &nbsp; &nbsp; if (dates[k] > mostOccurrence.occurrence) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; mostOccurrence = {date: [k], occurrence: dates[k]}&nbsp; &nbsp; &nbsp; &nbsp; } else if (dates[k] === mostOccurrence.occurrence) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; mostOccurrence.date.push(k);&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }&nbsp; &nbsp; users[userKey].mostOccurrence = [];&nbsp; &nbsp; for (var i = 0; i < mostOccurrence.date.length; i++) {&nbsp; &nbsp; &nbsp; &nbsp; var date = new Date();&nbsp; &nbsp; &nbsp; &nbsp; date.setTime(mostOccurrence.date[i]);&nbsp; &nbsp; &nbsp; &nbsp; users[userKey].mostOccurrence.push(date);&nbsp; &nbsp; }&nbsp; &nbsp; users[userKey].occurrence = mostOccurrence.occurrence;}// Format the numbers and output to documenteditor.NewFile();document.selection.Text = 'User\tEarliestDate\tLatestDate\tDates_with_Most_Occurences\tMost_Occurence_Number';for (var _ in users) {&nbsp; &nbsp; document.selection.Text += '\r\n';}document.ConvertCsv(2);function formatDate(d) {&nbsp; &nbsp; return d.getDate() + '/' + (d.getMonth() + 1) + '/' + d.getFullYear();}var line = 2;for (var userKey in users) {&nbsp; &nbsp; document.SetCell(line, 1, userKey, eeAutoQuote);&nbsp; &nbsp; document.SetCell(line, 2, formatDate(users[userKey].earliest), eeAutoQuote);&nbsp; &nbsp; document.SetCell(line, 3, formatDate(users[userKey].latest), eeAutoQuote);&nbsp; &nbsp; var mostOccurrenceStr = '';&nbsp; &nbsp; for (var i = 0; i < users[userKey].mostOccurrence.length; i++) {&nbsp; &nbsp; &nbsp; &nbsp; mostOccurrenceStr += formatDate(users[userKey].mostOccurrence[i]) + ';';&nbsp; &nbsp; }&nbsp; &nbsp; document.SetCell(line, 4, mostOccurrenceStr.substring(0, mostOccurrenceStr.length - 1), eeAutoQuote);&nbsp; &nbsp; document.SetCell(line, 5, users[userKey].occurrence, eeAutoQuote);&nbsp; &nbsp; line++;}希望它有效，但如果不起作用，请告诉我。