Преобразование CSV-файлов в книги Excel

Статья
02/07/2024

Многие службы экспортируют данные в виде файлов с разделими-запятыми (CSV). Это решение автоматизирует процесс преобразования этих CSV-файлов в книги Excel в формате .xlsx. Он использует поток Power Automate для поиска файлов с расширением .csv в папке OneDrive, а сценарий Office — для копирования данных из файла .csv в новую книгу Excel.

Решение

Сохраните файлы .csv и пустой шаблон .xlsx файл в папке OneDrive.
Создайте сценарий Office для анализа данных CSV в диапазон.
Создайте поток Power Automate для чтения файлов .csv и передачи их содержимого в скрипт.

Примеры файлов

Скачайте convert-csv-example.zip , чтобы получить файл Template.xlsx и два примера .csv файлов. Извлеките файлы в папку в OneDrive. В этом примере предполагается, что папка называется "output".

Добавьте следующий скрипт в пример книги. В Excel используйте команду Автоматизировать>новый скрипт , чтобы вставить код и сохранить скрипт. Сохраните его как Преобразовать CSV и попробуйте пример самостоятельно!

Пример кода. Вставка разделенных запятыми значений в книгу

/**
 * Convert incoming CSV data into a range and add it to the workbook.
 */
function main(workbook: ExcelScript.Workbook, csv: string) {
  let sheet = workbook.getWorksheet("Sheet1");

  // Remove any Windows \r characters.
  csv = csv.replace(/\r/g, "");

  // Split each line into a row.
  // NOTE: This will split values that contain new line characters.
  let rows = csv.split("\n");

  /*
   * For each row, match the comma-separated sections.
   * For more information on how to use regular expressions to parse CSV files,
   * see this Stack Overflow post: https://stackoverflow.com/a/48806378/9227753
   */
  const csvMatchRegex = /(?:,|\n|^)("(?:(?:"")*[^"]*)*"|[^",\n]*|(?:\n|$))/g
  rows.forEach((value, index) => {
    if (value.length > 0) {
      let row = value.match(csvMatchRegex);
    
      // Check for blanks at the start of the row.
      if (row[0].charAt(0) === ',') {
        row.unshift("");
      }
  
      // Remove the preceding comma and surrounding quotation marks.
      row.forEach((cell, index) => {
        cell = cell.indexOf(",") === 0 ? cell.substring(1) : cell;
        row[index] = cell.indexOf("\"") === 0 && cell.lastIndexOf("\"") === cell.length - 1 ? cell.substring(1, cell.length - 1) : cell;
      });
    
      // Create a 2D array with one row.
      let data: string[][] = [];
      data.push(row);
  
      // Put the data in the worksheet.
      let range = sheet.getRangeByIndexes(index, 0, 1, data[0].length);
      range.setValues(data);
    }
  });

  // Add any formatting or table creation that you want.
}

Поток Power Automate: создание файлов .xlsx

Войдите в Power Automate и создайте новый запланированный облачный поток.
Задайте для потока значение Повторять каждые "1" "День" и нажмите кнопку Создать.
Получите файл Excel шаблона. Это основа для всех преобразованных .csv файлов. В построителе потоков нажмите кнопку + и добавьте действие. Выберите действие Получить содержимое файла соединителя OneDrive для бизнеса. Укажите путь к файлу Template.xlsx.
- Файл: /output/Template.xlsx
Переименуйте шаг Получение содержимого файла . Выберите текущее название "Получить содержимое файла" в области задач действия. Измените имя на "Получить шаблон Excel".
Добавьте действие, которое получает все файлы в папке output. Выберите действие "Списокфайлов в папке" соединителя OneDrive для бизнеса. Укажите путь к папке, содержащей файлы .csv.
- Папка: /output
Добавьте условие, чтобы поток работал только с .csv файлами. Добавьте действие элемента управления Условие . Используйте следующие значения для условия.
- Выберите значение: Имя (динамическое содержимое из списка файлов в папке). Обратите внимание, что это динамическое содержимое содержит несколько результатов, поэтому элемент управления For each окружает условие.
- заканчивается на (из раскрывающегося списка)
- Выберите значение: .csv
Остальная часть потока находится в разделе Если да , так как мы хотим действовать только с .csv файлами. Получите отдельный файл .csv, добавив действие, которое использует действие Получить содержимое файласоединителя OneDrive для бизнеса. Используйте идентификатор динамического содержимого из списка файлов в папке .
- File: Id (динамическое содержимое из шага Перечисление файлов в папке )
Переименуйте новый шаг Получение содержимого файла в "Получить .csv файл". Это помогает отличить этот файл от шаблона Excel.
Сделайте новый файл .xlsx, используя шаблон Excel в качестве базового содержимого. Добавьте действие, использующее действие Создать файлсоединителя OneDrive для бизнеса. Используйте следующие значения.
- Путь к папке: /output
- Имя файла: имя без расширения.xlsx (выберите имя без динамического содержимого расширения в папке Список файлов в папке и вручную введите ".xlsx" после него).
- Содержимое файла: содержимое файла (динамическое содержимое из шаблона Получить Excel)
Запустите скрипт, чтобы скопировать данные в новую книгу. Добавьте действие Выполнить скрипт соединителя Excel Online (бизнес). Используйте следующие значения для действия.
- Расположение: OneDrive для бизнеса
- Библиотека документов: OneDrive
- File: Id (динамическое содержимое из файла Create)
- Скрипт: преобразование CSV-файла
- csv: содержимое файла (динамическое содержимое из get .csv file)
Сохраните поток. Конструктор потоков должен выглядеть так, как показано на следующем рисунке.
Нажмите кнопку Тестировать на странице редактора потоков или запустите поток через вкладку Мои потоки . Обязательно разрешите доступ при появлении запроса.
Новые файлы .xlsx должны находиться в папке output вместе с исходными файлами .csv. Новые книги содержат те же данные, что и CSV-файлы.

Устранение неполадок

Тестирование скриптов

Чтобы протестировать скрипт без использования Power Automate, присвойте значение перед csv его использованием. Добавьте следующий код в качестве первой строки main функции и выберите Выполнить.

  csv = `1, 2, 3
         4, 5, 6
         7, 8, 9`;

Файлы с запятой и другие альтернативные разделители

В некоторых регионах вместо запятых для разделения значений ячеек используются точки с запятой (';'). В этом случае необходимо изменить следующие строки в скрипте.

Замените запятую точкой с запятой в инструкции регулярного выражения. Это начинается с let row = value.match.
```
let row = value.match(/(?:;|\n|^)("(?:(?:"")*[^"]*)*"|[^";\n]*|(?:\n|$))/g);
```
Замените запятую точкой с запятой в проверка для пустой первой ячейки. Это начинается с if (row[0].charAt(0).
```
if (row[0].charAt(0) === ';') {
```
Замените запятую точкой с запятой в строке, которая удаляет символ разделения из отображаемого текста. Это начинается с row[index] = cell.indexOf.
```
   row[index] = cell.indexOf(";") === 0 ? cell.substr(1) : cell;
```

Примечание.

Если файл использует вкладки или любой другой символ для разделения значений, замените ; в приведенных выше подстановках \t на или любой другой символ.

Большие CSV-файлы

Если файл содержит сотни тысяч ячеек, вы можете достичь ограничения на передачу данных Excel. Вам потребуется периодически принудительно синхронизировать скрипт с Excel. Самый простой способ сделать это — вызвать console.log после обработки пакета строк. Добавьте следующие строки кода, чтобы это произошло.

Перед rows.forEach((value, index) => {добавьте следующую строку.
```
  let rowCount = 0;
```
После range.setValues(data);добавьте следующий код. Обратите внимание, что в зависимости от количества столбцов может потребоваться уменьшить 5000 число.
```
  rowCount++;
  if (rowCount % 5000 === 0) {
    console.log("Syncing 5000 rows.");
  }
```

Предупреждение

Если CSV-файл очень велик, могут возникнуть проблемы с временем ожидания в Power Automate. Необходимо разделить данные CSV на несколько файлов, прежде чем преобразовывать их в книги Excel.

Акценты и другие символы Юникода

Файлы с символами Юникода, такие как гласные éс диакриплексами, должны сохраняться с правильной кодировкой. Для создания файла соединителя OneDrive Power Automate по умолчанию используется ANSI для .csv файлов. Если вы создаете файлы .csv в Power Automate, необходимо добавить метку порядка байтов (BOM) перед значениями, разделенными запятыми. Для UTF-8 замените содержимое файла для операции записи .csv файла выражением concat(uriComponentToString('%EF%BB%BF'), <CSV Input>) (где <CSV Input> — исходные данные CSV).

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

Окружающие кавычки

В этом примере удаляются все кавычки (""), которые окружают значения. Обычно они добавляются к значениям, разделенным запятыми, чтобы предотвратить обработку запятых в данных как маркеры разделения. В файле .csv, который открывается в Excel, а затем сохраняется как файл .xlsx, эти кавычки никогда не будут отображаться для чтения. Если вы хотите сохранить кавычки и отобразить их в окончательных электронных таблицах, замените строки 27–30 скрипта следующим кодом.

// Remove the preceding comma.
row.forEach((cell, index) => {
  row[index] = cell.indexOf(",") === 0 ? cell.substring(1) : cell;
});

Поделиться через